Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Better Performance 1
- Thread starter ofsouto
- Start date
Its not just about performance, its about seperating your application from the database and making it less impacted by database changes.
James
James Culshaw
jculshaw@active-data-solutions.co.uk
James
James Culshawjculshaw@active-data-solutions.co.uk
SashiRaveendran
Programmer
Also the stored proceudres are compiled by database so they run very quickly (after first call) compared to striaight commandtext queries
-
1
- #6
mikewoodhouse
Programmer
Also the stored proceudres are compiled by database so they run very quickly (after first call) compared to striaight commandtext queries
Well, usually. If the S/P is going to take a long time to run (> 5 sec, say) then having a precompiled query plan is less important than getting it done right every time, so dynamic SQL doesn't lose much to a stored proc in this instance. It's also less important when (e.g.) an index is not evenly distributed: some queries will resolve well and some not using the same query plan.
Bear in mind that cost-based optimizers don't spend very much time thinking about query plans: small fractions of a second - if they go over their time budget they'll stop and use the best plan found so far.
I'm not saying you're wrong - just that it's not a hard-and-fast rule and developers should be aware of the kind of situations where the approach is suboptimal.
Mike
Well, usually. If the S/P is going to take a long time to run (> 5 sec, say) then having a precompiled query plan is less important than getting it done right every time, so dynamic SQL doesn't lose much to a stored proc in this instance. It's also less important when (e.g.) an index is not evenly distributed: some queries will resolve well and some not using the same query plan.
Bear in mind that cost-based optimizers don't spend very much time thinking about query plans: small fractions of a second - if they go over their time budget they'll stop and use the best plan found so far.
I'm not saying you're wrong - just that it's not a hard-and-fast rule and developers should be aware of the kind of situations where the approach is suboptimal.
Mike
I remember reading this in a book a while back:
Business rules that involve database operations should
be implemented as stored procedures.
If the operations involve many calculations or complex
flow-control, they should be classes.
Complex (not simple) validations should take place on
the middle tier.
Business rules that involve database operations should
be implemented as stored procedures.
If the operations involve many calculations or complex
flow-control, they should be classes.
Complex (not simple) validations should take place on
the middle tier.
- Status
Similar threads
- Locked
- Question
