Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS task taking time but SP called is quick Expand / Collapse
Author
Message
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: Sunday, November 9, 2014 7:53 AM
Points: 81, Visits: 192
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
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: Thursday, November 13, 2014 9:37 AM
Points: 160, Visits: 704
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
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: Sunday, November 9, 2014 7:53 AM
Points: 81, Visits: 192
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
Posted Wednesday, March 13, 2013 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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
Posted Thursday, March 14, 2013 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:18 AM
Points: 5,245, Visits: 12,161
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1430842
Posted Friday, March 15, 2013 1:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 5,438, Visits: 7,606
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
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: Sunday, November 9, 2014 7:53 AM
Points: 81, Visits: 192
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