Linked Server

Question

Post reply

Linked Server

anil -167628

Newbie

Points: 5

April 22, 2005 at 8:40 am

#61548

I have a problem with linked server slowness.

I am trying to insert data from SQL server to Oracle in a Sql server stored procedure. It is working ok, with out any problem except slowness. Some times it may insert upto 5000 rows.

Following is my code in SQL Server SP. Please let me know if there is any alternative method.

CREATE PROCEDURE [dbo].[proc_res_data]

@pid NUMERIC(8), @ppid VARCHAR(1000) AS

DECLARE

@local_sqlstmt NVARCHAR(4000);

SET @local_sqlstmt = 'INSERT INTO bo..USER1.ORA_TABLE'

SET @local_sqlstmt = @local_sqlstmt + '(Oracle table field list)'

SET @local_sqlstmt = @local_sqlstmt + 'SELECT sql table field list'

SET @local_sqlstmt = @local_sqlstmt + ' FROM SQL_TABLE'

SET @local_sqlstmt = @local_sqlstmt + ' WHERE id = ' + CAST(@pid AS VARCHAR)

SET @local_sqlstmt = @local_sqlstmt + ' AND pid IN (' +@ppid + ')'

EXECUTE sp_executesql @local_sqlstmt;

GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

It's probably a link issue between the servers where one is too busy to respond quickly if it's not a consistent problem.

Nilssond Hall of Fame Points: 3285 More actions · Answer 2

Collation compatibility may be an issue. Make sure you have the compatibility you want by checking the appropriate box in the linked server --> Properties -->Server Options applet tab.

anil -167628 Newbie Points: 5 More actions · Answer 3

Checking collation compatibility helped little. But it still took 32 seconds to transfer 2590 records from SQL Server.

I am not sure, what I am missing here.

Maneli Newbie Points: 1 More actions · Answer 4

If your're loading data from SQLServer into Oracle, you should see if the Oracle table is using Indexes.

You should drop then first and re-create then when the loading finish.

perhaps you should trying using Oracle Temporary table to get data and load it at the end, in the right Oracle Table