By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,304 Members | 3,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,304 IT Pros & Developers. It's quick & easy.

Planning on going with dynamic SQL, but...

P: n/a
I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.

Thanks,
Ron
Jun 13 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:Or*************@TK2MSFTNGP02.phx.gbl...
Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?
Two main reasons:

1) stored procedures are pre-compiled, so they (almost always) execute more
efficiently (i.e. faster) than dynamic SQL

2)
http://www.google.co.uk/search?sourc...=SQL+Injection
--
http://www.markrae.net

Jun 13 '07 #2

P: n/a
On Jun 13, 7:01 pm, "Ronald S. Cook" <r...@westinis.comwrote:
I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.

Thanks,
Ron
Ron:

In addition to Mark's comment, stored procedures are generally safer
in terms of SQL injection attacks and the like. One additional
comment: I use stored procedures as low-level data access/data writing
agents and have all of my core business logic in whatever business
logic layer I'm developing. There are pros/cons and holy wars over
this stuff, I know, but I've found this rule of thumb useful over the
years.

If you are developing on SQL Server, one helpful tool I've used in the
past is SPInvoke. It will read the SPs from SQL Server and generate an
object-based data access layer for you, that support sync and async
calls into the stored procedures - it works like a charm and has saved
me hundreds of hours of development.

Good luck,
jpuopolo

Jun 14 '07 #3

P: n/a
A bit OT, really, but...
I'm an SP fan myself, but to counter the existing posts:

* in many cases, on "decent" RDBMS, the performance will be very
comparable; re-use of query plans is better than it used to be,
especially with parameterised commands. SPs might be faster, but often
not by as much as you'd think [see also last point]

* ideally, and command you write would be parameterised; as such, it
doesn't give you any additional injection protection unless you are
writing your non-SP commands badly

Additional points:
* SPs tend to be vendor specific (perhaps not even existing on your
RDBMS); if vendor portability is an issue, then ORM like NHibernate or
(later) D-LINQ might be of interest; this is almost always raw
(parameterised) commands

* SPs allow you finer security control; it is hard to hack a database
if you don't have CREATE/SELECT/INSERT/UPDATE/DELETE permissions, but
can only EXEC existing (approved) SPs

* You can change the SPs without recompiling the client; especially
useful if clients with different architectures (java, .Net, gupta, etc
- perhaps a few reporting tools too) call the same SPs

* SPs can act kinda like a contract, creating a reasonable join-point
between technologies; unfortunately, the grids/columns aren't normally
part of that contract (just the name and params) so this is a weak
metaphor

* Sometimes, freaky performance just happens. I've seen massive
performance changes just be moving an obscure, inoffensive [i.e. runs
instantly on its own] line from an SP to a second SP and calling EXEC.
And yes, I understand lots about recompiles (DDL/DML interleave, KEEP
PLAN, etc) - it wasn't that simple. Hard to do that using raw SQL
without messy hacks like sp_ExecuteSQL

* Sometimes, the SQL to do something that looks simple is just
offensively complex; my current example here is some "temporal
database" work I have been doing. In which case, you can script your
SPs (however you want) and invoke a 1-line command instead of sending
a huge command down; this is the main counter-point to my first point:
sometimes, size does matter.

Marc

Jun 14 '07 #4

P: n/a
Mark Rae <ma**@markNOSPAMrae.netwrote:
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:Or*************@TK2MSFTNGP02.phx.gbl...
Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Two main reasons:

1) stored procedures are pre-compiled, so they (almost always) execute more
efficiently (i.e. faster) than dynamic SQL
No, they really don't. This has been done to death, and it does depend
on the database, but in most databases prepared queries go through the
same optimisation path as stored procs, I believe. As the OP has
already said:

"I ran a few performance test for myself and it appears to be a wash."
2)
http://www.google.co.uk/search?sourc...t&hl=en-GB&ie=
UTF-8&rlz=1T4GGIH_en-GBGB220GB220&q=SQL+Injection
That's a good reason to parameterise queries, but not a good reason to
avoid dynamically creating the queries themselves.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jun 14 '07 #5

P: n/a
Ronald S. Cook <rc***@westinis.comwrote:
I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
I'd say it's mostly preference, unless you want fine-grained security
(where stored procs win). One place where it *is* worth using stored
procs is if you need to process a lot of data but not actually retrieve
it all.

For instance, you wouldn't count the number of rows matching a query by
running the query, retrieving all the results, and then counting (on
the client) how many rows there are - you'd use COUNT in SQL. Now
imagine the same kind of "we don't need all the data" scenario but with
a more complicated situation which isn't expressible in a single SQL
query. At that point, stored procs can be a big win.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jun 14 '07 #6

P: n/a

"Marc Gravell" <ma**********@gmail.comwrote in message
news:11**********************@q19g2000prn.googlegr oups.com...
>A bit OT, really, but...
I'm an SP fan myself, but to counter the existing posts:
I liked your post Marc, and here are my 2 cents.

* in many cases, on "decent" RDBMS, the performance will be very
comparable; re-use of query plans is better than it used to be,
especially with parameterised commands. SPs might be faster, but often
not by as much as you'd think [see also last point]
This is why I don't decide to you use or the other technique, but always
start a new project with the idiom of 'we will use both stored procedures
and dynamic sql'.

This way, a huge chuck of sql can always be moved into a sproc or vice
versa.
>
* ideally, and command you write would be parameterised; as such, it
doesn't give you any additional injection protection unless you are
writing your non-SP commands badly
This is a valid point. While you may set up rules that all coders MUST use
parameters, when things get stressful, coders tend to 'forget' that rule,
starts to code sql by concating strings; and open up for sql-injection.

I think this is the argument that the hardcore SP-fanatics use. If you give
the coders full access to the database, they MIGHT do stupid/unsecure things
with it.
>
Additional points:
* SPs tend to be vendor specific (perhaps not even existing on your
RDBMS); if vendor portability is an issue, then ORM like NHibernate or
(later) D-LINQ might be of interest; this is almost always raw
(parameterised) commands
I agree. Linq for SQL is a neat way of handling CRUD for instance.
CRUD via SQL is just tiresome.

And I don't know how many times I have written a DELETE statement for Remove
and forgot to add the (very important *s*) WHERE clause. Ouch =)
>
* SPs allow you finer security control; it is hard to hack a database
if you don't have CREATE/SELECT/INSERT/UPDATE/DELETE permissions, but
can only EXEC existing (approved) SPs
This is true.

But at the same time, having the typical 5 sprocs for Create, Update, List,
Fetch, FetchAll per table gives you an enourmous amount of sprocs. Just
navigating and managing all the sprocs is a nightmare.

* You can change the SPs without recompiling the client; especially
useful if clients with different architectures (java, .Net, gupta, etc
- perhaps a few reporting tools too) call the same SPs
I have heard this argument before. But i think it is a weak argument
How often do you change the table-schema without making changes in the
client(s)?
* SPs can act kinda like a contract, creating a reasonable join-point
between technologies; unfortunately, the grids/columns aren't normally
part of that contract (just the name and params) so this is a weak
metaphor
Hmm. One could argue that classes also serves as the contract.
If a bug is in the SP or in dynamic sql, it is still the same; a bug.
>
* Sometimes, freaky performance just happens. I've seen massive
performance changes just be moving an obscure, inoffensive [i.e. runs
instantly on its own] line from an SP to a second SP and calling EXEC.
And yes, I understand lots about recompiles (DDL/DML interleave, KEEP
PLAN, etc) - it wasn't that simple. Hard to do that using raw SQL
without messy hacks like sp_ExecuteSQL

* Sometimes, the SQL to do something that looks simple is just
offensively complex; my current example here is some "temporal
database" work I have been doing. In which case, you can script your
SPs (however you want) and invoke a 1-line command instead of sending
a huge command down; this is the main counter-point to my first point:
sometimes, size does matter.
This is why I like to be able to use both sprocs and dynamic sql.
if I notice that what was at first a benign sql-statement, turning into a
blob, I can always move it into a sproc.

I remember a funny wtf at worsethanfailure.com. A company had a policy of
using sprocs only. What do you do?

ALTER PROCEDURE DoSQL(@sql varchar(8000))
AS
EXEC (@sql)
RETURN

QED - Quite Easily Done =)
>
Marc
- Michael S
Jun 14 '07 #7

P: n/a
Hi,

"Mark Rae" <ma**@markNOSPAMrae.netwrote in message
news:O6**************@TK2MSFTNGP05.phx.gbl...
"Ronald S. Cook" <rc***@westinis.comwrote in message
news:Or*************@TK2MSFTNGP02.phx.gbl...
>Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Two main reasons:

1) stored procedures are pre-compiled, so they (almost always) execute
more efficiently (i.e. faster) than dynamic SQL

I have read somewhere that this is not much an issue now. (especially with
just a query SP). The server does cache queries submitted and they are
already compiled when the are ran again.
IMO SP is the best way to handle DB access simply cause it isolate the
physical DB structure from the other layers (business, etc).
Having TSQL all over the place in your app is not just a pain but an
accidend waiting to happen. if something int he DB change (a table is
denormalized for example) you have to track ALL the methods where that table
is refrenced in the code, change it , recompile it and finally deploy it. IF
you have it in a SP you just need to change it. Heck you can even do it live
(while the apps are running) and it will have no impact.
Jun 14 '07 #8

P: n/a
Hi,

"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP*********************@msnews.microsoft.com. ..
>
For instance, you wouldn't count the number of rows matching a query by
running the query, retrieving all the results, and then counting (on
the client) how many rows there are - you'd use COUNT in SQL.
You can do the same using dynamic: SELECT COUNT(*) FROM ..... WHERE .....
Now
imagine the same kind of "we don't need all the data" scenario but with
a more complicated situation which isn't expressible in a single SQL
query. At that point, stored procs can be a big win.
I do always use SP simply cause the are the abstraction of the physical DB
structure, if not used the app needs to know the internal of the DB (tables,
views, etc). Heck I have seen code where a linked server was being used in
the code , when we had to move the app to another location, well hell got
loose :)
Jun 14 '07 #9

P: n/a
On Jun 14, 3:47 pm, "Ignacio Machin \( .NET/ C# MVP \)" <machin TA
laceupsolutions.comwrote:
For instance, you wouldn't count the number of rows matching a query by
running the query, retrieving all the results, and then counting (on
the client) how many rows there are - you'd use COUNT in SQL.

You can do the same using dynamic: SELECT COUNT(*) FROM ..... WHERE .....
Yes (although I'd use COUNT(1) instead of COUNT(*))- it was just an
example of something where it makes sense to get the database to do
work rather than the client.
Now
imagine the same kind of "we don't need all the data" scenario but with
a more complicated situation which isn't expressible in a single SQL
query. At that point, stored procs can be a big win.

I do always use SP simply cause the are the abstraction of the physical DB
structure, if not used the app needs to know the internal of the DB (tables,
views, etc). Heck I have seen code where a linked server was being used in
the code , when we had to move the app to another location, well hell got
loose :)
If you structure it well, it shouldn't be hard to change stuff - and
the nightmare of doing complicated things in SPs can be truly
horrific. I worked on a message tracking system allowing you to search
by multiple criteria. Building up the query using Hibernate was
trivial - doing the same thing in stored procs (whilst avoiding SQL
injection attacks) would have been really nasty.

If a table name had changed, I'd have had to change a single line of
code, and that's all (aside from the unit tests, of course). I'm not
talking about lots of SQL being hard-coded into the app - I'm talking
about SQL which is dynamically generated SQL by an ORM system or the
like.

Jon

Jun 14 '07 #10

P: n/a
"Ignacio Machin ( .NET/ C# MVP )" <machin TA laceupsolutions.comwrote in
message news:%2***************@TK2MSFTNGP02.phx.gbl...
I have read somewhere that this is not much an issue now. (especially with
just a query SP). The server does cache queries submitted and they are
already compiled when the are ran again.
Yep - John has put me right on that score - I know my place... ;-)
IMO SP is the best way to handle DB access simply cause it isolate the
physical DB structure from the other layers (business, etc).
Having TSQL all over the place in your app is not just a pain but an
accidend waiting to happen. if something int he DB change (a table is
denormalized for example) you have to track ALL the methods where that
table is refrenced in the code, change it , recompile it and finally
deploy it. IF you have it in a SP you just need to change it. Heck you can
even do it live (while the apps are running) and it will have no impact.
Agreed on all counts...
--
http://www.markrae.net

Jun 14 '07 #11

P: n/a
Ronald S. Cook wrote:
I've read a few posts on the stored procedure vs dynamic sql debate. I ran
a few performance test for myself and it appears to be a wash.

Given that, I'm leaning toward dynamic sql mostly because it would mean one
fewer place to have things.

But, before we go that route we wanted to ask the question:

Is there any compelling reason why we shouldn't abandon all of our stored
procs and just write the SQL inside inside our functions in our business
layer (essentially our data access layer)?

Or, is it just preference these days?

I was leaning toward procs, but I have to admit it would be nice not to have
to keep up with all of them per all of our functions that call them.
Real dynamic SQL where you concatenate values directly into an SQL
string is a no no for both practical and security reasons.

You need to use parameters.

Next question is whether to have the SQL in your C# code or in
a SP.

That is primarily a matter of application architecture.

Some points:

Do you want to have the business logic maintained in C# or SQL.
By a C# programmer or a DBA ?

By using SP's you will allow the logic to be reusable by
non-.NET code.

In general C# code is more readable and more maintainable than
SQL.

SP's will definitely be a problem if you change database.

SQL directly in the C# code will also cause problems when changing
database, but if you generate that SQL code using an O/R mapper
or similar tool, then that problem can be mitigated.

You need to analyze your specific context, make a decision and stick
to it.

Arne

Jun 15 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.