June 3, 2009 at 12:27 am
I am currently extracting and converting data from Oracle to SQL Server 2008.
Performance is terrible both with Linked servers, and direct using DTS/SSIS.
1 million rows of data loaded from Db to Db take 1.5 hours. But exactly the same data exported to a text file and then loaded via DTS/SSIS takes 5 minutes.
Someone suggested that different character sets (Collations?) may be causing a conversion overhead.
Our Oracle DB is using WE8DEC, and SQL Server default is Latin1_General_CI_AS.
Is there a closer/better match? or even better a direct mapping between Oracle and MSSQL?
Any suggestions would be greatly appreciated.
June 3, 2009 at 9:14 am
Here is the link for the meaning of the Oracle collation and the SQL Server 2008 collation page you can use 850 code page with accent and wide character. That will be close to the definition of the Oracle code page and yes what you have now is not good enough.
http://en.wikipedia.org/wiki/Multinational_Character_Set
http://msdn.microsoft.com/en-us/library/ms144250.aspx
Kind regards,
Gift Peddie
June 3, 2009 at 9:36 am
Other issues that I can think of between servers transfer is,
1)N/W bottleneck: Do you see ASYNC_NETWORK_IO when transfer is between servers when u use sysprocesses.
2)constraints enabled in destination server while transfer
Those were the two problems I had faced while doing the same thing that u are.
June 3, 2009 at 5:50 pm
Mayank Khatri (6/3/2009)
Other issues that I can think of between servers transfer is,1)N/W bottleneck: Do you see ASYNC_NETWORK_IO when transfer is between servers when u use sysprocesses.
2)constraints enabled in destination server while transfer
Those were the two problems I had faced while doing the same thing that u are.
Yes there are constraints enabled mostly NOT NULL on just about every damn field.. However we have ignored this becasue the same tool for loading is being used and from a flat file runs fine. So I assumed the bottleneck is not in the loading of the data, but is in the reading/interpreting from the source.
Edit: Just confirmed, 100K rows from oracle into MSSQL table with constraints and index, and exact same load into table without constraints or indexes takes approximately same time. ~80secs, vs ~76 secs.
I know that interpreting different character sets between oracle database costs performance. We're just wondering if we could rule out the same thing from oracle to MSSQL
June 3, 2009 at 6:59 pm
Gift Peddie (6/3/2009)
Here is the link for the meaning of the Oracle collation and the SQL Server 2008 collation page you can use 850 code page with accent and wide character. That will be close to the definition of the Oracle code page and yes what you have now is not good enough.
Thanks...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply