Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KlausJensen
Starting Member
14 Posts |
 Posted - 2004-03-19 : 07:12:46
|
| Hi!Is it true, that the performance on parameterized queries is excatly the same as stored procedures? If true, is this new for SQL Server 2000?I have always believed stored procedures were superior to adhoc- and parameterized queries performance-wise, but a developer told me yeasterday, parameterized queries can also reuse plans and therefore are just as effective as stored procedures.Answers and references would be greatly appreciated! :)- Klaus---Klaus JensenDeveloper |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-19 : 08:20:56
|
Ok, I'll start  Maybe this http://www.sommarskog.se/dynamic_sql.html is a starting point. Erland has also an article on dynamic searches on his site where he presents some performance numbers, IIRC.While the caching of ad-hoc queries improved in SQL Server 2000, in most cases s_procs are still faster. however, the use of s_procs serves some other important aspects like separating logic from front-end to back-end, controlled access to the data....It's not all about performance. --Frankhttp://www.insidesql.de |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-19 : 08:22:15
|
| They can reuse plans, but they are less likely to than a stored procedure. And while compile time is usually pretty small, if you have a frequently used procedure, saving time on compilation can add up.On the other hand, sometimes old(er), less optimal plans can get stuck in the procedure cache and require it to be flushed (DBCC FREEPROCCACHE) in order to generate a better plan. This doesn't happen often if the table(s) are indexed properly and the statistics are kept up-to-date. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-20 : 12:15:40
|
| http://www.msdn.microsoft.com/netframework/default.aspx?pull=/library/en-us/dndotnet/html/storedprocsnetdev2.asp |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2004-03-22 : 04:57:50
|
Thanks to all of you for your input! :)Encapsulating all data-logic in out DataAccessLayer (DAL) would be great and would make deployment/testing easier... Using stored procedures adds another "layer" to our DAL. It makes it harder to do versioning and make sure two developers do not tamper with the same stored procedure at the same time. On the other hand, we have always used stored procedures, they're like part of the family! :DAnd there seems to be performance-gains, from what I read in the articles you mention - and what robvolk concluded... But the articles discuss adhoc vs. sp's - not parameterized querises vs. sp's... I am still not sure of what to do, this requires I put on my thinking-hat.  ---Klaus JensenDeveloper |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-22 : 08:20:53
|
quote:
Encapsulating all data-logic in out DataAccessLayer (DAL) would be great and would make deployment/testing easier... Using stored procedures adds another "layer" to our DAL.
No, actually they are PART of your data access layer. The difference between using ad-hoc SQL/parameterized queries and stored procedures is no different than using a SELECT * FROM query vs. a table name when opening a recordset/dataset.quote:
It makes it harder to do versioning and make sure two developers do not tamper with the same stored procedure at the same time.
Not really. It requires the same amount of discipline any source control requires. At work we check stored procedure scripts into and out of SourceSafe just like any other files. If someone alters the procedure in the database, we blow it away and use the SourceSafe copy. If they lose changes, oh well, they should've checked them in. This is no more or less difficult to control than would compiling a separate executeable on a local machine and distributing that, instead of an executeable built off of the SourceSafe version. |
|
|
|
KlausJensen
Starting Member
14 Posts |
Posted - 2004-03-22 : 09:31:19
|
| RobVolk>>Points taken... Any links to best practices for using VSS for Stored Procedures?---Klaus JensenDeveloper |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-22 : 09:38:09
|
| http://www.sqlservercentral.com/products/bwunder/archiveutility/--Frankhttp://www.insidesql.de |
|
|
|
|
|
|
|
|
Parameterized queries vs. stored procedures