I have a query which is taking hours to complete. It is a self join on one table. Can some one please help me to tune this, so that it is completed in an efficient time. The table has only 1000 rows. This query runs on SQL server 7.0 (Windows) from a stored procedure.
The query is below.
from wtn a, wtn b
where a.code = b.code
and a.num = b.num
and a.unit = 'DLMMLT'
and b.unit != 'DLMMLT'
The data is like the following:
code num unit
---- ---- ----
123456 2345671990 ifty
123456 2345671990 DLMMLT
456123 4567891230 jkty
456123 4567891230 DLMMLT
All I need to do is to delete the duplicate rows with "unit = DLMMLT". The duplicate rows should be identified with "code" and "num" columns. The primary key includes all the three columns. One index exists for all the three columns.
I need this urgent. Any help is highly appreciated.
I understand that you want to delete x number of rows, but to establish a baseline, a good start is to see how long 1 record takes to delete -- It may not be that you are deleting 1000 records that only takes a long time to complete - 1 record could take a long(respectively) time as well - which would mean that your query is ok (especially if you use explicit values of the record that you know needs to be deleted and not try to pull the information by trying to delete all the records).
Sorry to get back to you so late. The reason was that the query tooks so long time to run. I ran the query on a mirror database to production with 18 millim rows. To delete 6 rows, the query took
21 hrs 31 min 34 sec.
I badly need help in tuning this query to reduce the time of execution.
I am going to rerun the same query with a clustered index created on unit.
One row got deleted with hard coded values in less than one second.
I tested the original delete query after creating the clustered index on 'unit'. The original delete query tihis time surprisingly finished in just 54 secs. But this may not happen always since there is lot of update/insert/delete activity on the table.
That's why I wanted to tune the query too, besides having a clustered index.
The table strucutre is as follows:
name type size
------- -------- -------
code char 6
num decimal 9
unit varchar 8
one full-text index exists on all the three columns.
Primary key includes all three columns.
No other dependencies.