Re: SQL Statement Class Helper request
- From: Jon Skeet [C# MVP] <skeet@xxxxxxxxx>
- Date: Tue, 4 Oct 2005 21:14:19 +0100
Nicholas Paldino [.NET/C# MVP] <mvp@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> *sigh* perhaps you had too much to drink the night Ian and I went over
> this with you =)
Not at all - but I disagreed with you then too, and see no reason not
to do so publicly :)
> On SQL Server (and most other relational database servers) stored procs
> will give you a perf increase because the server will have the execution
> plan cached. SQL Server will do this as well for regular queries issued
> against the DB, however, those caches are invalidated when you close the
> connection (there is also the notion of "preparing" a query for use multiple
> times, but once again, connection-specific, if you close the connection, or
> retrieve it from a pool, the cache goes bye-bye).
One advantage of using Java for the connection pool - this doesn't seem
to be an issue using c3p0+jtds.
(I must check this behaviour some time by the way - it would seem to be
a silly way for MS to implement the connection pool... Is it documented
anywhere?)
> I agree with you that there are ways you might want to filter the query.
> In the case where you want to filter on the result set only, it is better to
> place the code performing the select/logic in a function that returns a
> table, and then select from the function, filtering the return values.
Why?
> In the case where you need to place a filter on values that are not
> exposed through the final result set (which is not easily predictable so
> that you can't parameterize the function to provide those filters), then I
> would agree that dynamic sql is the way to go.
And it's really helpful (IMO) to generate the SQL with a tried and
tested component.
> Depending on how complex your logic is, portability of stored procs can
> be very difficult or very easy. Typcially, I don't place much logic in my
> database, and stick to the basics. It's not perfect, but it helps in
> portability. An ORM is much easier to port, I'll give you that, but it's
> very possible that it won't be a requirement.
Indeed - we don't know enough at the moment to know whether it's an
advantage in this case or not.
--
Jon Skeet - <skeet@xxxxxxxxx>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
.
- Follow-Ups:
- Re: SQL Statement Class Helper request
- From: Nicholas Paldino [.NET/C# MVP]
- Re: SQL Statement Class Helper request
- References:
- SQL Statement Class Helper request
- From: ABC
- SQL Statement Class Helper request
- Prev by Date: Re: Type Comparison
- Next by Date: Re: SQL Statement Class Helper request
- Previous by thread: Re: SQL Statement Class Helper request
- Next by thread: Re: SQL Statement Class Helper request
- Index(es):
Relevant Pages
|
