N
Nick P
Hi
I have recently started working on a project which is using the
OracleHelper as their data access layer. This consists of 2 classes
OracleHelper
OracleHelperParameterCache
The first handles all database interaction with .NET and an Oracle
database (connection, proc/sql calls retrieving result sets/parameters
etc.
The second deals with caching the parameter list for a specified
stored procedure so as to 'improve' performance on the next call to
the proc.
My issue is this. When you want to call a proc this class must first
go to the database to retrieve the parameter list from the database
(using OracleCommandBuilder.DeriveParameters) caches the parameter
list, then it builds the list plugging in the values you specified and
then calls the procedure. The next time the proc is called the
parameter list is taken from the cache. Therefore everytime a proc is
called for the first time it requires 2 database calls.
Surely this is very inefficient especially in large apps with a large
number of stored procedures?
On previous projects we predefine our parameter list, this has the
drawback of if the procedure changes you may need to change the
parameter list but is more efficient because your parameter list is
already defined and your are not making unnecessary DB calls.
Any comments or feedback on previous experience would be welcome
Nick.
I have recently started working on a project which is using the
OracleHelper as their data access layer. This consists of 2 classes
OracleHelper
OracleHelperParameterCache
The first handles all database interaction with .NET and an Oracle
database (connection, proc/sql calls retrieving result sets/parameters
etc.
The second deals with caching the parameter list for a specified
stored procedure so as to 'improve' performance on the next call to
the proc.
My issue is this. When you want to call a proc this class must first
go to the database to retrieve the parameter list from the database
(using OracleCommandBuilder.DeriveParameters) caches the parameter
list, then it builds the list plugging in the values you specified and
then calls the procedure. The next time the proc is called the
parameter list is taken from the cache. Therefore everytime a proc is
called for the first time it requires 2 database calls.
Surely this is very inefficient especially in large apps with a large
number of stored procedures?
On previous projects we predefine our parameter list, this has the
drawback of if the procedure changes you may need to change the
parameter list but is more efficient because your parameter list is
already defined and your are not making unnecessary DB calls.
Any comments or feedback on previous experience would be welcome
Nick.