Re: Pass-thru SQL performance vs Stored Proc

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 09:40:37 -0600

> This all arises because I am working on a project where it seems like we
> are going to have to create a lot of dynamic SQL based on user selects
> (mainly searching). A user can select one to more fields to search on
> which we have to 'AND' to our query. This is somewhat difficult to do in
> stored procs as you have to account for all the possible search criteria
> and add them all as parameters. This is where straight in-line (or
> pass-thru) SQL helps.

The problem with your scenario here is that cached plans have to meet a
stringent criteria, mainly that the query needs to be pretty much the same
each time to reuse the plan. In your case, if you have many different
criterium, and users regularly choose different parameters, then it will not
be useful.

So my suggestion is that if you have a relative few number of parameters and
a high performance need, then consider coding procedures. Build a very
generic stored procedure (look up optional parms here in this group) and
then optimize the troublesome parts, possibly even writing as many as one
stored procedure per permutation of parameters, particularly where testing
requires it. Obviously this is going to be tedious, so I would also
consider writing tools to generate this code.

On the other hand, if your system is not a very busy one, you might want to
just use dynamic SQL calls. It brings up security issues if you are not
using a middle tier (which I would suggest for any application taking
parameters and then executing queries from them,) but in terms of
performance, it will be good enough. Building the plan is rarely a large
cost, but even small costs take time. The difference being if it takes 100
ms per execution, and you are executing it 100 times an hour, no problem, a
minute, probably bad, or a second, then forget about it.

> And finally, should I be concerned about performance issues when using
> inline sql? Right now all our code is in stored procs.

In general, always use stored procedures. The case you just suggested where
you have a lot of parms building a large procedure is a possible exception,
just because of complexity, but in general, I always strive to keep all code
in stored procedures for all of the good reasons (performance, security,
encapsulation, etc) 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Tim Mavers" <webview@hotmail.com> wrote in message 
news:8oWdnUBpAusPF3vcRVn-uQ@giganews.com...
>I know this has been talked about before, but I couldn't find any specific 
>answers to my question searching Google Groups.
>
> What I am curious about is if SQL-Server (2000) does any caching of 
> pass-thru SQL code?  In other words, inside my C# code I execute straight 
> TQL (e.g. "select * from users") vs. calling a stored procedure to do the 
> same thing (this is a very simple example), does SQL-Server cache the 
> former?  If so, would stored procedures perform better?
>
> This all arises because I am working on a project where it seems like we 
> are going to have to create a lot of dynamic SQL based on user selects 
> (mainly searching).  A user can select one to more fields to search on 
> which we have to 'AND' to our query.  This is somewhat difficult to do in 
> stored procs as you have to account for all the possible search criteria 
> and add them all as parameters.  This is where straight in-line (or 
> pass-thru) SQL helps.
>
> I thought I read somewhere that SQL-Server will cache in-line SQL calls 
> (compile it as it would a stored proc) so you wouldn't have as much as a 
> performance penalty.  Is this true?  It seems that if we are passing 
> dynamically built SQL, how can it cache it?  Would SQL only cache calls 
> with the exact same signature?  If so, how does it do this with a stored 
> proc where param values are always different?
>
> And finally, should I be concerned about performance issues when using 
> inline sql?  Right now all our code is in stored procs.
>
> Thanks!
>

Relevant Pages

  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Pass-thru SQL performance vs Stored Proc
    ... answers to my question searching Google Groups. ... pass-thru SQL code? ... I thought I read somewhere that SQL-Server will cache in-line SQL calls ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)
  • Re: choices regarding where to place code - in the database or middle tier
    ... Sure, the DBMS is a good place for simple referential integrity constraints, ... to 4 separately-running-but-pipelined stored procedures, ... A typical user would enact a 100 or so business functions per day. ... own stored procedures' by storing the SQL for every business query in the DBMS ...
    (comp.lang.java.programmer)
  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
Loading