SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Option for OLE DB Command??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 08/11/2014 :  07:31:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1077 Posts

Posted - 08/11/2014 :  07:59:03  Show Profile  Reply with Quote
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.
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 08/11/2014 :  08:37:51  Show Profile  Reply with Quote
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.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1077 Posts

Posted - 08/11/2014 :  09:27:43  Show Profile  Reply with Quote
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.

Go to Top of Page

under2811
Constraint Violating Yak Guru

India
356 Posts

Posted - 08/13/2014 :  05:47:51  Show Profile  Reply with Quote
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

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1077 Posts

Posted - 08/13/2014 :  07:46:04  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000