IF statement not seeming to work...

Question

Post reply

IF statement not seeming to work...

Farrell Keough

SSCoach

Points: 17414
More actions
August 7, 2006 at 2:10 pm

#191782

I have inheritted an SP that I am trying to speed up.
Our data was radically changed, (legacy system) in 2001. I have a portion of the SP that checks if the @fyear < 2002. If that is true, the older tables are used, else the newer tables are used.
When I commented this section of the code out of the procedure, it ran substantailly faster, even though in both cases the fyear was 2002.
Any ideas what may be happening? TIA.
I wasn't born stupid - I had to study.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

can you do a PRINT @fyear just before the IF loop to see what is the value the variable has?

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I've had a similar problem before. It may have to do with procedure caches and compiling.

Try splitting the stored proc into 3. The main stored proc does a check of the year, if <2002 it calls one procedure, if >2002, it calls the other

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Thanks. I was thinking about that approach - of course I'm dealing with a large #TempTable throughout the procedure, but maybe I can pass the information back and fourth...

I wasn't born stupid - I had to study.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If you create the #Temp in the outer, it will be available to the two subprocedures.

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: 1004356 More actions · Answer 5

Don't forget... temp tables are tables, too. You can add indexes...

--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)

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Do it all the time. Thanks! (beautiful thing, that...)

(bit of a bang up today, (our car was hit on the way to work - not so good) so I have not had a chance to test..., will let you know).

I wasn't born stupid - I had to study.

Eric Swanson-355778 Say Hey Kid Points: 666 More actions · Answer 7

Also... consider using RECOMPILE in the stored proc if the server would be better off not trying to re-use an execution plan. You could also use the query hint for specific queries in the sproc. If you simply want to recompile the sproc once, use "sp_recompile".

--Eric Swanson
http://www.ericis.com/

Posts++;

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Yeah... I forgot that you've been around a long time... didn't even read to see who did the post, Farrell.

Speaking of being around for a long time, whatcha doin' gettin' all banged up? Hope you and your's are OK... best of luck, old friend.

--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)