Query speed negatively affected after converting from 97

[Trogg]

We recently converted our Data repository system from Access 97 to Access 2000. Sql 7 platform has not changed. After the conversion, I find that I have several queries that used to run in a few seconds that take minutes to run now... some that took minutes now run for hours and never produce results. After going back and working through a few of these I find that if I don't use the Totals button (aggregate functions) the query works quickly and returns data, however I need to use the aggregate functions for grouping and summing. What's wrong with 2000? Why did these same queries work fine in 97? I would certainly assume that 2000 would at least be the same if not better but certainly not worse or slower. I will be glad to be more specific if needed. Thanks in advance!

 

[PHV]

Have you relinked all the tables and checked the indexes ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trogg]

Thanks for responding... and yes, all tables are re-linked, I do suspect the indexes but I have to wait for the vendor of the system to respond to those concerns. Do you have any suggestions as to how I might do some checking on my own while I wait for them?

 

[PHV]

You don't have ac97 at hand anymore ?
I suggested to compare both (ac2k vs ac97) database schemas.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244