Performance - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Performance

Author

Topic

hubschrauber
Starting Member

16 Posts

Posted - 2006-04-05 : 12:06:36

Hello,

I build a query in SQL-server 2000 but i'm not happy with the performance, it takes about 15 minutes to execute the query (4 min INSERT and 11 min UPDATE). The table tbl_total has 3 million records and an index on Contract and Item, the table contracts has 1 million records and a key on Contract and Item.
How can I speed up this query, is it for example possible to put an index on @table (internal table)?

Thanx in advance!

DECLARE @table TABLE (Contract nvarchar(15), Item nvarchar(12), Change_date datetime)

INSERT INTO @table
SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date)
FROM tbl_total TOT
WHERE EXISTS (SELECT 'X' FROM contracts CONT
WHERE TOT.Contract = CONT.Contract
AND TOT.Item = CONT.Item)
GROUP BY TOT.Contract, TOT.Item

UPDATE contracts
SET contracts.Change_date = TT.Change_date
FROM contracts INNER JOIN @table TT On
contracts.Contract = TT.Contract AND
contracts.Item = TT.Item

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-05 : 12:49:56

I take it Contract, Item is unique in contracts
try
1.
UPDATE contracts
SET contracts.Change_date = TT.Change_date
FROM contracts
INNER JOIN
(
select Contract, Item, Change_date = max(Change_date)
from tbl_total TOT
GROUP BY TOT.Contract, TOT.Item
) TT
on contracts.Contract = TT.Contract
and contracts.Item = TT.Item

2.

select Contract, Item, Change_date = max(Change_date)
into #a
from tbl_total TOT
GROUP BY TOT.Contract, TOT.Item

create unique index ix on #a (Contract, Item)

UPDATE contracts
SET contracts.Change_date = TT.Change_date
FROM contracts
INNER JOIN
#a TT
on contracts.Contract = TT.Contract
and contracts.Item = TT.Item

3.
DECLARE @table TABLE (Contract nvarchar(15), Item nvarchar(12), Change_date datetime, unique (Contract, Item))
INSERT INTO @table
SELECT TOT.Contract, TOT.Item, MAX(TOT.Change_date)
FROM tbl_total TOT
WHERE EXISTS (SELECT * FROM contracts CONT
WHERE TOT.Contract = CONT.Contract
AND TOT.Item = CONT.Item)
GROUP BY TOT.Contract, TOT.Item

UPDATE contracts
SET contracts.Change_date = TT.Change_date
FROM contracts INNER JOIN @table TT On
contracts.Contract = TT.Contract AND
contracts.Item = TT.Item

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources