Temp Table Versus Direct Update? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp Table Versus Direct Update?

Author

Topic

beanz
Starting Member

35 Posts

Posted - 2006-03-07 : 11:20:43

Hi,

We have a couple of "maintenance" stored procedures that run each month.

Some of the stored procedures execute delete statements directly on the data:

DELETE ContactEmail 
FROM Contact INNER JOIN ContactEmail ON Contact.ContactID = ContactEmail.ptContactID
WHERE (Contact.Employed = 0)

The others populate temp tables to get the data to be deleted/changed then loop through the table deleting/updating real data as it goes:


INSERT INTO #InvoiceBatches (BatchID,InvoiceNet,InvoiceGross,InvoiceVat)
SELECT DISTINCT InvoiceBatches.BatchID, SUM(Invoice.ValueNet), SUM(Invoice.ValueGross), SUM(Invoice.ValueVat)
FROM InvoiceBatches_Link INNER JOIN Invoice ON InvoiceBatches_Link.ptInvoiceID = Invoice.InvoiceID INNER JOIN InvoiceBatches ON InvoiceBatches_Link.ptBatchID = InvoiceBatches.BatchID
GROUP BY InvoiceBatches.BatchID
HAVING (SUM(Invoice.ValueNet) <> InvoiceBatches.BatchTotalNet) OR
       (SUM(Invoice.ValueGross) <> InvoiceBatches.BatchTotalGross) OR
       (SUM(Invoice.ValueVat) <> InvoiceBatches.BatchTotalVAT)
ORDER BY MInvoiceBatches.BatchID DESC

--Get the current record we're working on
SELECT TOP 1 @BatchID = BatchID, @InvoiceNet = InvoiceNet, @InvoiceGross = InvoiceGross, @InvoiceVat = InvoiceVat
FROM #InvoiceBatches ORDER BY BatchID

SET @RowCnt = @@ROWCOUNT
WHILE @RowCnt <> 0
BEGIN
  --DO SOMETHING

  DELETE #InvoiceBatches WHERE BatchID = @BatchID
  SELECT TOP 1 @BatchID = BatchID, @InvoiceNet = InvoiceNet, @InvoiceGross = InvoiceGross, @InvoiceVat = InvoiceVat
  FROM #InvoiceBatches ORDER BY BatchID
 
  SET @RowCnt = @@ROWCOUNT
END

DROP TABLE #InvoiceBatches

Which code (if either) is the most effiecient/best practice?

Cheers,
Danny

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 11:40:27

The first. by far.

beanz
Starting Member

35 Posts

Posted - 2006-03-07 : 11:44:13

why is that then?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 12:08:19

before I answer that, let me ask you: Which do you think it more efficient?

beanz
Starting Member

35 Posts

Posted - 2006-03-07 : 12:15:29

I would have said "not a clue" but thinking about it a little more it would make sense that the first is a better option - no temp table, no select to populate it.

Am I on the right track?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 12:22:46

Definitely. Whenever possible, use set-based SQL Statements to get the job done. They are much shorter, easier to read, and almost always more efficient.

Sometimes, you do need to loop to get things done, but if you don't -- use set-based SQL.

There can be times, with a huge UPDATE, where you might need to do it in pieces, but I don't think you'd ever want to do it 1 row at a time. For example, it may be more efficient to update 1,000 rows at a time to avoid locking everything at once, instead of trying to update 1,000,000 rows all at once.

beanz
Starting Member

35 Posts

Posted - 2006-03-07 : 12:29:05

what are "set-based SQL Statements", regular SQL statements?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-07 : 12:35:14

yep, SELECT, INSERT, UPDATE, DELETE --- all are inherently designed and optimized to work with sets of rows at all once, instead of 1 row at a time. You can use them with 1 row at a time (with TOP 1, or with restrictive WHERE clauses, etc) but they work best when you allow them to operate on multiple rows all at once.

The same with JOINS. You could loop through things one row at a time, and then manually lookup corresponding values in other tables. or, you can use JOINS in your set-based SQL statements to allow SQL Server to do it all at once, which is much more efficient.

beanz
Starting Member

35 Posts

Posted - 2006-03-07 : 12:45:42

Nice one, thanks for the explanation!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources