trigger question

Question

Post reply

trigger question

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Lynn Pettis (10/23/2008)

The BEGIN and END may not be necessary, but I find it makes it easier to see what code makes up a trigger, stored procedure etc. Especially true if there are several together in a single script.

😎

Heh... Begin will always be at the beginning of the code and End will always be at the end of the code... so, I don't optional BEGINs or ENDs.

I used to have an example that showed they actually made some code run slower... I'll see if I can find the example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Lynn Pettis

SSC Guru

Points: 442447

October 23, 2008 at 9:44 pm

#889013

Jeff,

I'd be real interested in seeing that, and testing it as well. I have never seen that myself, so that has to be a Missouri moment (show me).

Thanks,

Lynn

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Like I said... lemme see if I can find the code I made previously...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Crud... I gotta learn to keep everything. I can't find the code that I wanted. I do remember that it's not the same as the following which seems to indicate just the opposite of what I said...

Here's the test procs... nice and simple...

CREATE PROCEDURE dbo.WithoutBegin AS



DECLARE @Bitbucket INT

 SELECT @Bitbucket = 1

GO

 CREATE PROCEDURE dbo.WithBegin AS

BEGIN

DECLARE @Bitbucket INT

 SELECT @Bitbucket = 1

END

GO

... and here's the test code...

SET NOCOUNT ON



DECLARE @Counter   INT

DECLARE @Limit     INT

DECLARE @StartTime DATETIME

    SET @Limit = 1000000

--------------------------------------------

 SELECT @StartTime = GETDATE(),

        @Counter = 1

  WHILE @Counter <= @Limit

  BEGIN

           EXEC dbo.WithoutBegin

            SET @Counter = @Counter + 1

    END

  PRINT DATEDIFF(ms,@StartTime,GETDATE())

--------------------------------------------

 SELECT @StartTime = GETDATE(),

        @Counter = 1

  WHILE @Counter <= @Limit

  BEGIN

           EXEC dbo.WithoutBegin

            SET @Counter = @Counter + 1

    END

  PRINT DATEDIFF(ms,@StartTime,GETDATE())

Like I said, it indicates just the opposite of what I said... gotta believe the code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Well, first of all, I had to fix the code you posted. Both tests ran the same stored procedure. After fixing the code, yes the stored procedure with the BEGIN END still ran faster than the one without the BEGIN END.

Just remember, this doesn't disprove what you said earlier as there could code out there that goes the other way.

😎

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

On my 2008 box, it varies. First one is faster then the other, but it's a difference of around 20ms over 1000000 executions, So works out at about 20 nanoseconds per execution. Time differences at that level could easily be accounted for by other processes taking CPU time.

Results -

with: 8086, 8110, 8110, 8076, 8063

without: 8110, 8080, 8096, 8096, 8093

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Lynn Pettis (10/24/2008)

Well, first of all, I had to fix the code you posted. Both tests ran the same stored procedure. After fixing the code, yes the stored procedure with the BEGIN END still ran faster than the one without the BEGIN END.

Just remember, this doesn't disprove what you said earlier as there could code out there that goes the other way.

😎

Heh... oh, I think I'll quit while I'm behind 🙂 I did the tests first and then went to make it pretty instead of making it pretty as I went. I copied a section of code and pasted it and forgot to mod the proc name. Gotta stop posting late at night...:P

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions