Databases
StackOverflow2013
Postsdbo
POODP.Net Stored Procedure performance issues on large datasets
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POODP.Net Stored Procedure performance issues on large datasets
    primarykey
    data
    text
    <p>We are having some major performance issues with SELECT queries out one of our databases. Please see the simple procedure and associated code below. </p> <p>In the code the ExecuteReader() method is executing in around 10 seconds on a query returning 30K records. Iterating the Reader takes 2 minutes (even when I am not pumping the data into any other object). 2 minutes for a 30k row data set is unacceptable for us as we are expecting datasets in the millions. </p> <p>Is there anything here that stands out to any of you? Hoping that your experience with ODP.NET and PL/SQL might help out.</p> <pre><code> create or replace PROCEDURE TRACKING_FETCH ( p_tracking_id IN NUMBER, p_parent_id IN NUMBER, p_media_id IN NUMBER, p_custodian_id IN NUMBER, p_return_cursor OUT SYS_REFCURSOR) AS BEGIN OPEN p_return_cursor FOR SELECT * FROM tracking WHERE (tracking_id = p_tracking_id OR p_tracking_id = 0) AND (parent_id = p_parent_id OR p_parent_id = 0) AND (media_id = p_media_id OR p_media_id = 0) AND (custodian_id = p_custodian_id OR p_custodian_id = 0); END TRACKING_FETCH; </code></pre> <p>--</p> <pre><code>using (DataFactory command = new DataFactory(dbConnection, DatabaseType.Oracle, CommandType.StoredProcedure, "TRACKING_FETCH")) { command.AddInParameter("p_tracking_id", DbType.Int32, trackingid); command.AddInParameter("p_parent_id", DbType.Int32, parentid); command.AddInParameter("p_media_id", DbType.Int32, mediaid); command.AddInParameter("p_custodian_id", DbType.Int32, custodianid); using (var dr = command.ExecuteReader()) { while (dr.Read()) { //Do Things... } } } </code></pre> <p>Any guidance will be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLuc M
    1. USmattdlong
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POODP.Net Stored Procedure performance issues on large datasets
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POODP.Net Stored Procedure performance issues on large datasets
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload