Home » RDBMS Server » Performance Tuning » To improve execution time of query
To improve execution time of query [message #288296] Sun, 16 December 2007 23:29 Go to next message
Messages: 95
Registered: February 2007

Below is the query takking too much time in deleting records.

WHERE exists (SELECT 1 FROM tab2 b
WHERE a.col1 = b.col2);

The columns col1 and col2 are indexed. The select subquery is returning 6 million rows.

Its taking 4 minutes for deleting all these records.

Is there any efficient way writing the above query so that the performance can be increased.

Thanks in advance
Re: To improve execution time of query [message #288298 is a reply to message #288296] Sun, 16 December 2007 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65085
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Spent time spent here is not in the select part but in the delete one.
(You can see it just using select instead of delete.)
If you delete a great part of your table it is more efficient to save the rows you want to keep, truncate the table and reinsert the saved rows.

Re: To improve execution time of query [message #288314 is a reply to message #288298] Mon, 17 December 2007 01:08 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So the sub-query returns 6M rows. Does that mean it DELETEs 6M rows? If so, I agree with Michel. I'm actually a bit impressed that it can delete 6M rows in 4 minutes. That's pretty fast.

If you are deleting much, much fewer rows (say, < 100,000), then you may be using indexes inappropriately.

Tell us how many rows in BOTH tables, and how many rows will be deleted.

Ross Leishman
Previous Topic: Some questions on SQL tuning
Next Topic: While SQL tuning : Is NULL, In vs Exists
Goto Forum:

Current Time: Mon Jul 24 08:15:14 CDT 2017

Total time taken to generate the page: 0.07712 seconds