July 26, 2009 at 10:24 am
Hi All,
I am getting the timeout error while executing the below delete statement.
table1has 26208623 records
table2 has 43 records
DELETE FROM table1 WHERE ContractType NOT IN (SELECT DISTINCT ContractType FROM table2 with (nolock)
WHERE TARGETTABLE ='abc' AND QUERYTYPE IN ('rev1','rev2') AND ACTIVE = 1 )
Can some one help me, How to optize delete statement.
like If we place table level locks will it improve
or
If I delete some indexes will it improve...
If I create non clustered index on table1.ContractType will it improve..
Please give me your suggesions on how to improve delete statement performance......
Rajesh Kasturi
July 26, 2009 at 10:33 am
26 million records to scan whether to delete or not, and it requires a table scan because of the NOT IN;
instead of NOT in, can you join instead? is there an index on ContractType?
do the delete from SSMS where there is no timeout.
here's how i would create the deelte:
DELETE MyAlias
From table1 MyAlias
LEFT OUTER JOIN table2 ON MyAlias.ContractType = table2.ContractType
WHERE MyAlias.TARGETTABLE ='abc'
AND MyAlias.QUERYTYPE IN ('rev1','rev2')
AND MyAlias.ACTIVE = 1
and table2.ContractType is NULL --no matching record in table1
Rajesh kasturi (7/26/2009)
Hi All,I am getting the timeout error while executing the below delete statement.
table1has 26208623 records
table2 has 43 records
DELETE FROM table1 WHERE ContractType NOT IN (SELECT DISTINCT ContractType FROM table2 with (nolock)
WHERE TARGETTABLE ='abc' AND QUERYTYPE IN ('rev1','rev2') AND ACTIVE = 1 )
Can some one help me, How to optize delete statement.
like If we place table level locks will it improve
or
If I delete some indexes will it improve...
If I create non clustered index on table1.ContractType will it improve..
Please give me your suggesions on how to improve delete statement performance......
Lowell
July 26, 2009 at 10:45 am
Hi
instead of NOT in, can you join instead?
I will try query suggesion.
is there an index on ContractType?
There is non clustered index on Contract Type, I suggested dev team to create clustered index.
do the delete from SSMS where there is no timeout?
There were deleting from VB code so I suggested to give the timeout parameter inc onnection string.
Rajesh Kasturi
July 26, 2009 at 10:48 am
I have question
Will SQL Server use Index Scan even though I have clustered index on table If I use NOT in clause in sql query?
Rajesh Kasturi
July 27, 2009 at 11:47 pm
Hi,
Instead of SubQuery, you should write query using JOIN.
Secondly, you may try to delete rows in batch of records like 10000 records and then commit. This will not take much resources e.g. Transaction Log etc.
July 28, 2009 at 12:10 am
Hi Rajesh,
You should JOIN the tables instead of using SubQuery.
Moreover, you can try deleting rows in batch as below
SET ROWCOUNT somenumber -- this could be 100000 or any number you decide.
WHILE 1 = 1
BEGIN
DELETE FROM table1 WHERE ContractType NOT IN (SELECT DISTINCT ContractType FROM table2 with (nolock)
WHERE TARGETTABLE ='abc' AND QUERYTYPE IN ('rev1','rev2') AND ACTIVE = 1 )
IF @@ROWCOUNT = 0 BREAK
END
SET ROWCOUNT 0
This will delete as many records as you specify at SET ROWNUMBER somenumber.
Donot forget to reset SET ROWCOUNT 0.
July 28, 2009 at 1:09 am
Hi
If you intend to use batch deletes use TOP instead of SET Rowcount. Check SET Rowcount in BOL for more details on this.
Did you check for any locking issues? I would suggest you try the join query for delete and then check for locking if u face any issues.
BTW what about using stored procedures instead?
"Keep Trying"
July 28, 2009 at 1:17 am
Hi
If you intend to use batch deletes use TOP instead of SET Rowcount. Check SET Rowcount in BOL for more details on this.
Did you check for any locking issues? I would suggest you try the join query for delete and then check for locking if u face any issues.
BTW what about using stored procedures instead?
"Keep Trying"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply