Query performance

[EYMatt]

Hello

I'm having a massive difference in query speed between two machines both running the same query on Access. I expect the query to return between 30,000 and 50,000 results, there are calculated fields etc etc in it so I know it won't be a fast one. But my machine (which has 2.33GHz dual core processor and 2gigs of RAM, Windows XP and Office 2003) runs the query much much slower than another machine I am using (3GHz processor, 0.5gig RAM, Windows 2000 and Office 2000). Does anyone know if the difference is purely down to the different versions of office and windows? To give an idea, my machine takes approximately 3 - 3.5 hours to run this query, the other machine takes approximately 1.5 - 2 hours. Has anyone else experienced this or has anyone got any ideas? I've been through the query and it's properties and I have stripped it down to be as fast as I can make it without losing the info I need.

Cheers very much
Matt

 

[Andrew Fergus]

Hi Matt

I have noticed some routines run a lot slower on Excel 2003 versus Excel 2000 so there may be something in that.....

Have you set up indexes on all of the fields that use criteria within the query? 2 hours is a long time and 50k results is quite a lot. How many rows of data are you running the query over?

When confronted with this sort of situation I will often rebuild a query from the ground up introducing one change at a time and seeing which steps are contributing to the poor performance. Maybe that is worth a try?

Andrew

 

[SydneyGeek]

Adding to Andrew's comment:

If your query has joins that don't use indexed fields, you will take much longer to process them. I had to spend some time optimising a routine a while back, and picking indexed fields for the joins on a large dataset (pulling 400 K records) sped the query up 10-fold. The prcess time (mostly eaten by one query) went from 30 minutes to just under 3 minutes.

Also, you may find that judicious use of one or two make-table or append queries to push some of the calculated data to tables before you run the main query, can make a big difference in performance.

Denis

 

[EYMatt]

Thanks chaps, unfortunately my query is running from databases containing financial information from a live system used by thousands of users.....it's not an ideal system by any means. There is a good indexing structure throughout the whole system I'm using and a lot of the time parts of a query "naturally" fit themselves together if that makes sense. I believe the number of combinations of queries, tables and fields probably runs into the millions, as there is a vast amount of data (potentially millions of records) held on the system I'm running my reports from. I think the key lies in the versions of MS Office I'm using, but I'm going to see if any of my calculated fields can be worked out a different way......

Thanks for your advice on this, now I'm off to ask my manager for a new reporting tool!

 

[Andrew Fergus]

Denis made a suggestion which you may want to try. By using append queries to grab snapshots of data from the financial system (and storing some of the calculated values*) in tables in your database, you eliminate a number of potential bottlenecks (e.g. network traffic & constraints, inadequate indexing, shared processing capacity, excessive dataset etc.) and you can take control of the process / indexing etc.

Furthermore, you can restrict the data to just the subset(s) you want to work with. The longest time in this process will be the append queries (which could be a monthly/weekly task), and my bet is any reporting queries based on these tables will appear to 'fly' compared to your current experience. I'm not entirely convinced another reporting tool will solve the problem given you are only addressing one aspect out of half a dozen that be causing this problem.

*Normally one shouldn't store calculated values in tables for a variety of reasons (potential for errors, normalisation etc.) but in this case I think there is a valid reason for ignoring my usual advice of not storing calculated values!

HTH, Andrew

 

[EYMatt]

Thanks again. I've never done anything like that before, where would I start? In this case all of the data I am reporting on is from 2007, so none of it will be changing as users work on the live system. So the calculated fields will always return the same result - if I'm understanding properly are you saying I should almost work out these calculated fields separately or beforehand, so the main query isn't running the calculations, it's just looking up the data?

I won't need to run this report again most likely, so if an append query is good for a one time snapshot use and I don't need to worry about the calculations changing or anything like that, could it be the right way to go?

 

[SydneyGeek]

If you have built a query that returns the data you need, go into Design view, then select Make Table from the Query menu. You will be prompted to give the new table a name. Click OK, then press the Run button on the toolbar. It will take some time, but you will end up with a new, large table containing all the data from the original query. Any new queries based on that table will be much quicker than running all the calcs again.
If the data isn't changing, you may as well archive it like that.
Next month go to the same query (in Desing view -- don't double click it) and add criteria to restrict the data to the previous month. Also, change the query type from a Make-table to an Append. You will be prompted to choose to table to append to: pick the one you created. You should see all the field names from the query lined up above the field names from the table. When you run that, the last month's data will be added to the table.

Denis

 

[EYMatt]

If you have built a query that returns the data you need, go into Design view, then select Make Table from the Query menu. You will be prompted to give the new table a name. Click OK, then press the Run button on the toolbar. It will take some time, but you will end up with a new, large table containing all the data from the original query. Any new queries based on that table will be much quicker than running all the calcs again.
If the data isn't changing, you may as well archive it like that.

That sounds good. The trouble is, my query has criteria in one field which limits the results to around 40,000 records. As I run each report, I jump to the next set of record ID numbers : so I would say "between 1 and 40000" then 40,001 to 80,000 and so on. If I take this criteria out I think the number of records could run into the millions. Other than this taking a long time to run do you think there would be any other problems or would it be alright to do it?

Keep the advice coming, even if I don't use it this time I can see where else I can use this way of doing it! Cheers

 

[SydneyGeek]

You could always make the table with the first 40K records, then cycle through appending the others in 40K chunks. That way, the individual bits shouldn't take too long to run and you can do them as you have time.

Denis