473,848 Members | 1,559 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECTs vs Stored Procedures

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
Nov 21 '05
45 3441
How is this different than what applications have to do otherwise? Is
drag-and-drop or hand-coding the application any different because you have
moved the update logic to the middle tier?

--
_______________ _______________ ______
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.
_______________ _______________ ____

"Frans Bouma [C# MVP]" <pe************ ******@xs4all.n l> wrote in message
news:xn******** *******@news.mi crosoft.com...
DHass [MCP] wrote:
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


"William (Bill) Vaughn" wrote:
> 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. _______________ _______________ ____
>
> "John" <Jo**@nospam.in fovis.co.uk> wrote in message
> news:ON******** ******@TK2MSFTN GP12.phx.gbl...
> > 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?

Nov 21 '05 #21
Well i totally agree with this , however i would like to add that if you
are talking about parameters that change , we are talking about the
parameter signature of the Proc and not the parameter values
as only a signature change requires a forced recompile while testing .

at least that is how i learned it at my 70-229 MCSE course

regards

Michel Posseth [MCP]

"William (Bill) Vaughn" <bi************ **@nwlink.com> wrote in message
news:O$******** *****@TK2MSFTNG P12.phx.gbl...
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.
_______________ _______________ ____

"m.posseth" <mi*****@nohaus ystems.nl> wrote in message
news:Om******** ******@TK2MSFTN GP14.phx.gbl...
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]" <pe************ ******@xs4all.n l> wrote in message
news:xn******** *******@news.mi crosoft.com...
sh**********@ya hoo.com wrote:
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

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


Nov 21 '05 #22
well even then the statement still stands ,, as how dynamic is dynamic ???
to be non beneficial for the execution plan ? wich will work on anny SQL
statement wether it is stored in a Proc or not
that is exactly why Frans Bouma is right in his above satements

regards

Michel Posseth

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:e1******** ******@TK2MSFTN GP14.phx.gbl...
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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Nov 21 '05 #23
Yup, changing parameter VALUES does not affect the cached plan--it's used
regardless of how well it works given the parameter values provided. If you
change the signature, you're certainly going to get a new SP and thus a new
plan when it first executes.

--
_______________ _______________ ______
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.
_______________ _______________ ____

"m.posseth" <mi*****@nohaus ystems.nl> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..
Well i totally agree with this , however i would like to add that if you
are talking about parameters that change , we are talking about the
parameter signature of the Proc and not the parameter values
as only a signature change requires a forced recompile while testing .

at least that is how i learned it at my 70-229 MCSE course

regards

Michel Posseth [MCP]

"William (Bill) Vaughn" <bi************ **@nwlink.com> wrote in message
news:O$******** *****@TK2MSFTNG P12.phx.gbl...
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.
_______________ _______________ ____

"m.posseth" <mi*****@nohaus ystems.nl> wrote in message
news:Om******** ******@TK2MSFTN GP14.phx.gbl...
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]" <pe************ ******@xs4all.n l> wrote in message
news:xn******** *******@news.mi crosoft.com...
sh**********@ya hoo.com wrote:

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

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



Nov 21 '05 #24
William (Bill) Vaughn wrote:
How is this different than what applications have to do otherwise? Is
drag-and-drop or hand-coding the application any different because
you have moved the update logic to the middle tier?


Well, with dynamic SQL generated at runtime, you don't have to do
anything in that scenario in most cases, while in the case of a proc,
you have to change more than 1 tier. That's not all, often there are
two people or even two teams involved in such a change, which can be
cumbersome, as it's over 2 or more tiers.

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#)
------------------------------------------------------------------------
Nov 21 '05 #25
On Tue, 16 Aug 2005 02:45:48 +0100, "John" <Jo**@nospam.in fovis.co.uk> wrote:

¤ 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?
¤

I would agree with most of the other sentiments toward a preference with respect to stored
procedures.

The only issues I have with SPs is that development can be cumbersome if your data store is not
supported in an integrated development environment. You can't see the SQL code, or other database
objects, so you typically have to open up a separate tool to work with the SPs.

In addition, some designs may need to implement dynamic SQL, which may not translate well to an SP.

Otherwise, the benefits of using SPs far outweigh those for naked SQL.
Paul
~~~~
Microsoft MVP (Visual Basic)
Nov 21 '05 #26
Hi frans. Always a pleasure to see your posts on this subject, even if we
disagree on others. ;)

So, you're saying, putting "dbo." in front of your table names, actually
improves performance?

Fregas

"Frans Bouma [C# MVP]" wrote:
John wrote:
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

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

Nov 21 '05 #27
Ok, guys, we've heard a lot of pros cons about stored procedures versus
dynamic (hopefully parameterized) sql.

How do you handle optional parameters? For example, I want to query for
orders by customerid, a date range and/or productID. Any or all of these
parameters could be included:

SELECT O.*
FROM Orders
WHERE OrderID = @OrderID
AND ProductID = ProductID
AND CustomerID = @CustomerID
AND DateOrder between @startDate and @enddate

how do i make the parameters optional? I've tried using IsNull() and
Coalesce() but these have slowed performance quite a bit for me even in
stored procs. With parameterized dynamic sql, its not an issue, because I
can change the query to not include one of the pieces of the WHERE clause.
Would I just have to make a bunch of different stored procs for every
combination of the parameters? Bleh...

This is very common in applications that require search functionality, BTW.

I'm not issuing this as a challenge or to be a prick...I really want to know.

Fregas

"John" wrote:
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

Nov 21 '05 #28
Ah well, we write the SP to set default values for the parameters (for
example "NULL"). The SP logic then incorporates that fact into its WHERE
clause logic.

WHERE (x = @MyXParm OR @myXParm IS NULL)
--
_______________ _______________ ______
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.
_______________ _______________ ____

"Fregas" <Fr****@discuss ions.microsoft. com> wrote in message
news:2C******** *************** ***********@mic rosoft.com...
Ok, guys, we've heard a lot of pros cons about stored procedures versus
dynamic (hopefully parameterized) sql.

How do you handle optional parameters? For example, I want to query for
orders by customerid, a date range and/or productID. Any or all of these
parameters could be included:

SELECT O.*
FROM Orders
WHERE OrderID = @OrderID
AND ProductID = ProductID
AND CustomerID = @CustomerID
AND DateOrder between @startDate and @enddate

how do i make the parameters optional? I've tried using IsNull() and
Coalesce() but these have slowed performance quite a bit for me even in
stored procs. With parameterized dynamic sql, its not an issue, because I
can change the query to not include one of the pieces of the WHERE clause.
Would I just have to make a bunch of different stored procs for every
combination of the parameters? Bleh...

This is very common in applications that require search functionality,
BTW.

I'm not issuing this as a challenge or to be a prick...I really want to
know.

Fregas

"John" wrote:
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

Nov 21 '05 #29
Fregas wrote:
Hi frans. Always a pleasure to see your posts on this subject, even
if we disagree on others. ;)
:P
So, you're saying, putting "dbo." in front of your table names,
actually improves performance?
Yes, as it will make the sql optimizer more able to find back a plan
and re-use it.

Frans

Fregas

"Frans Bouma [C# MVP]" wrote:
John wrote:
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.

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
10767
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!!
2
2831
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the code in stored procedures (im advocating encryption of them). When deploying an application however stored procedure seem to add another level of complexity to installation. In future we also plan to have an basic ASP app with some of the...
5
3490
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored Procedures. I thought stored pricedures were an Oracle/MS SQL Server thing and don't know how they work with Access Jet. I've looked at some of the help on stored procedures in A2003, but really don't understand what's going on. Can someone...
2
3339
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some of them are quite big (insert datas in several
28
72620
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be accessing views from stored procedures?
11
3454
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up the MQT at the time it is bound on the creation of the static SQL. This raises the question on how you stop it or start it using a MQT as there is no option on the bind. What happens when it is rebound? What happens if the plan is made invalid...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10661
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4542
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4134
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3172
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.