Ranking Query Takes 100 Minutes To Run ?

[The Brown Growler]

Hi,

I have a ranking query that ranks dates for buyers and sites. The record set that is subject to the ranking is approximately 60,000 records and the query takes 100 minutes to complete.

Would I be better splitting the query into separate ranking queries via an alphabetic split for the sites then appending together the results of the separate ranking queries or is there a better way to reduce the overall time taken to process the ranking?

The hierarchy is site then buyer then date and the dates are ranked per site and buyer

The code for the query is below:

SELECT T_SitesAlias.Site, T_SitesAlias.Buyer, T_SitesAlias.Date, (Select Count(*) from T_Sites WHERE  [Site] = [T_SitesAlias].[Site] And [Buyer] = [T_SitesAlias].[Buyer] And [Date] > [T_SitesAlias].[Date])+1 AS SiteDateRank INTO T_Rank_Sites
FROM T_Sites AS T_SitesAlias
ORDER BY T_SitesAlias.Site, T_SitesAlias.Buyer, T_SitesAlias.Date DESC;

Any advice most appreciated.

Rgds

 

[vbaInet]

Two things spring to mind:

1. INSERTING into a table
2. Indexes and Primary Keys

Why are you inserting the derived values into a table? This adding to the execution time of your query.

If the table you are ranking against doesn't have a Primary Key or the fields you are using in your criteria are not indexed, then it will run slow.