-
December 13th, 2010, 11:45 AM
#1
Help Speeding Up Rank Query
I’m running ms access from my workstation (PC) and running queries on my computer. I’m trying to speed up my query particularly this one (see below)
Once my testing has been completed, I was planning on reconnecting through ODBC. Should I not be doing this, and is there a more efficient way to run my Ranking query?
Thanks for all your help Jeff
Corey
Code:
SELECT qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal, (Select count(*) from qryCustomerTotals1 as B where qryCustomerTotals1.CustomerTotal > B.customerTotal) AS Rank, (Select count(*) from qryCustomerTotals1) AS TotalRecords, [Rank]/([TotalRecords]-1)*5 AS 0to5Rank
FROM qryCustomerTotals1
ORDER BY qryCustomerTotals1.OMNI_Number, qryCustomerTotals1.Account_Executive_Name, qryCustomerTotals1.[Correspondent Name], qryCustomerTotals1.CustomerTotal DESC;
-
December 13th, 2010, 12:07 PM
#2
Re: Help Speeding Up Rank Query
Access does not have the fastest database engine I have ever seen, to say the least, and things get worse when your query is using "attached tables" which is the case here, I guess.
I got a better speed when I replaced my attached table and its ODBC link with a transfer of the query via FTP to the other computer, and another transfer to get the result back. The program is more complex, but it was worthwhile because, the process was working on several thousands of rows. Initially it lasted for about an hour. Then it lasted about 5 minutes.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|