Help Speeding Up Rank Query
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums
Results 1 to 2 of 2

Thread: Help Speeding Up Rank Query

  1. #1
    Join Date
    Dec 2010

    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


     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;

  2. #2
    Join Date
    Apr 2009

    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

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

HTML5 Development Center