Can cursor avoided here?

Question

Can cursor avoided here?

NewBeeSQL

SSCrazy

Points: 2170
More actions
December 17, 2010 at 10:13 am

#99445

I have a job which is is taking almost 4 Hours everyday, This job just gets the data of almost 40 tables into sql server 2000 database from DB2(via linked server ). few tables has maximum of 8 million records.
Basically, this job is calling a stored proc in sql server.. steps involved in proc:
1. Written a cursor to loop through each table
2. conenct to source server to get the data
3. insert the data in to sql server.
4. writing the information into log tables for auditing.
I am attaching the proc and my requirement is to reduce the job execution time ( to the best of possibles). I cannot give DML or DDL scripts as there 40 tables being populated from DB2 to sql server via this job.
Thanking everyone who looking at this.
-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why do you transfer all the data across the network twice? Just to set @inputcount = @@ROWCOUNT? It would be much more efficient to use a count(*) from OPENQUERY() to get the number of rows...

How many indexes do you have on your target tables? It might also help to drop nonclustered indexes and recreate after the insert.

You might also benefit from following this thread including the links provided regarding the SQL stuff. The thread I mentioned originally deals with an SSIS package. If you have the option to use SSIS I'd recommend you consider using it to benefit from parallel tasks.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

NewBeeSQL SSCrazy Points: 2170 More actions · Answer 2

Thanks Lutz.. I will let you know once it gets changed and the impact..

-----------------------------------------------------------------------
For better assistance in answering your questions[/url]
Perforamance Issues[/url]
Cross Tabs and Pivots[/url]
Cross Apply[/url]
The Numbers or Tally Table- Jeff Moden[/url]