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 |
|
ghan
Starting Member
11 Posts |
 Posted - 2004-05-29 : 15:43:11
|
| As a developer, we always say "using a stored procedure, instead of a cliet side SQL statement, provides performance benefits". However, it seems it has not been true anymore since SQL Server 7.0.See SQL online "Execution Plan Caching and Reuse" at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409I am quite confused with the following questions:1. it seems since SQL 7.0, a SQL statement in client side uses the existing execution plan as a stored procedure does. That means SP doesn't has much advantage over SQL statement in terms of performance.2. It seems, a stored procedure is not always compled ONLY once. If a stored procedure is not used for a long time, it could be kicked out from procedure cashe.3. In order to use an existing execution plan, it seems that we have to use the fully qualified identifier, such asSELECT * FROM Northwind.dbo.Employeesinstead of SELECT * FROM EmployeesHowever, I rarely see anyone uses these kind of fully qualified references for objects both in SQL statements and SP. For example, in the sample database pubs and NorthWind, they don't use the fully qualified expression. I only see the use of it in master database. I guess I might miss something in the issues above. I would like to get any explanation from SQL guru or anybody. Thanks a lot.SQL Server MCPMCAD.NET |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-29 : 22:06:48
|
| Performance was only one of the benefits of using SPs. The main one is so that the application is not bound to the database structure so making maintenance and optimisation easier.To get plan re-use the query has to be exactly the same - changes in values and case will cause a new plan to be generated.With an SP the text will be checked against the cache - if no hit then the id of the SP will be resolved and that used to obtain the plan.If anything is not used for a long time it can be purged from cache - there's not a lot of use in using up cache space for things that are rarly used (usually). SPs are also recompiled if any objects change - permanent tables are droppped, temp tables not created in the SP, ...Systems that are very concerned about performance do qualify the names but usually it is better not to so that it is not database dependant.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ghan
Starting Member
11 Posts |
Posted - 2004-05-30 : 12:13:20
|
| Thanks for your input, Nigel. I agree with you qualititavely. But, my confusions were caused by specific questions quantitavely.You might see a same post in www.asp.net (http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=589170) where I got more feedback I expected.I am a developer, not quite familiar with SQL server as administrators do. I would like to get more suggestions from SQL guru.==============SQL Server MCPMCAD.NET |
|
|
|
|
|
|
|
|
Asking SQL guru: Execution Plan?