CRUD in SP or Dynamic SQL

[MattC]

Hi,

I'm implementing a new Business Layer in one of our applications. I'm
toying with the idea of placing all the Create, Read, Update and Delete SQL
in the object in question and build a dynamic SQL string using a class
builder.

The pros I can see are:
It reduces the number of stored procs to admin.
Changes to the underlying table schema does not require massive changes to
large numbers of storeprocs

The cons are probably I can se are:
the potential for malicious code to be generated would be higher.
The slight loss in performance do to no execution plan being available to
cache.

I would appreciate any ideas, opinions URL links etc

TIA

MattC

 

[The Crow]

Hi,

I'm implementing a new Business Layer in one of our applications. I'm
toying with the idea of placing all the Create, Read, Update and Delete
SQL
in the object in question and build a dynamic SQL string using a class
builder.

The pros I can see are:
It reduces the number of stored procs to admin.
Changes to the underlying table schema does not require massive changes to
large numbers of storeprocs

Wouldnt database schema change affect the way you build dynamic sql in ur
code? and it requires compilation.

 

[MattC]

Yeah that's gonna happen regardless, but I wouldn't have to change the
update/insert/read stored procs as well as the code.
MattC

 

[PB]

Stored procedures perform better than dynamic SQL. A stored procedure is
parsed and complied once (the first time it is executed). Every time you
subsequently execute the sp, the parsed/compiled version gets executed.
Dynamic SQL requires parsing and compiling on *every* execution. This is a
bit more runtime overhead - which negatively impacts your application's
scalability.

Also from a maintenance perspective - when you use stored procedures - you
have one place to make an update (in your SQL Server); whereas dynamic SQL
requires a recompile of your application and redistribution and
reinstallation. That's a whole lot of work - especially if the change is
small - and most changes are typically small.

It's a push regarding radical database schema changes (because app logic and
supporting sql will likely both change) - but such changes are typically far
less frequent in occurance compared to small changes.

Personally I'd opt for stored procedures over dynamic SQL for (1) improved
runtime performance; (2) simplified maintenance; and (3) protection against
SQL injection attacks.

-HTH

 

[Tien]

Think about CRUD in SP if your object is quite complex, but in most of
the time dynamic SQL will save lot of time.

http://www.nethoa.com

 

[MattC]

Well paramaterised queries and stored procs both have their execution plans
stored in the cache. From what I've read stored procs, by virtue of their
stronger signature, might be found quicker from the cache.

A change to a stored proc (and I'm talking changes that mean datatype, field
addition or removals) will require recompilation anyway, so that point is
moot.

From the perspective of saving a single set of information to a database the
performance cost versus maintainability. If I want to know how my OM is
transformed into a format acceptable to my RM I would simply look at my
class. With stored procs it's an extra level.

I think Yukon will do a lot to simplify this or provide an inbetween step
but I'm still undecided on whether to use SP's for my CRUD stuff.

MattC

 

[spinthemoose]

I use storedprocs exclusively. I write them in sql scripts and precede
each declaration with a matching (if this_storedproc exists, delete
this_storedproc) statement.

Storing them in sql scripts lets me keep them under version control
alongside the code that references them so they are always in sync.

Preceding each with a conditional removal statement allows me to run
the entire script w/o conflicts if i need to make a change, and has the
added benefit of serving as an install script as it describes the
entirety of my stored procs.

Obviously i don't store table definitions with remove statements in
these storedproc script files, or my table data would be cleared out
each time i ran these scripts.

By keeping each table definitions separate, however, i can group my
stored procedure by table or list them all in a single script. In
either case, storedprocs do not maintain any data, so removing them and
recreating them only incurs a (relatively) small cost at update time.

Even changes to the table schema can generally be handled by a few
search/replace queries on my script file when all my storedproc defn's
are laid out linearly in a flat text file. Either that or i use the
find feature of VIM to zip around my script definitions and make the
updates.

Using this method, i find that administration and update time is
minimal.

This seems to address (eliminate?) the pros that you list below and
suggest that storedprocs are, indeed, the way to go.

From the reading that i've done, the more calculation you can move into

the database, the more the server can optimize your query for speed,
and the fewer high-cost database accesses you need to incur in your
application.

cheers,

David.