General optimization question 1

[TheWkndr]

As a relative newb who doesn't have any formal training in SQL programming (or programming in general), I have a question that may be more of an opinion piece for you guys - if I have a section of code, say an 'if...else' which runs maybe 30 lines for either scenario - is it better to build this as a stored proc and call it each time I need to run it or better to type the code in each time? I have maybe ten instances in a pretty massive trigger where I could do this.

I know it would also be a lot easier to change one place, but this was all learned on the fly and I don;t want to rewrite everything if it isn't going to gain us anything outside of being easier to look at.

Thanks.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TheWkndr
Braccae tuae aperiuntur...

 

[philhege]

Use a stored proc. SQL caches query plans for SPs and there's a definite performance advantage.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[ptheriault]

I second Phil's suggestion of a stored procedure. A procedure is compiled within the database. So the database engine does not need to do as much work.


- Paul

- If at first you don't succeed, find out if the loser gets anything.