mberman (3/15/2013)
I have a Data Flow Task that reads from a Flat File Source (orders.csv) and then does an OLE DB Command to do a delete from a SQL database table if a match is found. This is the SQL command in the OLE DB Command Task:Delete orders from orders with(index(IX_orderno))
WHERE orderno = ? and ordersuf = ? and oetype in ('r','t','d') and cono = ?
(The index IX_orderno is defined as non-clustered and is orderno + ordersuf + oetype + cono. )
There are 400,000 records in the orders.csv and the delete is extremely slow (over 1 hour.)
We do have 8 indexes on this table, all non-clustered. Would this have any ramifications on the deletion speed?
Help would greatly appreciated.
The OLE DB Command Task will execute a Sql Command for every row in an ssis dataset.
This is typically not what you would want to do for wholesale deletes as what this means is that your Sql Command is running, possibly thousands of times deleting one row each time.
You can check this by spinning up Profiler and you will most likely see thousands of Delete actions.
This is probably why the Delete is slow.
You could try importing the data from the flat file into a scratch/temp. table in the database and then perform a batch delete via JOINS/MERGE or some such.