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

Thread: Help Speeding Up Rank Query

  1. #1
    Join Date
    Dec 2010
    Posts
    17

    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;

  2. #2
    Join Date
    Apr 2009
    Posts
    598

    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center