Better Performance

[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.

 

[Craig0201]

ofsuoto,

This is true. The reasoning behind this is that SQL Server misses out a stage when executing stored procedures. In-line SQL has to have an execution plan written for it before it executes. With a stored proc, the execution plan is already there.

See

http://msdn.microsoft.com

for further details.

Craig

 

[mikewoodhouse]

Well....mostly true, and I'd recommend going down the SP route, although I would suggest leaving complex logic out of the database.

But bear in mind the situation where an index has a lop-sided distribution and some time it's very selective, others not. The SP will use the stored query plan which will be good for the set of parameters supplied when the query plan was created but bad in the other case. In which case the stored proc would have to be created "with recompile", which loses most of the benefit.

Not usually an issue, but always worth keeping in mind.

Mike

 

[Craig0201]

Mike,

Didn't think the complexities were quite necessary....hence left out!!!

As an aside, I don't suppose you would happen to be Mike Woodhouse, formerly (and possibly presently!) of Diss, Norfolk???

Craig

 

[mikewoodhouse]

I've spent the odd weekend in Norfolk, mostly in the Docking area. Don't recall ever getting to Diss though!

 

[Guest_imported]

"Well....mostly true, and I'd recommend going down the SP route, although I would suggest leaving complex logic out of the database."

Of course if you ever want to change database vendors, the SP code is generally not portable...

 

[mikewoodhouse]

Of course if you ever want to change database vendors, the SP code is generally not portable...

But then again, neither is the SQL

 

[DonQuichote]

I don't know if it is of any help, but parameterqueries might also speed up your code. I don't know how protable they are, though...

Best regards