Sudden issue: getDataSetByQuery very slow.

Classic Servoy
1

Hi all, a mystery,

we do a complex search using getDataSetByQuery() to find transactions to be included on a monthly statement. We return zero or more pks. Typically about 20.

SELECT DISTINCT ON(document_date,transaction_document_id,transaction_id) transaction_id FROM acc_transactions,acc_documents WHERE  (transaction_document_id = document_id AND (date_trunc('day',document_date) BETWEEN ? AND ?) AND document_type NOT IN ('Delivery Note','Pro-Forma Invoice','Quote') AND document_customer_id = ? AND (document_outstanding <> 0 OR document_type = 'Payment on Account'))  OR (transaction_document_consolidated_id = document_id AND transaction_document_consolidated_id NOTNULL AND (date_trunc('day',document_date) BETWEEN ? AND ?) AND document_customer_id = ? AND document_outstanding <> 0)  ORDER BY document_date,transaction_document_id

OK, I know it is a bit nasty but it works for now.

Before today, there was no noticeable delay. Today, getDataSetByQuery() takes 20 seconds. Even when it returns an empty dataset.

I have run exactly the same query from a third party tool over the internet and the query takes about 400ms (I know a bit slow, but nowhere near 20 seconds).

We have restarted the Servoy server. Database server and servoy server on same computer. Servoy Server Win 2003 and clients Win XP, PostgreSQL 8.1.3, Servoy 3.1.3+. Developer Mac OS X 10.4.8.

Same behaviour when searching from Servoy Client and Servoy Developer.

Based on this, we don’t think the problem is caused by

  • the database server
  • the query
  • the network connection.

Any ideas what could cause this sudden change?

2

Hi, we restarted the Win2003 server and speed is now more or less back to normal.

3

swingman:
Hi, we restarted the Win2003 server and speed is now more or less back to normal.

What else would you expect from a Windows server? ;)

4

ngervasi:
What else would you expect from a Windows server? :wink:

Agree. I’m trying to get them over to Unix or Mac.

5

swingman:
I’m trying to get them over to Unix or Mac.

Why do you say Unix twice ? ;)

By the way W2K3 Server is one of the most stable windows releases ever. I know of people who use it as their desktop windows just for that reason.