Michel Posseth [MCP] wrote:
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.
>
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 .
>
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 .
>
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.
Comment