Post reply

Time out loading data into a Local Table Variable

  • May 3, 2012 at 2:39 am

    #257330

    Hi,

    We have a stored procedure that has run fine for years. Last night it started to take an age to execute.

    We narrowed it down to an insertion into a Table Variable.

    The Select statement ran perfectly on its own and we change the volumn of data being inserted all the way down to a single row, but it was still taking just as long.

    There was an Index hint on the Select statement originally, but surely this would impact the Select and not the Insert?

    Thinking it must be a memory issue (as it was an @table) we tried to clear the memory (DBCC FreeProcCache and DBCC DropCleanBuffers) and even tried a reboot, but to no avail.

    Eventually we changed the proc to use a temp table #table and the performance picked up, however after years of running this proc 24/7 I'm concerned as to why it would happen.

    Any help, guidance, advice, anythign really is appreciated.

    The T-SQL is below.

    Thanks

    Giles

    We're using SQL Server 2008 r2 64bit Standard Edition.

    DECLARE @temp table (sid nvarchar(6) , source_id NVARCHAR(50), data nvarchar(max), agent_processed BIT);

    INSERT INTO @temp (sid,source_id,data, agent_processed)

    SELECT TOP (@batch_size) sid,source_id,data,agent_processed

    FROM Item WITH(READUNCOMMITTED,NOLOCK)

    WHERE list_sid = @list_sid AND ([sid] > @last_item_processed OR @last_item_processed IS NULL)

    ORDER BY [unique_key] asc;

  • May 3, 2012 at 3:54 am

    #1482889

    From BOL.

    Best Practices

    Do not use table variables to store large amounts of data (more than 100 rows). Plan choices may not be optimal or stable when a table variable contains a large amount of data. Consider rewriting such queries to use temporary tables or use the USE PLAN query hint to ensure the optimizer uses an existing query plan that works well for your scenario.

    table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

    Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. Queries that read table variables without modifying them can still be parallelized.

    Limitations and Restrictions

    Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 1 (of 1 total)

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