SQL Server Performance

taking time to retrieve records from temperary table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by ramanajvv, Feb 11, 2008.

  1. ramanajvv New Member

    Hi,
    I have one temperary table (#results) having the 30000 records.
    If i give simple query "select * from #results" , taking time of 25 min to complete query sucessfully.
    is there any possibility to reduce the time.
    Please provide the advices.
    ramanajvv, Feb 11, 2008
    #2

  2. Luis Martin Moderator

    Welcome to the forum!.
    Are there others users inserting or updating #results at the same time?
    Luis Martin, Feb 11, 2008
    #3

  3. satya Moderator

    Do you really need all of those rows from that temp table?
    Why not choose the columns you want to see the performance, in any case make sure the TEMPDB is not having issues in this case. Better to view SQL server error log for any warnings or errors.
    satya, Feb 12, 2008
    #4

  4. Adriaan New Member

    Check your code to see if it is creating the temp table using this syntax:
    SELECT *
    INTO #MyTempTable
    FROM MyTable
    This syntax will try to lock the source table. Always use the following syntax instead:

    CREATE TABLE #MyTempTable (column definitions)
    INSERT INTO #MyTempTable (list of columns)
    SELECT list of columns
    FROM MyTable
    Another suggestion is to use a table variable instead of a temporary table.

    And in either case, add a primary key.
    Adriaan, Feb 12, 2008
    #5

  5. susanthab New Member

    Possible reasons could be,
    1. If your table does not contain a primary key, it can take more time
    1. Check the size of the tempdb log. If the tempdb is not optimized your query would take more time. Restart your server, so that tempdb will be initialized and then re-run your query.
    1. If your query using a remote server, need to check the network bandwidth.
    susanthab, Feb 13, 2008
    #6

Share This Page