SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Parameterized versus stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 05/27/2005 :  05:00:19  Show Profile  Visit henrikop's Homepage  Reply with Quote
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

Slovenia
11751 Posts

Posted - 05/27/2005 :  05:34:05  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 05/27/2005 :  17:20:22  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 05/27/2005 :  19:54:01  Show Profile  Reply with Quote
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 code
2. Additional bit of security
3. Possible performance improvements because of stored execution plan

Now, 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


Edited by - eyechart on 05/27/2005 19:55:30
Go to Top of Page

raclede
Posting Yak Master

Philippines
180 Posts

Posted - 05/29/2005 :  20:05:58  Show Profile  Reply with Quote
Using stored procedure has many benefits:
1. Security (Prevents SQL Injection, see BOL)
2. Clean and readable codes
3. Scalability

Also 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
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 06/06/2005 :  09:03:15  Show Profile  Visit henrikop's Homepage  Reply with Quote
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
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 06/06/2005 :  09:08:43  Show Profile  Visit henrikop's Homepage  Reply with Quote
This link gave me a good view on the topic

http://www.theserverside.net/news/thread.tss?thread_id=31953

And also the replies are superb.... (pro's and con's)



Henri
~~~~
Anyone who can walk to the welfare office can walk to work.
- Al Capone

Edited by - henrikop on 06/06/2005 09:21:46
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 06/06/2005 :  09:32:33  Show Profile  Visit henrikop's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000