Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert returning ROWID needs to STOP! Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 1:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 1:06 PM
Points: 1, Visits: 1
When my data flow gets to the OLE DB Destination it hits a performance roadblock. The Insert that SSIS generates for the step contains "returning rowid into :#var" which I would like to be rid of.

Ex. INSERT INTO TARGET_TABLE( "TABLE_NO", "ENTRY_NO", "TABLE_
DATA", "DTTM") VALUES (:1,:2,:3,:4) RETURNING ROWID INTO :5

Variables 1-4 are defined on the mapping page. I don't even know where variable 5 is coming from.

How can I make SSIS stop generating this superfluous code?
Post #1070789
Posted Wednesday, April 27, 2011 2:32 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
Performance is probably not poor because of returning statement. Check for triggers on destination table. Or it has (too)many indexes.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1099815
Posted Wednesday, May 04, 2011 6:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 1,205, Visits: 9,313
The OLE DB destinations for non-SQL databases are pretty slow generally and isn't due to the returning of rowid. It's inserting and committing every row one by one which is *slow*.

If you use Enterprise Edition, you could try using the Attunity driver set for Oracle which is said to be much faster (and has a fast load option similar to a bulk insert I believe).

SSIS is optimised for pulling data out of external sources into SQL Server rather than the reverse, so it's never going to be as fast as a SQL Server/Flat File Destination.

Another option would be to output to a flat file, then call SQL*Loader to load the data from Oracle's end.

Post #1103030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse