Poor performing delete operation....

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 2005 Forums

Transact-SQL (2005)

Poor performing delete operation....

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-04-17 : 20:41:13

Hi,
a little background...

I have an app that loads data from CSV files into tables in sql server..
i do the loading in my application, as i cannot use bulk insert on shared hosting etc...

I have stored procs that perform actions such as dropping foreign keys, truncating tables - then the data import is performed, then there is another stored proc to "clean" the data -
it runs this simple query:

DELETE FROM tDeal
WHERE HandsetID NOT IN (SELECT HandsetID FROM tHandset)

DELETE FROM tDeal
WHERE TariffID NOT IN (SELECT TariffID FROM tTariff)

DELETE FROM tDeal
WHERE RetailerID NOT IN (SELECT RetailerID FROM tRetailer)

It takes a little while to execute
(tDeal contains around 250k rows)

is there anything else i could do?

I have to run something like this, as the data from the CSV files is not particulaly clean

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-17 : 22:57:38

try this one than not in
DELETE t
FROM tDeal t
LEFT JOIN tHandset x ON x.HandsetID = t.HandsetID
WHERE x.HandsetID IS NULL

DELETE t
FROM tDeal t
LEFT JOIN tTariff x ON x.TariffID = t.TariffID
WHERE x.TariffID IS NULL

DELETE t
FROM tDeal t
LEFT JOIN tRetailer x ON x.RetailerID = t.RetailerID
WHERE x.RetailerID IS NULL

r use not exists too... not in will having less performance than not exists and left join

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-18 : 00:32:18

Do you have indexes in HandsetID,TariffID and RetailerID?

alexjamesbrown
Starting Member

48 Posts

Posted - 2009-04-18 : 02:01:12

Thanks, will try that
No... I don't have indexes on the tables yet.. Where should I have?

Alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources