Bad Openquery Update performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bad Openquery Update performance

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2009-07-23 : 11:42:27

i am running into a performance issue that has crept up on me.
i have the following:
DECLARE @sSQL NVARCHAR(2000)
SET @sSQL = 'UPDATE ' + CHAR(10)
SET @sSQL = @sSQL + 'OPENQUERY(ORACLELINK, '
SET @sSQL = @sSQL + ' ''SELECT *'
SET @sSQL = @sSQL + ' FROM DETAIL '
SET @sSQL = @sSQL + ' WHERE DETAIL_ID='+ @DI +''')'
SET @sSQL = @sSQL + 'SET DETAIL_VALUE = ' + @PI
EXEC sp_executesql @sSQL
the field DETAIL_ID is an indexed field in ORACLE, i have a cursor of 100 records that go and execute this @sSQL string for each record. Its only able to do 4 updates per minute. How can I make this faster? Whats odd is it use to do about 100 updates in the span of a minute. I'm at an ends as to what to look for in speeding this up. Is it something in the settings of the OracleLink?
Server Options are:
Collation Compatible=False
Data Access=True
RPC = False
RPC Out = False
Use Remote Collation = False
Collation Name =
Connection Timeout =0
Query Timeout =0
I can run this same update, using the DETAIL_ID field in PL/SQL and it comes instantaneously. Which leads me to believe it has something to do with my Linked Server/SQL Server.
Help anyone...

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-23 : 12:03:34

Most likely the use of your cursor is the problem. I would try to look for a different approach instead of a cursor - most of the time they can and should be avoided.

Some days you're the dog, and some days you're the fire hydrant.

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2009-07-29 : 19:14:32

its not the cursor Skorch.
I can take the openquery syntax and run it out of the cursor and it still takes 15-20 seconds to update the record.
Also i'm only cursoring through 100 records, so that shouldn't be an issue.

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2009-08-03 : 17:35:09

nothing huh?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources