Re: Pass-thru SQL performance vs Stored Proc
galenboyerdev_at_hotpop.com
Date: 01/13/05
- Next message: Munch: "Scan and Update"
- Previous message: John Viele: "SQL Statement Formatter"
- In reply to: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Next in thread: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Reply: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Messages sorted by: [ date ] [ thread ]
Date: 13 Jan 2005 10:53:01 -0500
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> writes:
> > 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)
I would think you could do something like below and get fairly far:
- Parameterize each and everything that can be a restriction.
- Default the restriction to " and 1=1 ".
-- Galen Boyer
- Next message: Munch: "Scan and Update"
- Previous message: John Viele: "SQL Statement Formatter"
- In reply to: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Next in thread: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Reply: Louis Davidson: "Re: Pass-thru SQL performance vs Stored Proc"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
