-
Linked Server to Oracle
I am looking for a way to increase performance when deleting or updating an Oracle table from SQL Server 7.0. The Insert I use works great but the delete (and update) take up to 15 minutes to delete one row from an Oracle table and there are indexes on the proper columns of the Oracle table.
This is the INSERT command using OPENQUERY that seems to work very fast:
SELECT @sqlstring =
'INSERT INTO #old_id SELECT * FROM OPENQUERY(Linked_Server,"'+ 'SELECT I_ID FROM ORDER_TABLE WHERE I_ORDERNO = ' + '''' + @order_num + '''' + '"'
EXEC sp_executesql @sqlstring
However, unless there is a better way...please tell me if there is...I can only get the Delete to work using linked server with this syntax and it takes forever to delete one row in the Oracle table:
SELECT @sqlstring = 'DELETE FROM Linked_Server..Owner.ORDER_TABLE WHERE I_ORDERNO = 'SELECT @sqlstring = @sqlstring + '''' + convert(varchar(20), @order_num) + ''''
EXEC sp_executesql @sqlstring
It would really be nice if Microsoft would make it as easy to delete and update as it is to select and insert using OpenQuery. Any ideas would be appreciated to speed up this performance issue.
Thanks,
Jan
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|