Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to have suggestions on improving the performance of some queries. These will be run on SQL Server 2008 R2 against a single table called dbo.bids

In a production environment the table is expected to hold up to 1,000,000 records. Here is what I have upto now.

    /* dbo.Bids structure
    bid     decimal (10,2)
    auctionid   bigint
    msisdn      bigint
    lineid      bigint
    timestamp   bigint

CONSTRAINT [PK_Raw] PRIMARY KEY CLUSTERED 
(
    [auctionid] ASC,
    [bid] ASC,
    [msisdn] ASC,
    [lineid] ASC,
    [timestamp] ASC
)

    dbo.bids test data:

    bid     auctionid   msisdn  lineid  timestamp
    1.01    1011        9999    1       0
    1.20    1011        8888    2       1
    0.98    1011        7777    4       2
    1.01    1011        6666    5       3
    0.98    1011        5555    7       4
    0.99    1011        6666    8       6
    0.99    1011        4444    9       6
    0.99    1011        6666    10      6
    0.99    1011        3333    11      8
    1.20    1011        1234    13      10
    1.01    1011        8888    14      11
    */


    DECLARE @auctionid bigint;
    DECLARE @msisdn bigint;
    SET @auctionid = 1011;
    SET @msisdn = 8888;

    SELECT TOP 1 [bid], [msisdn] FROM [dbo].[bids] 
    WHERE [auctionid] = @auctionid AND [bid] = 
        (
            SELECT [bid] FROM 
                (
                    SELECT TOP 1 [bid], COUNT([bid]) AS [tally] FROM [dbo].[bids] 
                    WHERE [bid] >= 0.01 AND [auctionid] = @auctionid 
                    GROUP BY [bid] ORDER BY [tally] ASC, [bid] ASC
                ) AS LowestTally
         ) ORDER BY [lineid] ASC;

    /* RESULTS SHOULD BE
    bid msisdn
    0.98    7777
    */ 


    SELECT msisdn, MinBid, Ranking FROM 
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
    (select [bid] from [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = (SELECT [tally] FROM (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally) )
     GROUP BY msisdn ) AS Temp  ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    7777    0.98    1
    5555    0.98    2
    8888    1.20    3
    1234    1.20    4 
     */


    SELECT msisdn, MinBid, Ranking FROM 
    (SELECT msisdn, MIN(bid) as MinBid, ROW_NUMBER() OVER(ORDER BY MIN(bid), MIN(lineid)) AS Ranking FROM [Bids] WHERE [auctionid] = @auctionid and [bid] IN 
        (SELECT [bid] FROM [Bids] WHERE [auctionid] = @auctionid and [bid] >= 0.01 GROUP BY [bid] HAVING COUNT([bid]) = 
            (SELECT [tally] FROM 
                (SELECT TOP 1 bid, COUNT(bid) AS [tally] FROM [dbo].[bids] GROUP BY bid ORDER BY [tally] ASC) AS LowestTally))
     GROUP BY msisdn ) AS Temp  WHERE msisdn = @msisdn ORDER BY MinBid;

     /* RESULTS SHOULD BE
    msisdn  MinBid  Ranking
    8888    1.20    3
     */
share|improve this question
    
Please be more specific as to you performance expectations. –  RolandoMySQLDBA Aug 27 '13 at 12:07
    
The expectation is to reduce the the number of database read operations per query. –  VasooV Aug 27 '13 at 12:38
    
Can you describe the indexes that already exist on dbo.Bids? –  Aaron Bertrand Aug 27 '13 at 12:40
    
@Aaron, edited the OP to include the index. –  VasooV Aug 27 '13 at 12:45
1  
And I usually don't ask for word problems, but can you describe exactly what these three results are supposed to achieve? It might be easier to get context that way than to try and reverse engineer the query logic. –  Aaron Bertrand Aug 27 '13 at 13:35

1 Answer 1

up vote 1 down vote accepted

For the first query, this version eliminates a CI seek and a TOP N Sort, and cuts the reads against dbo.Bids in half. It performs more reads overall, but those are caused by a worktable, not an actual table. I believe this gives your desired results, but without more sample data, I can't be sure that it matches your requirements.

SELECT TOP (1) bid, msisdn
  FROM dbo.Bids
  WHERE bid >= 0.01 AND auctionid = @auctionid
  ORDER BY COUNT(*) OVER (PARTITION BY bid), bid, lineid;

For your second query, this gets rid of a CI seek, a sort, a nested loop join, and a few other operators:

;WITH x AS 
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS 
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
  FROM x ORDER BY tally
)
SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid) 
FROM y ORDER BY ranking;

For the third query, this gets rid of two sorts, a CI seek, and cuts the table reads in half.

;WITH x AS 
(
  SELECT msisdn, MinBid = bid, lineid, tally = COUNT(*) OVER (PARTITION BY bid)
  FROM dbo.Bids AS t WHERE auctionid = @auctionid AND bid >= 0.01
),
y AS 
(
  SELECT TOP (1) WITH TIES msisdn, MinBid, lineid 
  FROM x ORDER BY tally
),
z AS 
(
  SELECT msisdn, MinBid, ranking = ROW_NUMBER() OVER (ORDER BY MinBid, lineid)
  FROM y
)
SELECT TOP (1) msisdn, MinBid, ranking FROM z WHERE msisdn = @msisdn ORDER BY ranking;

For all of these, you'll have to test whether this leads to better or worse overall performance in your environment. I only tested (both accuracy and plan shape / duration) using your limited sample data. Things could go askew depending on data distribution, cardinality estimates, any other indexes, etc. etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.