Explicit Temp Table Instead of SubSelect

Explicit Temp Table Instead of SubSelect

Post by Richard A. Poluns » Sat, 20 Dec 1997 04:00:00



I have an application in which I updated a status flag based on whether
items in the table have been invoiced.   I was using an UPDATE statement
similar to the following:

UPDATE MyData SET StatCode = 'I' WHERE MyDataID IN
(SELECT DISTINCT MyDataID FROM Invoiced WHERE InvStatus = 'Y')

Due to the sizes of the tables involved I was having problems with timeouts,
so I decided to try breaking the subselect out into an explicit temporary
table.  After all, that's more or less what SQL Server is doing anyway.

I now have something similar to the following:

SELECT DISTINCT MyDataID INTO #temptbl FROM Invoiced WHERE InvStatus = 'Y'
UPDATE MyData SET StatCode = 'I'
  FROM MyData m, #temptbl tpt
 WHERE m.MyDataID = tpt.MyDataID
DROP TABLE #temptbl

I'm seeing a marked improvement (i.e., no time-outs) but I wondered if there
were any pitfalls I might be overlooking.

I'll appreciate any feedback on this subject.  The approach looks so simple
and reasonable that I can't help looking for the catch.

Richard A. Polunsky
Houston, Texas

 
 
 

Explicit Temp Table Instead of SubSelect

Post by Balajee Subramania » Sat, 20 Dec 1997 04:00:00


Richard,

Why don't you try joining  MyData and Invoiced directly instead of
creating a temporary table first and dropping it ?

UPDATE MyData SET StatCode = 'I'
  FROM MyData m,Invoiced Inv WHERE InvStatus = 'Y'
 and m.MyDataID = Inv.MyDataID

Subqueries specially with IN clause are generally much slower than joins.
HTH,
Balajee


> I have an application in which I updated a status flag based on whether
> items in the table have been invoiced.   I was using an UPDATE statement
> similar to the following:

> UPDATE MyData SET StatCode = 'I' WHERE MyDataID IN
> (SELECT DISTINCT MyDataID FROM Invoiced WHERE InvStatus = 'Y')

> Due to the sizes of the tables involved I was having problems with timeouts,
> so I decided to try breaking the subselect out into an explicit temporary
> table.  After all, that's more or less what SQL Server is doing anyway.

> I now have something similar to the following:

> SELECT DISTINCT MyDataID INTO #temptbl FROM Invoiced WHERE InvStatus = 'Y'
> UPDATE MyData SET StatCode = 'I'
>   FROM MyData m, #temptbl tpt
>  WHERE m.MyDataID = tpt.MyDataID
> DROP TABLE #temptbl

> I'm seeing a marked improvement (i.e., no time-outs) but I wondered if there
> were any pitfalls I might be overlooking.

> I'll appreciate any feedback on this subject.  The approach looks so simple
> and reasonable that I can't help looking for the catch.

> Richard A. Polunsky
> Houston, Texas



 
 
 

Explicit Temp Table Instead of SubSelect

Post by Roy Harv » Sat, 20 Dec 1997 04:00:00


Richard,

The performance problem with the first version might relate as much to
using IN as anything else.  You might try an EXISTS alternative with a
coorelated subquery such as the one below.  I almost always use EXISTS
rather than IN; EXISTS stops searching in the subquery when the first
match is found.  EXISTS would also work if the join beween the tables
required multiple columns.

UPDATE MyData
   SET StatCode = 'I'
 WHERE EXISTS (SELECT * FROM Invoiced
                WHERE InvStatus = 'Y'
                  AND MyData.MyDataID = Invoiced.MyDataID)

Roy

 
 
 

1. Which is better Implicit or Explicit drop table for #temp tables

Hello All,

We have some locking problems with our tempdb system tables (who
doesn't).  One thing that I noticed was that some new code went up
that creates alot of #temp tables and then throughout the code
executes truncates and drops of the tables.  My question is when the
system automatically cleans up #temp tables upon exit is this done by
some system process?  My thinking is yes, and the truncates and drops
throughout the code are causing more unnecessary hits against tempdb
plus I think it is adding overhead to the timing of the proc.  Would
it be less expensive to let the system clean up the temp tables on
exit?

We are running 11.9.2.3 ebf 9920 on Compaq Tru-Unix64  5.1

TIA,

Carl A. Dotger

2. SQL dB wont autogrow

3. need help...Using @TABLE variable instead of #temp tables with dynamic SQL (sp_executesql)

4. Charles Simonyi

5. Other ways to store data instead temp table

6. Problems with password on Win95 client connectiong to OI1.2

7. Subselect to show 1 to 1 instead of 1 to many

8. Temp table vs Global Temp table

9. returning temp result of temp table to ADO/ASP

10. Difference between create table #temp and ##temp ?

11. temp table problem with global temp option

12. Create table temp or select into temp.