My report is running very slow, what can I do!

[Sohaila Taravati]

I have a report with a query behind it that comes form several other queries. It takes 4 to 8 minutes for the query to ask the question for the user to put the starting and ending date. Once the staring and the ending date is put in, it goes fast. What could be the problem. Thank you very much in advance.

 

[Chris RR]

If you just run the query, does it still take as long?

If it does, I would look at each of the underlying queries and see what you can do to speed things up.

For example, let's say that the purpose of all of this is to create a sales report for a period of time (starting date to ending date) , and that you have 250,000 rows in your table.

If the first query doesn't do any record selection, it's going to be massaging and sorting a quarter of a million rows. So is the second query, and maybe the third. Then you finally pick out the 1,942 records you want (prompting for beginning and ending dates), and write the report. It would be much better & faster to select the correct records up front, then do the processing. Logically, you get the same result, but it will run light years faster.