473,550 Members | 2,713 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Confused about proc vs. dynamic SQL vs LINQ

I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
- is the more proper tier to put it since is a data function

But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Thanks for any insight,
Ron
Apr 30 '07 #1
7 3015
Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql. In the case of SQL Server, you might get just as fast
performance from dynamic sql (sql that you generate on the client and issue
to the server, as opposed to executing the stored procedure with parameters)
depending on the cache (dynamic queries are cached to a degree, and whether
or not they are reused depends on how often the cache is hit, as well as if
the queries are the same and a number of other factors). With stored
procedures, the execution plan is already compiled, and the server won't
have to recompile it. Depending on the statistics that were in place when
the stored procedure was compiled, and the statistics that are in place when
you execute your dynamic sql, you might get a different execution plan
(assuming you are executing the same sql dynamically that you are executing
in the stored procedure.

Now LINQ (when coupled with SQL, in LINQ to SQL, or with ADO.NET
Entities with LINQ to Entities) will rely completely on dynamic sql. I'd
expect the sql that is generated to be pretty performant, if issued against
SQL Server, and probably not too much of a difference performance wise
(depending on your load) if you are doing single-statement inserts, updates,
and deletes.

LINQ to objects, LINQ to XML, and anything that works on objects in
memory is a different technology, one which doesn't attach to a database at
all.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
- is the more proper tier to put it since is a data function

But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Thanks for any insight,
Ron


Apr 30 '07 #2
Thanks very much for the reply. Good info.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c omwrote in
message news:eO******** ******@TK2MSFTN GP04.phx.gbl...
Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql. In the case of SQL Server, you might get just as fast
performance from dynamic sql (sql that you generate on the client and
issue to the server, as opposed to executing the stored procedure with
parameters) depending on the cache (dynamic queries are cached to a
degree, and whether or not they are reused depends on how often the cache
is hit, as well as if the queries are the same and a number of other
factors). With stored procedures, the execution plan is already compiled,
and the server won't have to recompile it. Depending on the statistics
that were in place when the stored procedure was compiled, and the
statistics that are in place when you execute your dynamic sql, you might
get a different execution plan (assuming you are executing the same sql
dynamically that you are executing in the stored procedure.

Now LINQ (when coupled with SQL, in LINQ to SQL, or with ADO.NET
Entities with LINQ to Entities) will rely completely on dynamic sql. I'd
expect the sql that is generated to be pretty performant, if issued
against SQL Server, and probably not too much of a difference performance
wise (depending on your load) if you are doing single-statement inserts,
updates, and deletes.

LINQ to objects, LINQ to XML, and anything that works on objects in
memory is a different technology, one which doesn't attach to a database
at all.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m
"Ronald S. Cook" <rc***@westinis .comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
>I've always been taught that stored procedures are better than writing
SQL in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
- is the more proper tier to put it since is a data function

But then I've heard that writing SQL in my client .NET code might run
just as fast? Dynamic SQL or something?

And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?

Thanks for any insight,
Ron



Apr 30 '07 #3
Ronald S. Cook <rc***@westinis .comwrote:
I've always been taught that stored procedures are better than writing SQL
in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
It depends what you're doing, of course, but on many databases they'll
go through the same optimisation steps, and if you make several calls
to queries which are the same other than parameters, that optimisation
is cached.
- is the more proper tier to put it since is a data function
It depends what you're trying to do. I've seen plenty of situations
where people create stored procs which *just* call normal CRUD
operations, for no reasons other than dogma. Yes, there's the
theoretical "change the database and the procs implementation, don't
change the calling code" argument - but in my experience the change is
often widespread enough to require changes in the client code anyway.

Note that there's nothing stopping you from having a layer in your
application which is solely about data, but happens to be running in
the web server (or whatever) rather than in the DB.

I can see two primary reasons to use stored procs:
1) More finely grained security - give users access to specific stored
procs, rather than a whole table, etc.
2) When the stored proc needs to do a lot of work with the data but
doesn't actually need much of the data to come out of the other end,
thus vastly reducing network traffic.
But then I've heard that writing SQL in my client .NET code might run just
as fast? Dynamic SQL or something?
It depends on the database server, but I believe that using a cached
parameterised query tends to be as fast as a simple "just CRUD" stored
proc on most of the major database platforms these days.
And then there's LINQ on the horizon. Is it a successor to everything
previously mentioned?
Well, it's a way of dynamically creating SQL and maintaining a degree
of compile-time checking. It's another step in the ORM journey - but
ORM's been around for a while now.

See http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx for
more details "against" stored procs.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Apr 30 '07 #4
Ronald S. Cook wrote:
I've always been taught that stored procedures are better than
writing SQL in client code for a number of reasons:

- runs faster as is compiled and lives on the database server
Modern RDBMSs don't compile stored procs anymore, they evaluate any
query at runtime to execution plans which are optimized at runtime, not
at compile time. So, no, stored procedures aren't faster and no they're
not stored in compiled form.
- is the more proper tier to put it since is a data function
I think opinions differ on that ;)
But then I've heard that writing SQL in my client .NET code might run
just as fast? Dynamic SQL or something?
parameterized queries are as fast as procs and can be faster as you
can tailor dyn. SQL (parameterized! ) to the job you have to do, so if
you have a table with 10 fields, you don't have to write a single
UPDATE proc which accepts values for all fields to update, you can
write an update query which updates just 1 field.
And then there's LINQ on the horizon. Is it a successor to
everything previously mentioned?
Linq is a language construct and the result of linq expressions,
expression trees, can be used to generate dyn. SQL at runtime, like any
other O/R mapper does these days.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
May 1 '07 #5
Nicholas Paldino [.NET/C# MVP] wrote:
Ronald,

Generally speaking, stored procedures are going to be faster than
dynamic sql.
no. Don't feed a myth.
In the case of SQL Server, you might get just as fast
performance from dynamic sql (sql that you generate on the client and
issue to the server, as opposed to executing the stored procedure
with parameters) depending on the cache (dynamic queries are cached
to a degree, and whether or not they are reused depends on how often
the cache is hit, as well as if the queries are the same and a number
of other factors). With stored procedures, the execution plan is
already compiled, and the server won't have to recompile it.
no that's not true. Execution plans are created at runtime for procs
as well as for dyn. parameterized queries. This is done to be able to
optimize queries based on the actual statistics. If one would store
compiled execution plans, a query could slow down over time because
another execution plan would be faster because statistics changed.

See BOL on execution plans and caching, there is no difference between
procs and dyn. sql in this.
Depending on the statistics that were in place when the stored
procedure was compiled, and the statistics that are in place when you
execute your dynamic sql, you might get a different execution plan
(assuming you are executing the same sql dynamically that you are
executing in the stored procedure.
I believe this is only true on DB2 these days, and they too have now
more runtime optimization than in the earlier days where procs were
converted to C, compiled and were only optimizable via hints.

FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
May 1 '07 #6
Jon Skeet [C# MVP] wrote:
See http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx for
more details "against" stored procs.
For a more clearer overview:
http://weblogs.asp.net/fbouma/archiv...-A-new-Stored-
Proc-vs.-Dyn.-Sql-battle_2100_.as px

The 'procs are bad, m'kay?' article was a bit tongue in cheek as well,
so please read
http://www.theserverside.net/news/th...d=31953#158113
which is more serious.

Procs aren't all bad of course, Jon gave a great example when they're
useful:
" 2) When the stored proc needs to do a lot of work with the data but
doesn't actually need much of the data to come out of the other end,
thus vastly reducing network traffic."

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
May 1 '07 #7
On Apr 30, 4:55 pm, "Nicholas Paldino [.NET/C# MVP]"
<m...@spam.guar d.caspershouse. comwrote:
Generally speaking, stored procedures are going to be faster than
dynamic sql. In the case of SQL Server, you might get just as fast
performance from dynamic sql (sql that you generate on the client and issue
to the server, as opposed to executing the stored procedure with parameters)
depending on the cache (dynamic queries are cached to a degree, and whether
or not they are reused depends on how often the cache is hit, as well as if
the queries are the same and a number of other factors). With stored
procedures, the execution plan is already compiled, and the server won't
have to recompile it. Depending on the statistics that were in place when
the stored procedure was compiled, and the statistics that are in place when
you execute your dynamic sql, you might get a different execution plan
(assuming you are executing the same sql dynamically that you are executing
in the stored procedure.
As of Sql Server 7, that's not true. Sql Server doesn't 'compile'
stored procedures at all. It just checks syntax. Either method you
use will cause sql to create an execution plan and cache it. After
that, it will used the cached plan either way. Check the BOL.

May 1 '07 #8

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

Similar topics

11
13644
by: =?Utf-8?B?U3RlZmFuIFdpbGhlbG0=?= | last post by:
Hello, played a little with orcas and went into a problem by building dynamic queries. I want to build a dynamic where clause but only with parameters that are not empty =(TextBox.Text != ""). Doing this by building a string is very simple, but how to do it with LINQ? string sql = ""; if (TextBox1.Text != "") sql +=...
15
10841
by: EDBrian | last post by:
My problem is this. Our clients create different fields they want to collect and we allow them build dynamic filters, reports etc... We run some TSQL to actually create the column and all works very well. We are now adding a lot more functionality to our filters and could really benefit from using the LINQ to SQL. I have experimented with...
0
3178
by: EDBrian | last post by:
Problem: We enable our clients to create custom fields they wish to collect. We want to use Dynamic LINQ to query this table (Just one). My initial post and suggestion(s) are here: http://shrinkster.com/tzp So I have a class called "ClientTable". All tables have some common fields, but I need to add more at runtime. Now I have a class that...
1
6982
by: Lacutas | last post by:
Hi I'm having some problems getting a dynamic LINQ query to work on my DataSet. The idea is that a user selects certain criteria, and then the LINQ query filters through the dataset making the requested changes. The table I am trying to alter is called RawData, this is defined in a DataSet but has no data columns as these aren't known until...
1
3737
by: john | last post by:
I'm trying to build a LINQ expression that will use a dynamic construction of a LIKE statement in the WHERE clause, it would look something like this in SQL: WHERE TaskGroup Like "*00*" OR TaskGroup Like "*20*" It would be many variations on the above.
10
2771
by: Jonathan | last post by:
Hi all, I have a file consisting fixed width records from which I need to extract only those lines meeting certain conditions. These conditions do change and I find myself recoding/compiling for each set of conditions then running again. e.g.
2
2517
by: Mucahit ikiz | last post by:
I cant make a full dynamic query in LINQ I have 2 situation methods (only_exp_query, only_tbl_query) those are working. .... using System.Linq.Dynamic; using System.Data.Linq; .... string connString = @"Data Source=.;Initial
4
2741
by: acgritt | last post by:
I am looking for some information on if it is possible/how to create a dynamic XLinq statement. I know for Linq to SQL you can do the following: var query = db.Customers. Where("City = @0 and Orders.Count >= @1", "London", 10). OrderBy("CompanyName"). Select("new(CompanyName as Name, Phone)"); I am wondering how to do this with Linq to...
4
3595
by: =?Utf-8?B?TWlrZSBDb2xsaW5z?= | last post by:
I am trying to set up a dynamic search using linq. I believe the syntax is correct, but cannot confirm it because when I try to cast my Session from a List<to IQueryable<>, I get a cast error “Unable to cast object of type System.Collections.Generic.List to type System.Linq.IQueryable”. Is there a way to cast List<to IQueryable<>, or is...
0
7761
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7999
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...
0
7845
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...
1
5403
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...
0
5129
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...
0
3532
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...
0
3517
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1978
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
0
801
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...

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.