Option for OLE DB Command?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Option for OLE DB Command??

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2014-08-11 : 07:31:19

Hi All,

I need your help..I have one ETL package for import excel records to DB table.
Task details
1. Excel Source
2. Derived Column - concatenate excel column 1 and excel column 3
3. Conditional Split 1
4. Conditional Split 2
5. Data Conversion
6. OLE DB Command - delete matching records from DB table matching on composite PK Keys
7. Insert records.

What I notice is that there are multiple records in DB table matching on excel Column 1 + excel column 4 + excel column 5

Composite PK is Column 1 "-" Column3 + excel column 4 + excel column 5

Delete condition is like

delete from table xyz
where ID like 'Excel Column 1%' AND CITY = excel column 4 AND Contry = excel column 5
this step is time consuming as OLE DB command taking each row set to delete so i need to avoid OLE DB Command task.

Please help me out

T.I.A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 07:59:03

It's not the OLDEDB command that is the problem here. It's the table and its indexes. SQL has to find rows matching your criteria:


where ID like 'Excel Column 1%' AND CITY = 'excel column 4' AND Country = 'excel column 5'

If the columns ID, City and Country are not indexed, SQL will have to scan the whole table to find them. If the table is big, this will take some time. If you can build an index on these three columns, the execution time should be dramatically reduced.

Try looking at the execution plan for the query using SSMS. Likely you will see table scans or index scans instead of index seeks.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2014-08-11 : 08:37:51

Thanks for reply..

but my business requirement is like this only first delete matching records and then insert.

And this table is not controlled by us so no way to do modification in table design.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 09:27:43

Your DELETE command will do what you want. The performance is heavily influenced by the indexes (or lack thereof) on the table. Your DBA should be able to help you with that.

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2014-08-13 : 05:47:51

Thanks..

Just wanted to understand performance point of view of OLE DB Command...

My excel file has records which matches with records in table so i want to delete and then insert records...
For delete i am using OLE DB command and used script

Delete from table
where condition..

Then..used another task to insert records..

suppose my excel file has 10 records out of which 4 records are matching with table data column then OLE DB Command execute 4 times for delete operation for same records to delete even though it got deleted for first record itself???? As i am seeing that OLE DB Command "Executes an SQL command for each row in a dataset."

T.I.A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 07:46:04

OLE DB Command "Executes an SQL command for each row in a dataset." is correct. Because this is in a dataflow task.

OTOH you could use the SQL Command task before you run the dataflow task to do the delete operation. Then there will be only one call.

There's another option: Use your dataflow task to load the Excel data to a staging table then use a SQL Command task to invoke the MERGE statement, which will do both deletes and inserts in one pass and will run entirely on the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources