August 12, 2005 at 11:33 am
There IS an advantage to put a single select statement in stored procedures!!
Reasons:
1. if that statement is has parameters The previously compiled plan can be reused on the contrary ad-hoc is compiled every time unless you use sp_execsql or a parameterized ODBC call!!
2. The amount of information going through the pipe is amaller
3. SP use RPC calls (FASTEST possible) instead of batch
The real advantage comes when you call those queries REPEATEDLY if it is a one shot deal probably it won't make much difference!!
* Noel
August 12, 2005 at 1:15 pm
One thing worthy of note:
It is almost never always right to use the exact same method for every situation.
If your query works the way it is, and it gets executed once a year, it would be kinda silly to rewrite everything. Although there MIGHT be a situation in which the preceding statement is untrue.
It would probably be worth it, if for no other reason than to know how to do it.
hth jg
August 15, 2005 at 5:38 am
1. Microsoft says op page msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_4azp.asp: "While the goal is for SQL Server 2000 to always recognize that the statements generate essentially the same plan and reuse the plans, SQL Server sometimes does not detect this in complex SQL statements.". Brings me back to my point: educate me, what is sometimes, what is complex? (By the way, I said "simple"). And futhermore, Java's PreparedStatement and .Net's equivalent are often used. So most calls are parameterized?
2. and 3. Yes, you are right, but I don't think these two reasons are big advantages. Maybe sometimes , when the performance is really critical.
The big advantage is avoiding roundtrips. When you even put statements like SELECT A,B,C FROM T1 WHERE D=getdate() in a SP, you could get into trouble. SP's are not firmly connected with your development kit, but are part of the database (in SQL Server 2005 it is getting better). You are making your DBA responsible not only for the database but also for a part of your application. This can be a problem (version control, etc) in big projects.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply