CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008
Alex Gordon
Alex Gordon

Reputation: 60821

Speeding up deletes that have joins

i am running a stored procedure to delete data from two tables:

delete from TESTING_testresults
from TESTING_testresults
inner join TESTING_QuickLabDump
on TESTING_QuickLabDump.quicklabdumpid = TESTING_TestResults.quicklabdumpid
where TESTING_quicklabdump.[Specimen ID]=@specimen



delete from TESTING_QuickLabDump
from TESTING_Quicklabdump
where [specimen id]=@specimen

one table is 60m rows and the other is about 2m rows

the procedure takes about 3 seconds to run.

is there any way i can speed this up? perhaps using EXISTS?

meaning IF EXISTS...THEN DELETE - because the delete should not be occurring every single time

something like this

if @specimen exists in TESTING_QuickLabDump then do the procedure with the two deletes

thank you !!!

Upvotes: 1

Views: 112

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Beside indexing "obvious" fields, also look in your database schema and check if you have any FOREIGN KEYs whose ON DELETE CASCADE or SET NULL might be triggered by your delete (unlike Oracle, MS SQL Server will tend to show these in the execution plan). Fortunately, this is usually fairly easy to fix by indexing the child endpoint of the FOREIGN KEY.

Also check if you have any expensive triggers.

Upvotes: 1

Brandon Moore
Brandon Moore

Reputation: 8790

For a table with 60 mil rows I would definitely look into partitioning the data horizontally and/or vertically. If it's time-sensitive data then you ought to be able to move old data into a history table. That's usually the first and most obvious thing people do so I would imagine if that were a possibility you would have already done it.

If there are many columns then it would definitely benefit you to denormalize the data into multiple tables. If you did this, I would suggest renaming the tables and creating a view of all the partitioned tables named after the original table. Doing that should ensure existing code isn't broken.

If you 'really' want to fine tune the speed then you should look into getting a faster hard drive and learn a little about hard drives work. Whether the data is stored towards the inner or outer section of the hd will affect speed of access slightly for example. And solid state hard drives have come a long way so you might look into getting one of those.

Upvotes: 1

krlmlr
krlmlr

Reputation: 25474

Rewriting the query probably wont help speeding this up. Use the profiler to find out which parts of the query are slow. For this, make it profiler output the execution plan. Then, try adding appropriate indexes. Perhaps one or both tables could use an index over [specimen id].

Upvotes: 2

Related Questions