Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate some input optimising the statement:
delete from [dbo].[tbl1] where exists ( select * from [dbo].[tbl2] t where [dbo].[tbl1].[col1] = t.[col1] and [dbo].[tbl1].[col2] = t.[col2] and [dbo].[tbl1].[col3] = t.[col3] )
The columns are as follows:
tbl1.col1 varchar(10) tbl1.col2 datetime tbl1.col3 varchar(60) tbl2.col1 varchar(10) tbl2.col2 datetime tbl2.col3 varchar(30)
I realise that the datatype on
col3 differs, I know this is bad, but would this mean the index cannot be used?
There is a non-unique clustered index on each table (not covered by this query) and a non-clustered index on both, covering all three columns included in the where clause.
tbl1 contains ~1.2 billion rows,
tbl2 contains ~30 million rows. I'm expecting around 30 million rows to be deleted from
Any help appreciated!
tbl2 are on differing filegroups, but on the same disk (SAN). Also, here is the execution plan: