Post reply

Tuning a plain select statement

  • February 24, 2011 at 4:27 am

    #388928

    We have one procedure where after join some tables the records are put into one temporary table (this step is processed fast). Now at the end when we are doing a select statement from that temp table to show data on our frontend, it takes around 10 seconds for mere 1000 records. Can a plain select statement be made faster ?

  • February 24, 2011 at 7:56 am

    #1290999

    Can u send your queries pls.

  • February 24, 2011 at 8:40 am

    #1291032

    sql_butterfly (2/24/2011)

    We have one procedure where after join some tables the records are put into one temporary table (this step is processed fast). Now at the end when we are doing a select statement from that temp table to show data on our frontend, it takes around 10 seconds for mere 1000 records. Can a plain select statement be made faster ?

    the devil is in the details...

    some hints while we are waiting for more info:

    @temp table variables can cause slow results if they contain a lot of records...my rule of thumb is more than 500 records should be in a #temp table and not a table variable

    the WHERE statement is where most of the tuning decisions are made. you might even need to index the temp table, ore remove the temp table and use a CTE to get the final results to take advantage of indexes ont he original table.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 24, 2011 at 1:43 pm

    #1291271

    I would ask why you need to use a temp table at all. The CTE option described would likely help (but again we need the details.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • February 24, 2011 at 3:02 pm

    #1291308

    Are you 100% sure the slow part of the process is the select statement? Also, if it's just a straight select, no joins, no where clause, then you're dependent on scanning the temp table and there is no real way to improve a scan.

    But as everyone else has said, hard to know without knowing what we're talking about. Execution plans would be good too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 25, 2011 at 12:11 am

    #1291381

    Sorry Folks, After investigations we found it was because of rendering at frontend and not backend. Query is doing good.

Viewing 6 posts - 1 through 5 (of 5 total)

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