Re: CommandType = TEXT vs Stored Procedure.

[Steve Gerrard]

Michel Posseth [MCP] wrote:

>
Well in my office the argument to do everyhting with SP`s or IO
routines in seperate dll`s is that we do not want to have to send out
a complete version of our app just because we had some minor DB
changes
For verry small apps i can inmagine that someone uses plain sql ,
however for buiness apps i would favor stored procedures not for
perfomance , but for grouping , maintainability , easier debugging ,
easier optimizing ( wich wil give you ... extra perfomance ) etc etc
etc
In my opinion the TS is doing fine when using SP`s he did not choose
the easy way but the way with the most beuatifull view and
perspective :-) but that is just my opinion .

I agree if the database client is the end user. I think things are different if
the database client is a web service, which in turn has the end user as a
client. In that scenario, updating after DB changes means updating only the web
service, not the windows client program. In my case, I put all the DB code in a
separate dll, referenced by the web service itself. It is a database code layer
residing on the server.

If the select statements are written with parameters, I think the results are
actually better than with SPs, at least with Oracle. Note that the following is
impressions from limited testing, not an exhaustive analysis.

For an SP, the return from the SP is a reference cursor, which is then under
the control of the .Net code. Oracle will not directly reuse that cursor,
because it has been "sent out" to a non-Oracle program. It can still cache the
plan, and cache internal session cursors, but it won't leave the actual cursor
open. A second request will open a new cursor to be returned to the calling
program.

If the web service uses a select statement, the Oracle driver will cache the
statement itself. After first use, it will leave the cursor open on the server,
since it stays under Oracle control the whole time. For the second request, it
does not need a parse, and does not need to open a new cursor at all. From the
point of view of the DB server, it is the same connection asking to re-read the
same cursor.

The result in my tests was that cursor reuse went up, and parsing went down,
when the Oracle driver was allowed to cache select statements, compared to using
stored procedures on the server that returned reference cursors. This was not
tested rigorously, and I would not hang my hat on it, but when used in
conjunction with a web service, cached parameterized select statements give
excellent results.

I do appreciate hearing different points of view on this topic, and chewing over
the details. For a serious db app, getting this layer into good shape makes a
big difference in the overall result.

[Cor Ligthert[MVP]]

Steve,

When I was reading your message I only could think: "Does Steve's
application more than managing the cursor"

Normally this should only be a minor issue to make a good solution.

Cor

"Steve Gerrard" <my********@comcast.netschreef in bericht
news:wt******************************@comcast.com. ..

Michel Posseth [MCP] wrote:

>>
Well in my office the argument to do everyhting with SP`s or IO
routines in seperate dll`s is that we do not want to have to send out
a complete version of our app just because we had some minor DB
changes
For verry small apps i can inmagine that someone uses plain sql ,
however for buiness apps i would favor stored procedures not for
perfomance , but for grouping , maintainability , easier debugging ,
easier optimizing ( wich wil give you ... extra perfomance ) etc etc
etc
In my opinion the TS is doing fine when using SP`s he did not choose
the easy way but the way with the most beuatifull view and
perspective :-) but that is just my opinion .

I agree if the database client is the end user. I think things are
different if the database client is a web service, which in turn has the
end user as a client. In that scenario, updating after DB changes means
updating only the web service, not the windows client program. In my case,
I put all the DB code in a separate dll, referenced by the web service
itself. It is a database code layer residing on the server.

If the select statements are written with parameters, I think the results
are actually better than with SPs, at least with Oracle. Note that the
following is impressions from limited testing, not an exhaustive analysis.

For an SP, the return from the SP is a reference cursor, which is then
under the control of the .Net code. Oracle will not directly reuse that
cursor, because it has been "sent out" to a non-Oracle program. It can
still cache the plan, and cache internal session cursors, but it won't
leave the actual cursor open. A second request will open a new cursor to
be returned to the calling program.

If the web service uses a select statement, the Oracle driver will cache
the statement itself. After first use, it will leave the cursor open on
the server, since it stays under Oracle control the whole time. For the
second request, it does not need a parse, and does not need to open a new
cursor at all. From the point of view of the DB server, it is the same
connection asking to re-read the same cursor.

The result in my tests was that cursor reuse went up, and parsing went
down, when the Oracle driver was allowed to cache select statements,
compared to using stored procedures on the server that returned reference
cursors. This was not tested rigorously, and I would not hang my hat on
it, but when used in conjunction with a web service, cached parameterized
select statements give excellent results.

I do appreciate hearing different points of view on this topic, and
chewing over the details. For a serious db app, getting this layer into
good shape makes a big difference in the overall result.