question

anurag avatar image
anurag asked

LINKED Server, Performance issue while migration data from SQL Server to Oracle

Hi, We have many tables with millions of records at SQL Server side and we want to migrate that data to Oracle tables. To Setup a linked server between SQL Server and Oracle,We tried with provider 'OraOLEDB.oracle' and 'Microsoft OLE DB for Oracle' but couldn't get success because they failed while migrating big tables to Oracle database, also there were many other errors for those providers.But finally the combination of provider 'MSDASQL' with system DSN(IA_SQL2ORA) to target Oracle worked sucessfully for us, as we can now migrate data from SQL Server to Oracle. Syntax for Linked server is : EXEC sp_addlinkedserver @server='IA_LINKEDSERVER',@srvproduct='IA_SQL2ORA',@provider='MSDASQL', @datasrc='IA_SQL2ORA' But the problem is poor performance. To migrate 24 GB of data, it took 80 hrs ! Even for smaller database migration it is taking huge amount of time. We have DB script that has simple code to migrate all our tables, here is sample : Insert openquery(IA_LINKEDSERVER,'select DBTime,DBUser,Description,SortOrder from ia_sys_status') select DBTime,DBUser,Description,SortOrder from [SQLSERVERDB].[dbo].[ia_sys_status] During migration when we observe at Oracle side,there are lot of recursive calls happens.At oracle side first all the data is kept to buffer and once all the data from SQL Server tables is selected, Oracle insert that data to respective oracle table in one chunk. Could some one tell me what would be the best approch to improve the performance. We tried to insert records to Oracle table in small chunck but not much imprrovement as lots of calls between SQL Server and Oracle. Thanks in advance. Regards, Anu
oraclelinked-serverdata-migration
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@anurag Shooting in the dark: did you try **execute at** instead of openquery? I am afraid that due to the nature of the cross-server-queries beast, the **select DBTime,DBUser,Description,SortOrder from ia_sys_status** is executed once per record in the [SQLSERVERDB].[dbo].[ia_sys_status] table, and therefore, you can probably try any one of the following 2: - add **where 1 = 0** after **from ia_sys_status** - try using execute at:
declare @sql varchar(500);
set @sql = 'insert into ia_sys_status
(DBTime, DBUser, Description, SortOrder)
select
    DBTime,DBUser,Description,SortOrder
    from [SQLSERVERDB].[dbo].[ia_sys_status];';

execute (@sql) at IA_LINKEDSERVER;
go
0 Likes 0 ·
Thanks for your reply. I tried second option first and got the error : OLE DB provider "MSDASQL" for linked server "IA_LINKEDSERVER" returned message "[Oracle][ODBC][Ora]ORA-00903: invalid table name ". Msg 7215, Level 17, State 1, Line 9 Could not execute statement on remote server 'IA_LINKEDSERVER'. What I understand from this error that as the query will run on Oracle side, SQL Server syntan will not be recognized by Oracle. Please let me know if this kind of query worked for anybody? Regards,
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
If your purpose is data migration, then my suggestion is to go for SSIS packages or IMPORT/EXPORT wizards. I am sure that it will outperform linked server
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - I would agree although its expectation rather than experience, having never had Oracle as a linked server
0 Likes 0 ·
anurag avatar image
anurag answered
Thanks for your replay. We had considered all the options including DTS/SSIS before we choose LINKED Server for data migration purpose. Appreciate any help only LINKED SERVER for my case with the same setup I have. I am googling and found lot of tricks like writing SQL Satement,Chunking of data, or running insert on Oracle directly like this. I don't know how to do that. Regards, Anu
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.