Sql-server – reuse of temp table getting more time to execute

sql-server-2008temporary-tables

i'm creating temp table as,

SELECT 
    T.CD,
    T.OBJECT_CD,
    T.PRICE 
    INTO #TEMP FROM TABLE_NAME T

EDIT : ADDED CLUSTERED INDEX TO TEMP TABLE

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD
    ON #Temp (OBJECT_CD);

and using this table #TEMP two times in another query to get results as,

SELECT MAX(T1.PRICE),
        MIN(T1.PRICE),
        MAX(T2.PRICE),
        MIN(T2.PRICE)
        FROM PP INNER JOIN #TEMP T1 ON T1.OBJECT_CD = PP.OBJECT_CD
        INNER JOIN #TEMP T2 ON T2.CD = PP.CD AND T2.OBJECT_CD = PP.OBJECT_CD

but the query is taking more time to execute. when i remove any one of T1 or T2 from JOIN the query retrieves result instantly. what could be the reason?

Best Answer

I would add a second column to the clustered index statement, make it be

CREATE CLUSTERED INDEX IX_Temp_OBJECT_CD ON #Temp (OBJECT_CD, CD)

That may help, depending on how many CD's there are to OBJECT_CD's.

Related Solutions

Sql-server – Optimising join on large table

Your ix_hugetable looks quite useless because:

it is the clustered index (PK)
the INCLUDE makes no difference because a clustered index INCLUDEs all non-key columns (non-key values at lowest leaf = INCLUDEd = what a clustered index is)

In addition: - added or fk should be first - ID is first = not much use

Try changing the clustered key to (added, fk, id) and drop ix_hugetable. You've already tried (fk, added, id). If nothing else, you'll save a lot of disk space and index maintenance

Another option might be to try the FORCE ORDER hint with table order boh ways and no JOIN/INDEX hints. I try not to use JOIN/INDEX hints personally because you remove options for the optimiser. Many years ago I was told (seminar with a SQL Guru) that FORCE ORDER hint can help when you have huge table JOIN small table: YMMV 7 years later...

Oh, and let us know where the DBA lives so we can arrange for some percussion adjustment

Edit, after 02 Jun update

The 4th column is not part of the non-clustered index so it uses the clustered index.

Try changing the NC index to INCLUDE the value column so it doesn't have to access the value column for the clustered index

create nonclustered index ix_hugetable on dbo.hugetable (
    fk asc, added asc
) include(value)

Note: If value is not nullable then it is the same as COUNT(*) semantically. But for SUM it need the actual value, not existence.

As an example, if you change COUNT(value) to COUNT(DISTINCT value) without changing the index it should break the query again because it has to process value as a value, not as existence.

The query needs 3 columns: added, fk, value. The first 2 are filtered/joined so are key columns. value is just used so can be included. Classic use of a covering index.

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.

Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.

According to the Book

kdsjx

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.

It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.

I hope this answers your question.

UPDATE 2014-01-31 11:26 EST

In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:

CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.

Best Answer

Related Solutions

Sql-server – Optimising join on large table

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

UPDATE 2014-01-31 11:26 EST

Related Question