Sloooooow queries

[oticonaus]

I am hoping this will be a quick yes/no answer...

I have a table which has 230,000 records, and is still growing. If I try to write a query with any sort of domain aggregate function (DSUM, etc) it takes a very long time to run (well several minutes anyway) on a P4 notebook using both ODBC and using a local db. And that is just with a basic query - in the end, the query will need to involve multiple domain aggregate functions.

Am I realistically going to be able to do this in Access, or do I need to move to something like Crystal?

 

[PHV]

write a query with any sort of domain aggregate function (DSUM, etc)
I don't see why you should use a DSum VBA function in a query.
Can you please post an example of such SQL code ?

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

 

[oticonaus]

The query as it stands is

SELECT Prospects.Doctor, DSum("Counter","Prospects") AS Total
FROM Prospects
GROUP BY Prospects.Doctor
HAVING (((Prospects.Doctor)<>""));

I could use

SELECT Prospects.Doctor, Sum(Prospects.Counter) AS SumOfCounter
FROM Prospects
GROUP BY Prospects.Doctor
HAVING (((Prospects.Doctor)<>""));

but it does not seem to make much difference. Once I add more fields to the query, it seemed a little simpler using functions in the query.

 

[PHV]

And this ?
SELECT Prospects.Doctor, Sum(Prospects.Counter) AS SumOfCounter
FROM Prospects
WHERE Prospects.Doctor<>""
GROUP BY Prospects.Doctor;
You may also consider create an index (allowing duplicates) on the Doctor column to speed up this query.


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

 

[JonFer]

Avoid using domain aggregate functions as they are very inefficient. You should notice much faster queries with PHV's SQL. Note that the criteria is in the WHERE clause (where it belongs) instead of HAVING. I know Access does this automatically in the Query Design window but look up the difference between WHERE and HAVING if you don't know already. It is another way to improve query efficiency.