473,890 Members | 1,373 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 3454
Cor Ligthert [MVP] wrote:
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.


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

--
------------------------------------------------------------------------
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 #11
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?

Thanks

Regards

Nov 21 '05 #12
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.


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

Thanks

Regards


Nov 21 '05 #13
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.

"DHass [MCP]" <DH******@discu ssions.microsof t.com> wrote in message
news:55******** *************** ***********@mic rosoft.com...
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.


"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?
>
> Thanks
>
> Regards
>


Nov 21 '05 #14

"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
Nov 21 '05 #15
As we say here in the States, close enough for government work.

"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

"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

Nov 21 '05 #16
> 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 #17
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 #18
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 #19
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 #20

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

Similar topics

11
10770
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
2834
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
3493
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
3341
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
72645
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
3458
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
10830
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...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9641
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...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5855
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...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
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
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3283
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.