[Webinar] Learn how to a build a cloud-first strategyRegister Now


How to reduece the  execution time of the sotred procedure.

Posted on 2009-02-24
Medium Priority
Last Modified: 2012-06-22
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
Question by:mseit
LVL 35

Expert Comment

by:David Todd
ID: 23719959

Can you post the procedure here?

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

LVL 15

Accepted Solution

Haris Djulic earned 1500 total points
ID: 23720377
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 .. ;)

LVL 25

Expert Comment

by:Lee Savidge
ID: 23720625

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


NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 23730823
kindly let me know how can i use bulk method to export excel
LVL 15

Expert Comment

by:Haris Djulic
ID: 23731091
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...

Author Closing Comment

ID: 31550500

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question