Re: Performance problems with StoredProcedure in Web application

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 ;)

--
Dave Sexton

"Dave Sexton" <dave@jwa[remove.this]online.com> wrote in message news:uhjUtJU7GHA.1492@xxxxxxxxxxxxxxxxxxxxxxx
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/default.asp?url=/library/en-us/cpguide/html/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. :)

--
Dave Sexton

"Frank" <frank.toeppel@xxxxxxxxxxxx> wrote in message news:1160575833.320920.143540@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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





.