SQL Server Performance

sp with temporary tables runs forever ?

Discussion in 'SQL Server 2008 General DBA Questions' started by acki4711, Mar 31, 2010.

  1. acki4711 Member

    Hi all,Have a stored proc that creates three tables and uses these tables in a select within the same sp.
    If I create these tables in my db the sp runs ~ 20 sec.
    If I use temporary tables instead, the sp runs forever.
    Does any1 have a logical explanation for this behavior ??

    TIA
    acki
  2. FrankKalis Moderator

    Could you please post the code?
  3. acki4711 Member

    @Frank
    Sorry no, does not work.
    Is there a limit with the size of a post in this forum ???
  4. Adriaan New Member

    If you run the version that creates the tables, then how large do the tables end up? Check against file size and free space in tempdb's file(s), adjust file size(s) and growth factor(s) accordingly.
  5. acki4711 Member

    @Adriaan
    sizes for the temporary tables are:
    9 rows, 0.008 MB
    2860 rows, 0.345 MB
    10100 rows, 0.133 MB
  6. Adriaan New Member

    OK, that's clearly peanuts - assuming this is against production data.
    Are you by any chance using INTO like this:
    SELECT columns
    INTO #tmp_table
    FROM permanent_table
    This can cause locking issues. Always best to use a CREATE TABLE statement followed by an INSERT INTO query.
  7. acki4711 Member

    @Adriaan
    I use:
    DECLARE @Table0 TABLE ( Field1 ... )
    INSERT INTO @Table0
    SELECT FROM Permanent_Table0...
    DECLARE @Table1 TABLE( Field1 ...)

    INSERT INTO @Table1
    SELECT
    ...
    FROM
    Permanent_Table1
    WHERE
    @Table0.Field1 = Permanent_Table1.Field1
    checking for locking issues...
  8. Adriaan New Member

    Try adding WITH (NOLOCK) after each table in your FROM clauses.
  9. EugeneHwang New Member

    You are using table variable here but not temp table as described. No full script posted here to be sure. But in general, for big results sets in @table0 or @table1, you might be better off to use #temp table style to gain the statistics/execution plan or to create extra index on temp table to help the join.
    Adding WITH (NOLOCK) could help too if it is locking issue.
    Eugene
  10. vepraveen@yahoo.com New Member

    For larger data like above 10K rows the table variables are not good performers. Change the table variable to #temp table and add an additional filter to your query to get just 100 rows or so and see how it goes if that succeeds then remove the filter and run.
    Good luck...
  11. EHarman New Member

    In addition to what everyone else has said with using an actual Temp table instead of a table variable, also remember you can create an index on a temp table.
  12. acki4711 Member

    @Frank
    lines where temporary tables have been replaced with 'real' tables are ---

Share This Page