How To Make Query Run Faster (1 Viewer)

See attached image. I am looking for ways to make my query run faster. When I run this query for small groups which will return less than 100 records, the query takes about 30 seconds. However I have some groups that have over 1000 records and that will take 3-5 minutes to run. Actually sometimes the query even bombs after 30 seconds on the large groups.

This query hits against a linked SQL database. Three of the fields gets the criteria from a form combo box. The thing that makes this query take so long is the Count. It counts the number of times the Signature Type "duedte" has been changed.

I am looking for any suggestions for how to speed this up. I want real time results so I was not sure if copying the data locally each time then running the queries would help any in the overall time.

I appreciate any suggestions.

Jim

If the query runs OK without the count, can you base a report on the query and do the counting in the report?

How about if you replace the "form parameters" by actual hard parameters what happens to the performance at that point?
Query-ing some 1000 records shouldnt be a huge problem for a SQL server, but I am guessing it might be possible due to the form parameters the data is beeing fetched and queried local instead of on the server.

neileg said:

If the query runs OK without the count, can you base a report on the query and do the counting in the report?

Click to expand...

I don't think so because the query is looking up the records for "duedte" and counting the number of times it has a different date. I am not sure how I could do that in the report itself.

namliam said:

How about if you replace the "form parameters" by actual hard parameters what happens to the performance at that point?
Query-ing some 1000 records shouldnt be a huge problem for a SQL server, but I am guessing it might be possible due to the form parameters the data is beeing fetched and queried local instead of on the server.

Click to expand...

I have tried this and it has no effect on performance of the query. I may have misled you on the number of records. The results of the query is about 1300 records on the high end but the actual number of records that the query has to plow through to get there is probably closer to 6000. The reason is that the way the database is designed there is a new record each time the due date is reset. So my query is conunting the number of different dates. On average, I would say the due date is reset 3-5 times.

Still 6000 or even 600000 or even 600000 in each of those tables, shouldnt be a problem for a real server??

Are you sure the tables are joined properly using indexed fields and stuff??
Then again looking at your where clause... The LIKE(s) is probably what is freezing your query as that is impossible to index.

You probably need the LIKEs but... try doing it another way....
If your like contains limitted options, maybe an IN () clause will do?
Maybe use a subquery ONLY on the the one table without the joins in place to do the like on...
LIKE is slow.... period...