Post reply

Select statement help to speed it up

  • July 10, 2011 at 6:33 pm

    #240616

    Is there a quicker way of doing the last SELECT statement below??

    I have a lager dataset I need it to be quick ??

    Thanks:-)

    IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

    create table #priceData(symbol varchar(10), quote_date [datetime],close_price [decimal](6,2))

    INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('AAPL','20091026', 555.75)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091027', 550.97)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091028', 547.87)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091029', 543.01)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091030', 550.00)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091102', 537.08)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091103', 535.48)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('AAPL','20091104', 534.80)

    INSERT INTO #priceData (symbol, quote_date, close_price) VALUES ('MSFT','20091026', 555.75)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091027', 550.97)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091028', 547.87)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091029', 543.01)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091030', 523.00)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091102', 537.08)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091103', 535.48)

    INSERT INTO #priceData (symbol, quote_date, close_price ) VALUES ('MSFT','20091104', 585.80)

    CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)

    SELECT A.[Symbol]

    ,YEAR(A.quote_date)*100+MONTH(A.quote_date) AS YYYYMM

    ,A.close_price AS 'Price For Max Quote_Date'

    FROM #priceData AS A

    INNER JOIN ( SELECT Symbol

    ,YEAR(quote_date)*100+MONTH(quote_date) AS 'YYYYMM'

    ,MAX(quote_date) AS 'LastDate'

    FROM #priceData

    GROUP BY symbol, YEAR(quote_date)*100+MONTH(quote_date)) B

    ON A.Symbol = B.symbol AND A.quote_date = B.LastDate

    IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

  • July 10, 2011 at 8:24 pm

    #1350872

    How large is your dataset?

    Non Clustered Indexes?

    Edit: I add my indexes after I load the Data.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 10, 2011 at 8:53 pm

    #1350874

    Thanks, I think I cracked it !

  • July 10, 2011 at 9:00 pm

    #1350876

    Digs (7/10/2011)

    Thanks, I think I cracked it !

    I believe that there are additional actions that can be take to improve the performance than when I suggested.

    You might want to find a faster method to load the data.

    Also the execution plan on the Select could be improved.

    Just wait and see what the heavy hitters have to say. 😎 Edit, no pun intended.

    Regards,

    WC

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 11, 2011 at 2:03 am

    #1350918

    Please post the execution plan from the larger resultset. Can't make any useful performance decisions on 10 rows.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 11, 2011 at 8:03 am

    #1351087

    Digs (7/10/2011)

    Thanks, I think I cracked it !

    Please share your solution. Others may either have comments or could learn from your solution.

    On a quick pass I'd be looking at workign on eliminating the calculation in the group by of the derived table. Perhaps with indexed computed column(s) or a calendar table that you can join to to get the yyyymm format that you need.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • July 11, 2011 at 1:16 pm

    #1351294

    I use the same code structure above, I have only 3.5 million records. So it takes about a 1 second. I can live with that.

    The base table has an primary key index on Date and Symbol.

    Thanks

  • July 11, 2011 at 1:26 pm

    #1351298

    Digs (7/11/2011)

    I use the same code structure above, I have only 3.5 million records. So it takes about a 1 second. I can live with that.

    The base table has an primary key index on Date and Symbol.

    Thanks

    Perhaps I missing something but did you share your solution to your problem as requested?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply