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

Stored Procedure runs slower than SQL Query Why?? Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 9:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:28 AM
Points: 92, Visits: 681
Hello -

I created a Store Procedure to have Table A sync with Table B by doing an insert of the data that it does not have.

The issue I'm running into is when I run the Stored Proc it's taking longer then just running the Insert itself. I want to put this into a Job to run at night but also to have it in a Transaction in case I need to rollback as well on an error. Can someone take a look to see if I have to much or not enough in my script?

USE [Dev_SageReporting]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[hgsp_backloglaborb_sync]

AS
DECLARE @intErrorCode INT

BEGIN TRAN

INSERT INTO BackLogLaborB
SELECT *
FROM BackLogLaborCurrent AS A
WHERE NOT EXISTS ( SELECT *
FROM BackLogLaborB AS B
WHERE A.PIT = B.PIT )

SET NOCOUNT ON;

SELECT @intErrorCode = @@ERROR
IF ( @intErrorCode <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Regards,
David
Post #1430462
Posted Wednesday, March 13, 2013 9:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:13 AM
Points: 2,857, Visits: 5,129
david.ostrander (3/13/2013)
Hello -

I created a Store Procedure to have Table A sync with Table B by doing an insert of the data that it does not have.

The issue I'm running into is when I run the Stored Proc it's taking longer then just running the Insert itself. I want to put this into a Job to run at night but also to have it in a Transaction in case I need to rollback as well on an error. Can someone take a look to see if I have to much or not enough in my script?

USE [Dev_SageReporting]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[hgsp_backloglaborb_sync]

AS
DECLARE @intErrorCode INT

BEGIN TRAN

INSERT INTO BackLogLaborB
SELECT *
FROM BackLogLaborCurrent AS A
WHERE NOT EXISTS ( SELECT *
FROM BackLogLaborB AS B
WHERE A.PIT = B.PIT )

SET NOCOUNT ON;

SELECT @intErrorCode = @@ERROR
IF ( @intErrorCode <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Regards,
David


For the start:
1. If you have single INSERT statement, you don't need to wrap it in TRANSACTION as it's atomic enough by itself.
2. The error you get is not from result of INSERT, but from SET NOCOUNT ON (which is unlikely to fail );
3. I would suggest to learn using T-SQL TRY ... CATCH for error handling.
4. SET NOCOUNT ON; should be the first line in your stored procedure




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1430468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse