How to reduece the execution time of the sotred procedure.

I wrote one stroed procedure in MS sql 2005 server, when i excute th procedure using query analyzer it s take more time like more then 15 minits , how can i redues time.
   Basicaly that stored prcedure itself i'm crateing excel object and writi a data  into the excel sheet.

kindly give an valuable soluction
mseitAsked:
Who is Participating?
 
Haris DulicConnect With a Mentor Commented:
is procedure writing directly to excel row-by-row?

if that the case the best way to reduce the execution time is to make the procedure write everything to temp table than write it all data from temp table to excel at once... the insertion into excel or any remote storage takes a lot of time so the best way is to do it in  a bulk , all at once....

hope it helps i.e. speeds up .. ;)


0
 
David ToddSenior DBACommented:
Hi,

Can you post the procedure here?

Really difficult to say without being able to see the procedure.

Cheers
  David
0
 
Lee SavidgeCommented:
Hi,

If the above point is correct, to improve further, use a table variable to store the data before dumping out to Excel.

Cheers,

Lee
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
mseitAuthor Commented:
kindly let me know how can i use bulk method to export excel
0
 
Haris DulicCommented:
basically, in a bulk means that you do something like this

when you procedure s running instead of inserting to excel do

insert into #TEMP --which you have to create ofcourse
select something
from somewhere

and in the end you do this.


insert into excel.object
select * from #TEMP


and that would be bulk...
0
 
mseitAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.