Inserting to table from Stored procedue output takes long

• Swapna Akula

  SSC Enthusiast

  Points: 133

  More actions

  November 1, 2009 at 5:46 am

  #213072

  We are facing an odd performance issue/behavior in our production environment.

  Following is the scenario and description.

  Scenario 1

  Code

  EXEX PROC1

  .

  .

  INSERT INTO TABLE1

  EXEC PROC2 @Param

  SELECT FROM TABLE2,

  TABLE3, TABLE4, …

  JOINS…

  WHERE Param = @Param

  Output : It is takes very long time and gets hung sometimes and not always. SP_WHO2 shows COMMAND as SELECT and it is not moving.

  Since there are multiple tables, it takes lot of CPU to read data and causing issue.

  Any ideas on why this is happening?

  Scenario 2

  Code:

  EXEC PROC1

  .

  .

  EXEC PROC2 @Param

  INSERT INTO TABLE1

  SELECT FROM TABLE2,

  TABLE3, TABLE4, …

  JOINS…

  WHERE Param = @Param

  Output : It takes few minutes and completes the task.

  There is no difference in the amount of data being processed in both the scenarios.

  Is this an issue with SQL 2008 or anything specific to environment configuration?

  Any pointers on why this could be happening would be greatly helpful.

  Regards,

  Swapna

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  November 1, 2009 at 10:40 am

  #1073609

  Hi Swapna

  Could you please share your real SELECT statement and probably more information (or the code) of your procedure?

  Without some more information it's quiet hard to help.

  Thanks

  Flo