| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2005-05-27 : 05:00:19
|
| I attended a meeting yesterday. Someone demonstrated a code generation tool and took as example Norhtwind.It made entities of tables and for example an order could be handled as an object. If you did something in de the front-end, de sql code was generated dynamicly and executed on SQL server with parameterized querys. (@SomeValue instead of sql += " WHERE " & SomeValue.ToString & " ")He said that there's no (noticable) difference between the speed of a parameterized (dynamic) query and a stored procedure (with this query in it).Is this true?I thought that stored procedures are 'pre-compiled' so that no executing plan has to be made, because it's already there. He said that once a parameterized query or stored procedure is run the plan stays in the cache, and the have the same speed. But I thought a stored procedure doesn't need the cache in the first place.Henri~~~~Anyone who can walk to the welfare office can walk to work. - Al Capone |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-05-27 : 05:34:05
|
here we go again...  the performance of parametrized queries vs sprocs is not notable.the diff is in other things like security and permissions...there were a "few" debates on this. just google "parametrized queries vs stored procedures"Go with the flow & have fun! Else fight the flow  |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-27 : 17:20:22
|
| Why do people forget that stored procedures are also good for doing queries or processing that you just wouldn't want to stuff into an ad-hoc (parameterized or otherwise) query. I have a stored procedure I'm modifying here that builds a temporary table, populates it, runs about 6 modifying UPDATES to it and then pulls the resultset. You wouldn't want to put that in a paramterized query, even though it takes only one parameter. And that's all done in nice set-based code. No while loops or if statements in there, but sometimes those are important too. You just can't do a direct comparison between stored procedures and parameterize queries except in the simplest of systems.---------------------------EmeraldCityDomains.com |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-27 : 19:54:01
|
| no need to jump on this guy, he's asking a legitimate question. Anyway, I like sprocs for a number of reasons:1. Easy to update code2. Additional bit of security3. Possible performance improvements because of stored execution planNow, benefit 3 sometimes blows up on you because SQL has stored a bad plan - or better put, a plan that is no longer optimal.I think the main reason to go with sprocs is that they are more modular and easier to update because they reside in the database. Now if the TSQL error handling were a little better sprocs would be perfect. I guess we just need to wait until SQL2K5 for that.-ec |
|
|
|
raclede
Posting Yak Master
180 Posts |
Posted - 2005-05-29 : 20:05:58
|
Using stored procedure has many benefits:1. Security (Prevents SQL Injection, see BOL)2. Clean and readable codes3. ScalabilityAlso looks more pro when using SPs"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. " raclede |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2005-06-06 : 09:03:15
|
| Somehow I forgot to subscripe to this topic. But because I needed the input I got back happily.I really thought that the stored executing plan was giving a sproc a better performance over parameterized querys because I thought that making an execution plan (of big nasty query's) cost performance.Thanks anyway, I will read some other posts.Henri~~~~Anyone who can walk to the welfare office can walk to work. - Al Capone |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2005-06-06 : 09:32:33
|
| I will stop replying to myself, but this is cool.I read previous mentioned topic and then this reply: http://www.theserverside.net/news/thread.tss?thread_id=31953#158113 It's written by Frans Bouma. He's the one I was listening to in the meeting I named in the beginning of this topic. I asked him the question and he said that he didn't want to go into that discussion.Very funny!! (his reply is worth reading!)Henri~~~~Anyone who can walk to the welfare office can walk to work. - Al Capone |
|
|
|
|
Parameterized versus stored procedures