Performance problems with StoredProcedure in Web application

[Frank]

Hi,
imagine there's a WEB application reading data from an Oracle database
to visualize in using DataGrids in the clients browser. Yes, sounds
simple, just create OracleConnection + OracleCommand + DataAdapter,
bind a DataGrid to the DataAdapter, that's it.

Problem with that approach might be the hardcoded CommandString of the
OracleCommand. Whenever somebody requests query string changes you have
to adapt the C# code, to recompile and test your application, and last
but not least to deploy the new version to one or more servers.

To solve that the decision was made to create a StoredProcedure
containing the SQL CommandString, in case of query to be changed the
procedure independently from the C# application may be adapted.

Allright, after creating the StoredProcedure I adapted the C# code, so
that instead of the priviously used hardcoded query string of type
CommandType.Text the corresponding stored procedure of type
CommandType.StoredProcedure will be called. The procedure itself
supports a ReferenceCursor, which I bound to the return DataTable. No
big deal, in general it works.

BUT - THE PERFORMANCE:
Where the Text query took approx. 10s, the StoredProcedure took up to 2
minutes to return as the same data!
Does anybody have an idea what might be the reason for that behaviour?

Really appreciate your help - Frank

PS: Finaly two snapshots of the code, first the fast hardcoded version,
second the stored procedure based one:

[OLD]
dwhConnection.ConnectionString=(String)getSessionObject("dwhConnectionString");
generalDataAdapter.SelectCommand.CommandText= "SELECT * FROM MYTABLE";
DataTable dataGridInputTable=new DataTable("general");
generalDataAdapter.Fill(dataGridInputTable);
generalAdapter.Fill(dataGridInputTable);

[NEW]
OracleConnection oraConnection=new
OracleConnection((String)getSessionObject("oraConnectionString"));
OracleCommand generalQuery=new
OracleCommand("expt.expt_reports_pkg.getAllExperiments",
oraConnection);
generalQuery.CommandType=CommandType.StoredProcedure;
generalQuery.Parameters.Clear();
generalQuery.Parameters.Add(new OracleParameter("M_CURSOR",
OracleType.Cursor));
generalQuery.Parameters["M_CURSOR"].Direction=ParameterDirection.Output;
OracleDataAdapter currentAdapter=new OracleDataAdapter(generalQuery);
DataTable dataGridInputTable=new DataTable("general");
currentAdapter.Fill(dataGridInputTable);

 

[Dave Sexton]

Hi Frank,

I appreciate the background info.

BUT - THE PERFORMANCE:
Where the Text query took approx. 10s, the StoredProcedure took up to 2
minutes to return as the same data!
Does anybody have an idea what might be the reason for that behaviour?

It's probably the fault of the cursor. Any particular reason why you need the cursor? It's not being used by the textual query.

Really appreciate your help - Frank

PS: Finaly two snapshots of the code, first the fast hardcoded version,
second the stored procedure based one:

[OLD]
dwhConnection.ConnectionString=(String)getSessionObject("dwhConnectionString");
generalDataAdapter.SelectCommand.CommandText= "SELECT * FROM MYTABLE";
DataTable dataGridInputTable=new DataTable("general");
generalDataAdapter.Fill(dataGridInputTable);
generalAdapter.Fill(dataGridInputTable);

Stored procedures will commonly out-perform textual queries when selecting data, so I'd imagine that in stress situations the above
might perform worse then a stored procedure that does the same exact thing, but I doubt the opposite will ever be true.

[NEW]
OracleConnection oraConnection=new
OracleConnection((String)getSessionObject("oraConnectionString"));
OracleCommand generalQuery=new
OracleCommand("expt.expt_reports_pkg.getAllExperiments",
oraConnection);
generalQuery.CommandType=CommandType.StoredProcedure;
generalQuery.Parameters.Clear();
generalQuery.Parameters.Add(new OracleParameter("M_CURSOR",
OracleType.Cursor));
generalQuery.Parameters["M_CURSOR"].Direction=ParameterDirection.Output;
OracleDataAdapter currentAdapter=new OracleDataAdapter(generalQuery);
DataTable dataGridInputTable=new DataTable("general");
currentAdapter.Fill(dataGridInputTable);

Stored procedures are much safer to use because they're not susceptible to SQL injection attacks, where the user submits data in the
query string, for example, such as ""frank' OR 1=1) timedelay...". The exception is, of course, when executing dynamic SQL in your
stored procedures. I only mention this because of the background you supplied and how it sounded much more complex then your
example "SELECT * FROM MYTABLE", and the code above that executes the stored procedure. i.e., In your code examples you aren't
dealing with parameters but you seemed to imply that you use them.

I suggest keeping the procedure and removing the cursor to see if that helps.

 

[Frank]

Thank you Dave,

In general I'm with you regarding keeping the stored procedure (dynamic
SQL is not necessary) and to remove the REF_CURSOR.

But honestly this cursor is the only option I'm familiar with to return
multiple data records, each containing several column values itself.

So what are the alternatives? How to return a data table/data matrix
containing query results from a stored procedure?

Regards - Frank

 

[Dave Sexton]

Hi Frank,

I'm not really sure I understand your question so I'll try to address both possibilities, as I see them.

I'm not familiar with Oracle so I don't know if there is any alternative to using a cursor when returning multiple rows, but I must
assume that it's not your only option. And anyway, I really don't see how your second code sample could possibly work but since I'm
not familiar with Oracle I just had to assume that you know what your doing

If you are returning multiple result sets then you can use table mappings. The adapter recognizes each table in the result set with
a constant naming convention:

Table
Table1
Table2
Table3
....

So you can use the DataTableMappingsCollection (via the TableMappings property) to map each table in the result set to the DataTable
that the adapter must fill:

Table dtParentTable
Table1 dtChildTable
Table2 dtAnotherRelatedTable
....

You must determine the order in which you will return each result set from the procedure and then code the TableMappings to reflect
your choice by adding a single DataTableMapping for each table in the result set:

adapter.TableMappings.Add("Table", "dtParentTable");
adapter.TableMappings.Add("Table1", "dtChildTable");
adapter.TableMappings.Add("Table2", "dtAnotherRelatedTable");


Using DataTableMappings and DataColumnMappings on MSDN:
http://msdn.microsoft.com/library/d...cpconSettingUpDataTableDataColumnMappings.asp

(Ignore the first example in the link I posted because it's confusing and doesn't illustrate the common use of the Add method. Once
you understand how mappings work, then I suggest you review the first illustration again to see how mappings can be used in a manner
that alleviates the need for the constant naming convention used by DataAdapters. i.e. Table, Table1, Table2, becomes unnecessary.

 

[priyan]

Hi Frank,

Have you tried executing your query and SP using SQL-Plus to see the
difference in execution time ?

 

[Dave Sexton]

Hi Frank,

Actually, you could probably just ignore my comments about stored procedure performance as compared to textual queries because they
were based solely on my knowledge of SQL Server, not Oracle.

As for your question about how to avoid using a cursor (as I suggested was possible in Oracle) read the following paragraph for more
information.

Here's a section of the documentation from the MSDN topic on the OracleDataAdapter class:

The .NET Framework Data Provider for Oracle does not support batched SQL statements. However, it does allow you to use multiple REF
CURSOR output parameters to fill a DataSet, each in its own DataTable. You must define the parameters, mark them as output
parameters, and indicate that they are REF CURSOR data types. Note that you cannot use the Update method when the OracleDataAdapter
is filled using REF CURSOR parameters returned by a stored procedure, because Oracle does not provide the information necessary to
determine what the table name and column names are when the SQL statement is executed.

OracleDataAdapter on MSDN:
http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.aspx

At this point I'm not even sure how TableMappings could be useful to you. Sorry if I made you more confused

 

[Frank]

Hi Dave,

the MSDN article you're referring to describes in detail the current
status of my source code. As mentioned the SQL select statement has
been encapsulated in a StoredProcedure, which returns results via
REF_CURSOR. Afterwards the C# code has been adapted as described in the
article.
Technically that works, but accessing data via STORED_PROCEDURE instead
of the previously used (identic) SELECT statement takes 10 times
longer. Of course this is not acceptable, especially for a WEB
application.

So unfortunately I still do not know the reason for this performance
difference, as mentioned select statement and volume of retrieved data
both are identical.

Thanks anyway, Frank

 

[Dave Sexton]

Hi Frank,

Well you haven't made it clear to me whether you are trying to select multiple result sets (tables) or just a single result set
(table) with multiple rows. The article made it seem like Oracle provides a way to do the latter without cursors, which I assume
will increase the performance of your application. That's just an assumption however since the article doesn't mention anything
about selecting only a single result set.

I suggest that you do further research on Oracle to find out if you can do the same thing you are trying to do now but without
cursors, because I still believe that cursors are the source of the degradation in performance that you have observed based on my
experience with SQL Server.

GL

 

[Frank]

...well, thanks for all the answers,
but instead of the suspicious RefCursor within the StoredProcedure the
query itself was the performance killer. There was only one additional
column to be accessed via the StoredProcedure, which slowed done
everything.

Finally I'm very happy with the RefCursor, works really fine and fast.

Frank

Lessons learned: Never trust a colleague telling you "query A is as the
same as query B" without checking yourself

 

[Dave Sexton]

Hi Frank,

Thanks for the follow-up. (I'll be sure to keep my "mouth" shut when Oracle questions pop up in the future.)