Help tuning SQL Statement

Question

Help tuning SQL Statement

Thomas-253584

SSChasing Mays

Points: 639
More actions
November 27, 2006 at 6:06 pm

#121676

Hallo all,
i take this Statement to update a Linked Oracle Server from an MSSQL 2000 Server.
Please show for the Statement for tuning it. It is sooo slow.
About to set EMP_SK Rows to NULL its 6 Minutes.
And for this Statement 2500 Rows Update over 12 Minutes.
Please Help
Thanks
Thomas
GO
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
DECLARE @Emp varchar(20), @Sched varchar(20),@Sched_set varchar(20),@Cmd varchar(2500)
DECLARE contact_cursor CURSOR FOR
SELECT emp_sk,sched_id,schet_set FROM dbo.callnet
WHERE emp_sk is not null
ORDER BY sched_id
OPEN contact_cursor
FETCH NEXT FROM contact_cursor
INTO @Emp, @Sched,@Sched_set
WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER OFF
SET XACT_ABORT ON
select @cmd = "Update openquery(DB,'select EMP_SK,SCH_SET_SK,SEQ_NUM from DBOWNER.TRL_SCH
where SCH_SET_SK = " + @Sched_set + "
and SEQ_NUM = " + @Sched +" ' )
set EMP_SK = " + @Emp + ""
exec (@cmd)
FETCH NEXT FROM contact_cursor
INTO @Emp, @Sched,@Sched_set
END
CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

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

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

Bart Read SSCommitted Points: 1628 More actions · Answer 1

If you're updating an Oracle database the problem might lie there: I'd say as a first step you should check out what indexes (if any) are defined on the tables you're updating in your Oracle database, see if you can make any improvements and then come back to this. Just a suggestion though and I could be well wide of the mark.

Cheers,

Bart

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

I dont think you neeed a cursor here and thats what will be slowing it down most.

Get the data you want to update out of oracle into a table variable in sql server.

Then run the update joining on the values you are using in your where clause to your sql server data. Then run another update joining the updated table variable to your oracle data.

This way all operations will be set based and hence much faster.

www.sql-library.com[/url]

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Create a linked server and perform the operation as 1 set-based UPDATE. Assuming you've named the link "DB":

UPDATE orcl

SET

EMP_SK = sq.emp_sk

-- Use linked server. Careful - Oracle names are case-sensitive

FROM [DB]..DB_OWNER.TRL_SCH As orcl

INNER JOIN dbo.callnet As sq

ON (orcl.SCH_SET_SK = sq.sched_set AND

orcl.SEQ_NUM = sq.sched_id)

WHERE sq.emp_sk IS NOT NULL

Thomas-253584 SSChasing Mays Points: 639 More actions · Answer 4

Hallo all and thanks for resumes. In first sorry for my English 🙁 but its not so good.

@bart

There are indexes on the columns:

SCH_SET_SK is normal Index Unique no Join Index

SEQ_NUM is normal Index Unique no Join Index

EMP_SK is normal Index NonUnique no Join Index

@Jules

Sorry i dont understand what you mine.

I have all datas in the SQL table about over 2000 rows.

Sorry its the first time to work with an linked server and Oracle.

@PW

Thanks for the statement but if i do this the SQL give an error. MSDASQL can't do this no information from Oracle.

The Oracle allways do very very slow selects on work with

Select EMP_SK FROM [DB]..DB_OWNER.TRL_SCH.

Regards

Thomas

Thomas-253584 SSChasing Mays Points: 639 More actions · Answer 5

Hallo PW,

i do this Statement from you and my server is hanging up.

For one update i cancel the job after 1 1/2 hours.

Thanks

Thomas