none
oledb source Stored Procedure is taking long time for execution

    Question

  • Hi,

           I am having a stored procedure which will execute in 5 mins in SSMS and return about 1 million records, but the same Stored Procedure is taking 15-20 mins in OLEDB source to return 1 million.

    Why is it happen?

    How can we fix it?

    Sunday, December 30, 2012 5:56 PM

All replies

  • HI Moin57,

    You first reduce the time in SSMS for using INDEXES on columns.

    Thanks,

    Satish


    satti

    Sunday, December 30, 2012 6:07 PM
  • how did you checked the performance?

    BIDS or SSDT will reduce speed and performance because of the GUI.

    so just deploy the package and then run it from SSMS to see the difference,


    http://www.rad.pasfu.com

    Sunday, December 30, 2012 9:03 PM
    Moderator
  • I deployed the package in SSMS and execute from there

    Still same problem exists.

    I found the Stored Procedure is executing three times in OLEDB source, before getting the final result

    Why is it happening?

    Monday, December 31, 2012 6:58 AM
  • Do you have only single Data flow? or multiple data flows?

    did you tried to execute the sp in execute sql task, just to see how long it will takes there?


    http://www.rad.pasfu.com

    Monday, December 31, 2012 7:30 AM
    Moderator
  • Single Data Flow,

    Execute SQL Task is taking 5 mins only.

    but OLEDB source is taking 15-20 mins for executing Same Stored Procedure. Please explain me why it is happening ?

    I need to use the result set of Stored Procedure in Data flow, so only way of execution is "OLEDB Source".

    Monday, December 31, 2012 8:34 AM
  • Have you specified Fast Load option for OLEDB Destination?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, December 31, 2012 8:36 AM
  • Yes i have specified fast load option for OLEDB destination

    My Stored Procedure is returing 1 million record with 185 columns

    Monday, December 31, 2012 8:42 AM
  • Hi,

     Please help me on this issue.

                    This issue of Stored Procedure executing multiple times in OLEDB source is not only in this package but in other packages.But here i am having with time.

    I tried to change the isolation level also, but i didn't saw a major improvement.

    I am using SSIS 2008 R2 version

    Monday, December 31, 2012 7:20 PM
  • The likely cause of the performance difference is different execution plans.  This may be due to different connection settings.  I suggest you read Erland's article on the subject for more information:  http://www.sommarskog.se/query-plan-mysteries.html


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, December 31, 2012 7:32 PM
  • Hi,

        Please the image the below, whenever my package is executed , the same stored procedure is getting executed 3 times as shown in SQL Profiler

    after setting Delay Validation = true , if not then it is executing 4 times.

    Why this multiple executions are happening for Same SP when we are executing the Package only once.

    The Same result appears if we tried to stored procedure from Job.It is executing three times only.

    Tuesday, January 01, 2013 3:53 AM
  • Hi,

        Please the image the below, whenever my package is executed , the same stored procedure is getting executed 3 times as shown in SQL Profiler

    after setting Delay Validation = true , if not then it is executing 4 times.

    Why this multiple executions are happening for Same SP when we are executing the Package only once.

    The Same result appears if we tried to stored procedure from Job.It is executing three times only.

    The stored procedure is not actually executed multiple times.  The first 2 are part of the SET FMTONLY ON calls to prepare the SQL statement (as you can see by the SQL:BatchCompleted that immediately follows).  The last is the actual execute of the stored procedure (as you can see by the exec sp_execute without the SET FMTONLY ON).

    Do you actually have SET FMTONLY OFF in your stored procedure code? The SP:StmtCompleted event in the trace seems to indicate that.

    Have you compared the SSMS vs SSIS execution plans?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, January 02, 2013 2:23 AM
  • Hi,

      "The first 2 are part of the SET FMTONLY ON calls to prepare the SQL statement (as you can see by the SQL:BatchCompleted that immediately follows).  The last is the actual execute of the stored procedure (as you can see by the exec sp_execute without the SET FMTONLY ON)."

    Each these first 2 calls to prepare the SQL Statement will take the same time as to execute the Stored Procedure(i.e If a Stored Procedure is taking 1 min for execution, then calls will take 1*2=2 mins to prepare SQL).That is what is happening here.

    Yes i am having SET FMTONLY off in my stored procedure.Do i need to turn it in on?

    Executing Plans are same.When i execute the SQL,with Execute SQL Task its same time as compared to SSMS.But i am facing the issue with OLEDB source.

     

     

    Wednesday, January 02, 2013 3:42 AM
  •  

    Each these first 2 calls to prepare the SQL Statement will take the same time as to execute the Stored Procedure(i.e If a Stored Procedure is taking 1 min for execution, then calls will take 1*2=2 mins to prepare SQL).That is what is happening here. 

    Why does the Duration column in Profiler show 0ms?  The Duration reflects the time spent executing in SQL Server so I would expect a duration of 60000ms in the trace if the time is on the SQL Server side.  If you run the proc in a Data Flow as an OLE DB Source without a destination, does it still take a long time to run?

     

    Yes i am having SET FMTONLY off in my stored procedure.Do i need to turn it in on? 

     

    I suggest you remove it entirely since it serves no purpose. I don't think it's related to the issue, though.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, January 02, 2013 1:38 PM
  • I have temp tables in Stored Procedure and when i am removing "set fmtonly off" then i am message

    "The component reported the following warnings:

    Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command."

    Please guide me what need to be


    Friday, January 04, 2013 3:43 AM
  • Hi,

          In my Analysis i found that if the stored procedure is returning the data from temp tables,because we need to SET FMTONLY OFF off inside the Stored Procedure, then that Stored Procedure will be executed multiple time.

          Is there any way to stop this multiple executions for temp tables in Stored Procedure?

        

    Saturday, January 05, 2013 10:39 AM