Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Data Warehousing » Integration Services » SSIS task taking time but SP called is quick
Add to briefcase

SSIS task taking time but SP called is quick Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
namrata.dhanawade-1143388
Posted Wednesday, March 13, 2013 6:58 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:13 AM
Points: 75, Visits: 176
Hi,

We have a huge process that we perform using SSIS, which is mostly just calling SP's using SQL task.

When we execute each SP on SSMS they execute in about 2-3 seconds. However, when the SQL task is executed it takes about 10-15 seconds thus increasing the total process time.

What could be the reason? Are we doing something wrong? There are about 31 Sps that get executed in all and the process time goes to around 5-6 minutes.

Please help!!
Post #1430355
cafescott
Posted Wednesday, March 13, 2013 7:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 PM
Points: 109, Visits: 498
One possible work-around is to perform all of the stored procedure calls from a single umbrella procedure; i.e.,
create procedure Call_them_all
as
exec sProc1;
exec sProc2;
...

This way you don't need to establish a new connection for each stored procedure. Once you've done so with the main one it will be used for the others.
Post #1430360
namrata.dhanawade-1143388
Posted Wednesday, March 13, 2013 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:13 AM
Points: 75, Visits: 176
I agree with the work around. But the question still remains and that is, whether the SQL task is taking so much time only for the connection. Or is it something else?
Post #1430415
Evil Kraig F
Posted Wednesday, March 13, 2013 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194
Most likely they're validating. Turn on Delay Validation.

Also, make sure you're using what's become a defacto standard for SSIS called procs:

SET NOCOUNT ON;
SET FMTONLY OFF;

WHILE 1=0
BEGIN
-- Result set for SSIS to use

SELECT
CONVERT(VARCHAR(30), NULL) AS ColumnA,
...
END

... Real code here.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1430586
Phil Parkin
Posted Thursday, March 14, 2013 3:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665
Evil Kraig F (3/13/2013)
Most likely they're validating. Turn on Delay Validation.

Also, make sure you're using what's become a defacto standard for SSIS called procs:

SET NOCOUNT ON;
SET FMTONLY OFF;

WHILE 1=0
BEGIN
-- Result set for SSIS to use

SELECT
CONVERT(VARCHAR(30), NULL) AS ColumnA,
...
END

... Real code here.


...unless you're using 2012, in which case the result set definition moves to the Execute SQL task.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1430842
Evil Kraig F
Posted Friday, March 15, 2013 1:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194
Phil Parkin (3/14/2013)

...unless you're using 2012, in which case the result set definition moves to the Execute SQL task.


Haven't gotten my hands on 2012 yet, but does that include OLEDB Data Sources using a Proc as a source as well?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA
Post #1431717
namrata.dhanawade-1143388
Posted Saturday, March 16, 2013 1:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:13 AM
Points: 75, Visits: 176
This is 2008 R2.

I have used delay_validation before but FMTONLY is new. Will try and get back.

Thanks you so much for all your help.
Post #1431869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.