How to reduece the  execution time of the sotred procedure.

Posted on 2009-02-24
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

    Can you post the procedure here?

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

    LVL 15

    Accepted Solution

    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

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



    Author Comment

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

    Expert Comment

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


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now