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

Insert Exec Performance issue ?? Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 5:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:15 AM
Points: 76, Visits: 208
Hi,

I have a procedure which has a code something like

My SP is dynamic something like

WHILE(@i <= @max)

BEGIN

SELECT @db_name = db_name, @db_server_name = db_server_name from #dbs where id = @i

SET @SQL = 'EXEC '+@db_server_name+'.'+@db_name+'.dbo.SP_NAME '+@param1+','+@param2


INSERT into #temp(...)

EXEC (@SQL)

END

When I execute SP without insert statement (without insert into #temp(..)) SP returns in 2 seconds. But, when data is inserted into temp table it takes 7-8 seconds.

Only 36 records are returned.

I tried using Table Variables but it doesn't help. Even tried Permanent table but still no use.

My question is why is INSERT.. EXEC.. taking too long time than just an EXEC .. And is there any better option than that. I tried googling out to find solution and came across various posts saying INSERT.. EXEC.. has performance impact. Few pointed out to Linked Server being slow. But, I was not able to find any solution.

Thanks for your help. Will be happy to provide any other information requried.
Post #1427906
Posted Thursday, March 7, 2013 6:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:56 PM
Points: 20,806, Visits: 32,739
aakash214 (3/7/2013)
Hi,

I have a procedure which has a code something like

My SP is dynamic something like

WHILE(@i <= @max)

BEGIN

SELECT @db_name = db_name, @db_server_name = db_server_name from #dbs where id = @i

SET @SQL = 'EXEC '+@db_server_name+'.'+@db_name+'.dbo.SP_NAME '+@param1+','+@param2


INSERT into #temp(...)

EXEC (@SQL)

END

When I execute SP without insert statement (without insert into #temp(..)) SP returns in 2 seconds. But, when data is inserted into temp table it takes 7-8 seconds.

Only 36 records are returned.

I tried using Table Variables but it doesn't help. Even tried Permanent table but still no use.

My question is why is INSERT.. EXEC.. taking too long time than just an EXEC .. And is there any better option than that. I tried googling out to find solution and came across various posts saying INSERT.. EXEC.. has performance impact. Few pointed out to Linked Server being slow. But, I was not able to find any solution.

Thanks for your help. Will be happy to provide any other information requried.


Just a guess, but it could be because SQL Server has to run the insert in a distributed transact and it is taking a little extra time for that to be accomplished. I don't have a distributed environment in which I can test this theory.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1427939
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse