Re: CRUD in SP or Dynamic SQL

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



"MattC" <m@xxxxx> wrote in message
news:eWfzCGGYFHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>


.

Relevant Pages

  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... > under the impression that all stored procedures contain dynamic SQL. ... more than 1.5 MLoC of Fortran code + more than 1.2 MLoC of C++ ...
    (microsoft.public.dotnet.security)
  • Re: Dynamic SQL
    ... Dynamic SQL versus stored procedures is really a minimal discussion. ... Obviously if you CAN use stored procedures, you are going to have a MUCH ... With only minimal modification you could make your middle tier use stored ... > While I am not trying to start another discussion about> business rules and where they ...
    (microsoft.public.sqlserver.programming)
  • Re: Exec statements in stored procedures
    ... > dynamic sql executes in the security context of the current user. ... Dynamic SQL accesses objects directly so permissions on those objects are ... > No. Stored procedures execute in the context of the owner, ...
    (microsoft.public.sqlserver.programming)
  • Re: Confused about proc vs. dynamic SQL vs LINQ
    ... stored procedures are going to be faster than ... performance from dynamic sql (sql that you generate on the client and ... as well as if the queries are the same and a number ... another execution plan would be faster because statistics changed. ...
    (microsoft.public.dotnet.languages.csharp)