SELECTs vs Stored Procedures

[John]

Hi

When developing vb.bet winform apps bound to sql server datasource, is it
preferable to use SELECTs or stored procedure to read and write data from/to
SQL Server? Why?

Thanks

Regards

 

[Ken Tucker [MVP]]

Hi,

Stored procedures are better. They are faster and harder to use a
sql injection attack against.

sql injection
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000002.asp

Ken

 

[Cor Ligthert [MVP]]

John,

In addition to Ken, you use for both Select.

The differnce is that a stored procedure is on the Server and more
processing is done on the Server, which make your retrieving of data
something (or sometimes a lot) quicker.

I hope this helps,

Cor

 

[shiv_koirala]

Hi john

They have there plans precompiled , so are faster.

Yes definetly prevents SQL Injection to a decent level.

Shivprasad Koirala
C# , VB.NET , SQL SERVER , ASP.NET Interview Questions
http://www.geocities.com/dotnetinterviews/

 

[Marc Scheuner [MVP ADSI]]

Stored procedures are better. They are faster and harder to use a

sql injection attack against.

Seconded - also, with stored procs, you're only sending the name of
the stored proc to call and the parameters across the wire, not the
whole big SQL statement - that can make quite a difference on slower
links!

Marc

 

[Frans Bouma [C# MVP]]

Hi,

Stored procedures are better. They are faster and harder to
use a sql injection attack against.

sql injection
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag
2/html/PAGHT000002.asp

Faster than what? parameterized dyn. queries? Nope. Both are compiled
at runtime equally and execution plans are cached for both.

Sqlinjection attacks are not possible when you use solely parameters
in your dyn. sql. Furthermore, just by using a proc doesn't mean your
dyn. search stored procedure which concatenates SQL internally is not
vulnerable for sql injection.

Frans

Ken

--

 

[Frans Bouma [C# MVP]]

Hi

When developing vb.bet winform apps bound to sql server datasource,
is it preferable to use SELECTs or stored procedure to read and write
data from/to SQL Server? Why?

As long as you use solely parameterized queries, it's not making a
difference.
so do:
SELECT * FROM dbo.Foo where field1 = @param
and not:
SELECT * FROM dbo.foo where field1 = 'value'

Also, specify schema names for tables always, this makes the query's
execution plan get cached better and SQLServer's mechanism to find back
an execution plan works more efficiently.

Frans

--

 

[Frans Bouma [C# MVP]]

Hi john

They have there plans precompiled , so are faster.

Stored procs don't have their plans precompiled. At least not since
Sqlserver 7. Only on DB2 procedures are really precompiled and stored
in a precompiled fashion (not always as well, but most people opt for
that option, although it can hurt runtime optimization).

The reason for this is that only at runtime statistics are known and
only in the situation of a fresh query the optimizer can truly optimize
the plan based on the statistics. See BOL about execution plans.

As he's talking about SELECT's, the gain of a proc is not really an
issue (having processing on the server so you avoid pumping loads of
data to the client first and then back to the server).

FB

--

 

[Cor Ligthert [MVP]]

Frans,

Faster than what?

Please, read the question again.

Than you will see
"to use SELECTs or stored procedure to read and write data from/to SQL
Server"

Therefore this question should in my opinion not only be seen about Select.
I have seen that SP's can make a difference in speed.

Although we agree very much about the in my idea deeper meaning of the rest
of your answers in this thread. I get sometimes the idea that people think
that a SP is a solution for everything, which puts an application direct on
a higher level.

Cor

 

[Justin Hoffman]

Hi

When developing vb.bet winform apps bound to sql server datasource, is it
preferable to use SELECTs or stored procedure to read and write data
from/to SQL Server? Why?

Thanks

Regards

If the data is sensitive, you may not allow direct access to your table. So
if you had an employee table, your users would not be able to execute any
old sql statement, eg:
select firstname, lastname, salary from employee order by salary desc
but you could allow limited access to the employees table by writing a
stored procedure which returned only non-sensitive data for a given
employeeID. You give users permissions to run the stored procedure but not
read permissions for the table.

 

[Frans Bouma [C# MVP]]

Frans,

Please, read the question again.

Than you will see
"to use SELECTs or stored procedure to read and write data from/to
SQL Server"

Therefore this question should in my opinion not only be seen about
Select. I have seen that SP's can make a difference in speed.

and I've seen the other side of it where they were slower (because
they lack flexibility to tailor the query towards the actual need of
the action at runtime, you have to write the procs up front).

I indeed missed 'write' in the question, which makes the question
indeed a bit weird: how to compare a SELECT statement send directly
with an insert proc

Frans

--

 

[William \(Bill\) Vaughn]

A number of valid (and some not so valid) have been mentioned. Let me add
this: When I poll a room full of 1000 SQL Server developers asking "How many
of you don't use Stored Procedures?" I usually get a handful of people to
raise their hands. The vast majority (over 95%) use and depend on stored
procedures. They have found that they provide a single, focused way to
manage queries, protect the database, provide better performance (when they
are written correctly) and build a more secure, more scalable application.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

I would just like to add that Stored Procedures offer you the added benefit
of making changes to your queries without having to recompile your entire
application.

 

[Earl]

I'll have to add a hearty, "hear, hear" to that. I can't tell you how many
times I've saved myself tons of work by simply changing a stored procedure
versus having to make changes in code, recompile the app, and redeploy the
app. Along that same idea, it is sweet to be able to camp out on the server
for a short time testing a change to a stored procedure while your user
continues with their normal workday, totally oblivious to any fixes being
made.

 

[Cor Ligthert [MVP]]

"Earl"
..

I'll have to add a hearty, "hear, hear" to that. I can't tell you how many
times I've saved myself tons of work by simply changing a stored procedure
versus

Yes and and almost everybody can do that while changing the programs can
only done by few people, who have access to those programs..

Or is that not what you want to say?

))))

Cor

 

[Earl]

As we say here in the States, close enough for government work.

 

[m.posseth]

Stored procs don't have their plans precompiled. At least not since

Sqlserver 7. Only on DB2 procedures are really precompiled and stored
in a precompiled fashion (not always as well, but most people opt for
that option, although it can hurt runtime optimization).

in more detail regarding this for those who might be interested :

SQL server 2000 stores only the source for stored procedures and triggers
when a stored procedure or trigger is first executed the source is compiled
into an execution plan . If the stored procedure or trigger is again
executed before the execution plan is aged from memory, the relational
engine detects the existing plan and reuses it, otherwise a new plan is
built

The above process flow is simular to the process that SQL server follows for
all SQL statements

the main performance advantage that stored procedures and triggers have in
SQL 2000 is that their SQL statements are always the same , therefore the
realational engine matches them with any execution plans

Stored procedures had a more pronounced performance advantage over other SQL
statements in earlier versions of SQL server , they did not attempt to reuse
execution plans for batches that were NOT stored procedures or triggers


regards

Michel Posseth [MCP]

 

[Frans Bouma [C# MVP]]

I would just like to add that Stored Procedures offer you the added
benefit of making changes to your queries without having to recompile
your entire application.

Sure, until you have to add a parameter to your insert/update proc
set, because a column was added to a table. Have a nice time updating
the tier(s) above that proc-api!

FB

 

[Miha Markic [MVP C#]]

the main performance advantage that stored procedures and triggers have in
SQL 2000 is that their SQL statements are always the same , therefore the
realational engine matches them with any execution plans

Not exactly, nothing prohibits you to craft dynamic sql within stored
procedure.

 

[William \(Bill\) Vaughn]

Ah, close.
Stored procedure query plans change--each time they're compiled. No, they're not recompiled each time they're executed but when they're forced out of the cache or the DBA or developer forces recompilation for one reason or another. That's a problem. When you test a SP with a specific set of parameters, a plan matching those parameters is created. When the parameters change, a new plan is NOT created to match--not if there is a plan in the cache. Hopefully the old plan works efficiently with the new parameters--but it might not.
SQL Server 2005 changes this a bit--it can recompile parts of the plan without affecting other parts.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Stored procs don't have their plans precompiled. At least not since
Sqlserver 7. Only on DB2 procedures are really precompiled and stored
in a precompiled fashion (not always as well, but most people opt for
that option, although it can hurt runtime optimization).

in more detail regarding this for those who might be interested :

SQL server 2000 stores only the source for stored procedures and triggers
when a stored procedure or trigger is first executed the source is compiled
into an execution plan . If the stored procedure or trigger is again
executed before the execution plan is aged from memory, the relational
engine detects the existing plan and reuses it, otherwise a new plan is
built

The above process flow is simular to the process that SQL server follows for
all SQL statements

the main performance advantage that stored procedures and triggers have in
SQL 2000 is that their SQL statements are always the same , therefore the
realational engine matches them with any execution plans

Stored procedures had a more pronounced performance advantage over other SQL
statements in earlier versions of SQL server , they did not attempt to reuse
execution plans for batches that were NOT stored procedures or triggers


regards

Michel Posseth [MCP]

Stored procs don't have their plans precompiled. At least not since
Sqlserver 7. Only on DB2 procedures are really precompiled and stored
in a precompiled fashion (not always as well, but most people opt for
that option, although it can hurt runtime optimization).

The reason for this is that only at runtime statistics are known and
only in the situation of a fresh query the optimizer can truly optimize
the plan based on the statistics. See BOL about execution plans.

As he's talking about SELECT's, the gain of a proc is not really an
issue (having processing on the server so you avoid pumping loads of
data to the client first and then back to the server).

FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------