Better Performance 1

[ofsouto]

I think VB accessing stored-procedures gets better performance than writting all queries on VB code.
Is it true? I need some articles that prove it.
Thanks.

 

[NickBulka]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6x45.asp

nick bulka

 

[culshaja]

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

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[jmagave]

Coding calls to SPs can really improve your performance, as well as using Parameterized Queries, do. But you'll be tied to your vendor´s SQL dialect.

 

[SashiRaveendran]

Also the stored proceudres are compiled by database so they run very quickly (after first call) compared to striaight commandtext queries

 

[mikewoodhouse]

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

 

[SashiRaveendran]

thanks mike, useful info

 

[MikeMCSD]

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.