November 20, 2009 at 12:04 pm
Pretty simple scenario...
Selects from the linked server are fine, downloads 500k records in a couple of minutes.
Inserts to the linked server however...30k records (single numerical column) take over 40 minutes to complete! The same is true via SSIS so I know that it's not limited to the linked server (I have tried to perform the same insert to Oracle using a varchar column instead of numeric but it takes the same amount of time).
I wouldn't say that I was scratching my head too much, but my bleeding scalp would indicate an untruth. Any ideas?
November 20, 2009 at 12:34 pm
Nicholas Cain (11/20/2009)
Pretty simple scenario...
- Windows 2003 x64 EE
- SQL Server 2008 EE
- Oracle clients 10.2xxx for both 32 and 64 bit
- Linked Server with dynamic parameters, allow in process, non transacted updates, connecting to Oracle 11G
Selects from the linked server are fine, downloads 500k records in a couple of minutes.
Inserts to the linked server however...30k records (single numerical column) take over 40 minutes to complete! The same is true via SSIS so I know that it's not limited to the linked server (I have tried to perform the same insert to Oracle using a varchar column instead of numeric but it takes the same amount of time).
I wouldn't say that I was scratching my head too much, but my bleeding scalp would indicate an untruth. Any ideas?
The first question do you have the Oracle clients all three installed? because Oracle client is required for all development because SSIS is development. I have also read that the Oracle OLE DB driver is better and your Win2003 must be at least SP2. The Oracle 11g client is almost 2gig it needs to be installed in all server running SQL Server and if remember correctly the 10g is about 1gig.
Kind regards,
Gift Peddie
November 20, 2009 at 12:39 pm
I don't have the 11G client installed (although I am testing on another machine and seeing the same performance issue). Windows is running SP2, so no issues there.
I don't have connectivity issues and have no troubles with developing what I need, it's just the insert performance. I can't figure out why the insert and insert alone is causing the problem.
December 9, 2009 at 4:20 pm
After much pain I have a solution....download the Attunity Oracle connector from http://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E&displaylang=en
Had to redo the datapumps, however went from 40 minutes to under 60 seconds!!! :w00t:
December 9, 2009 at 4:33 pm
Nicholas Cain (12/9/2009)
After much pain I have a solution....download the Attunity Oracle connector from http://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E&displaylang=enHad to redo the datapumps, however went from 40 minutes to under 60 seconds!!! :w00t:
That may be related to SQL Server 2008 optimized for the new Oracle connector but we ported SSIS to Oracle without either Oracle or Microsoft help because we all know it worked with DTS. Good to know.
Kind regards,
Gift Peddie
December 10, 2009 at 7:14 am
Just to add to the knowledge base on this, we recently had the same issue. We normally "pull" data from Oracle into SQL but for this project it was the other way around. The first time we did an insert process we figured out that it would take approxiamtely 10 hours to insert 1 million rows into Oracle. After doing a bit of research, I found that the OLEDB providers from both Oracle and Microsoft will put each individual row into a transaction. So, what you get is Begin Transaction, Insert Row, Commit Transaction for each row! UGH! Until we found the Atunity providers, we used BCP and SQL*Loader
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 10, 2009 at 8:06 am
Back in the good old days (DTS) we did the same thing. Thought I would be smart (well try to be cool) and do it in SSIS. Until that connector came along it was a mare.
The attunity connector uses batch loading, it can enforce no logging and if there are no indexes on the table can even perform parallel loads.
December 10, 2009 at 9:26 am
The first time we did an insert process we figured out that it would take approxiamtely 10 hours to insert 1 million rows into Oracle. After doing a bit of research, I found that the OLEDB providers from both Oracle and Microsoft will put each individual row into a transaction. So, what you get is Begin Transaction, Insert Row, Commit Transaction for each row! UGH! Until we found the Atunity providers, we used BCP and SQL*Loader
If you are in SQL Server 2008 I cannot understand why Microsoft will implement transaction nesting at the point of connection for ADO.NET and not the OLE DB provider. The feature is very complex but the base implementation comes from the current ANSI SQL so adding it to ADO.NET and not the OLE DB provider which is used for complex data operations is actually strange.
Kind regards,
Gift Peddie
December 10, 2009 at 9:28 am
Nicholas Cain (12/10/2009)
Back in the good old days (DTS) we did the same thing. Thought I would be smart (well try to be cool) and do it in SSIS. Until that connector came along it was a mare.The attunity connector uses batch loading, it can enforce no logging and if there are no indexes on the table can even perform parallel loads.
I never had issues using DTS with either Oracle or DB2 so when the problems came up with SSIS just worked through it. Here is the blog posting with the Microsoft employee who thinks Oracle fast loader is next to recompiling SSIS for Linux.
Kind regards,
Gift Peddie
December 10, 2009 at 9:58 am
Gift Peddie (12/10/2009)
I never had issues using DTS with either Oracle or DB2 so when the problems came up with SSIS just worked through it. Here is the blog posting with the Microsoft employee who thinks Oracle fast loader is next to recompiling SSIS for Linux.
Which is pretty stupid if you are touting SSIS as a true ETL tool.
December 11, 2009 at 6:31 am
In my experience is always better "to pull" rather than "to push" data in between environments.
Having said that, if the business requirements ask to move data from SQL Server to Oracle I would certainly entertain the idea of deploying such a process on the Oracle side.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 11, 2009 at 7:51 am
No argument there. In fact I would have loved to setup a dblink from Oracle to perform that task however trying to set one of those up against a 64bit SQL Server is beyond a nightmare.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply