none
Updating rows in dataset is verrrry slow and just won't optimize RRS feed

  • Question

  • Hello,

    I am retrieving query results from Oracle using ADO.NET 2.0 and VS2005.  After retrieving the data I iterate through the returned data table to update 1 string field to represent the state of about 4 other strings in the row.

    I know that updating 1 field in each row is the reason for the awful delay (I removed the actual update and the difference was startling.

    I have tried (hopefully not) everything.  All compare strings are set up as constants. I am using an enumerator to step through a dataView.  This update takes in excess of 20 minutes to process 1200 (yes twelve hundred) rows.  I have tried to do the process on the Oracle Server, but Oracle seems to object to Fetching from a cursor passed in as a parameter.

    20 minutes to iterate over 1200 rows?  There is one unique key contraint which I have disabled for the loop.  It is below:

     

    DataView dv = dt.DefaultView; 

     

    IEnumerator dr = dv.GetEnumerator();

    dr.Reset();
    //Position at the Beginning

     

    {

    if (((DataRowView)(dr.Current))["WorkPlan"].ToString().Equals((((DataRowView)(dr.Current))["PupilID"].ToString())) && ((DataRowView)(dr.Current))["AssignedProv"].ToString().Equals(vendor) && ((DataRowView)(dr.Current))["IsActive"].ToString().Equals(LocalConstants.YES) && Convert.ToDecimal((((DataRow)(dr.Current))["BenefitLeft"].ToString())) >= 0)
    { ((DataRowView)(dr.Current))["BillStatus"] =  LocalConstants.OKSTATUS; continue;  DataRowView)(dr.Current))["EligStatus"].ToString())); DataRowView)(dr.Current))["ProgStatus"].ToString())); if (localStatus.Length > 0)
    {
    localStatus.Replace(localStatus.ToString(),
    LocalConstants.EMPTYSTRING); if ((eStatus.ToString().Equals(LocalConstants.ELIGIBLE_ELIG_STATUS) && pStatus.Equals(LocalConstants.BAD_PROV)) || !((DataRowView)(dr.Current))["AssignedProv"].ToString().Equals(vendor)) LocalConstants.INELIG_PROV); if (Convert.ToDecimal(((DataRowView)(dr.Current))["BenefitLeft"]) <= 0) LocalConstants.OVER_LIMIT); if (eStatus.ToString().Equals(LocalConstants.INELIG_SCHOOL_STATUS)) LocalConstants.INELIG_SCHOOL); if (eStatus.ToString().Equals(LocalConstants.INELIG_STATUS) || eStatus.Equals(LocalConstants.INELIG_LUNCH_STATUS)) LocalConstants.INELIG_STUDENT);

     

    }

    eStatus.Replace(eStatus.ToString(), (((

    pStatus.Replace(pStatus.ToString(), (((

     

    }

     

    {

    localStatus.Append(

    }

     

    {

    localStatus.Append(

    }

    {
    localStatus.Append(

    }

    {

    localStatus.Append(

    }

    if (!((((DataRowView)(dr.Current)))["WorkPlan"].ToString().Equals(((DataRowView)(dr.Current))["PupilID"].ToString()))) LocalConstants.NO_WORKPLAN); DataRowView)(dr.Current))["BillStatus"] = localStatus.ToString();

     As I said, that last statement is what cripples the whole process. 

    I could understand the delay if I were processing Star Catalogs but we are talking about 1200 records sometimes taking 1 minute per record.  I could do 10 or 20 faster before I lost focus.  Access could do this more quickly.

     The computers are dual Core with 2G of memory running Window XP.

    Comments or suggestions are requested.  I also have plenty but they all center around 1 minute per string assignment and conventional reaction to the possibility that hand updating this data is quicker than using a computer.

    Thanks for any help at all.

    {
    localStatus.Append(

    }

    ((

    }

    while(dr.MoveNext()) #region
    • Edited by Michael K2 Monday, March 15, 2010 8:15 PM reformat
    Monday, March 15, 2010 8:11 PM

Answers

  • Michael,

    Did you try to use an extra column with an expression to represent the state?
    You could also try to first call BeginEdit and at the end call EndEdit
    Is the dataview connected to a control which displays the data while you're updating the dataview, if so try it without this connection.

    Hope this helps.
    Marcel
    • Marked as answer by Michael K2 Tuesday, March 16, 2010 1:53 PM
    Tuesday, March 16, 2010 7:11 AM

All replies

  • Michael,

    Based on the code snippet you've posted, it sounds like the time is spent iterating through the contents of the DataView.  Is that accurate?  Or is more of the time spent connecting to Oracle, retrieving the original query results and/or submitting the changes back to the database.  I have a few suggestions and follow up questions depending on where the code seems to be getting bogged down.

    If you can move the processing to the database in the form of a PL/SQL stored procedure, that's likely your best bet in terms of performance.  This approach requires some knowledge of PL/SQL, as well as the database administrator's permission to create a stored procedure.


    David Sceppa
    Monday, March 15, 2010 8:53 PM
    Moderator
  • Michael,

    Did you try to use an extra column with an expression to represent the state?
    You could also try to first call BeginEdit and at the end call EndEdit
    Is the dataview connected to a control which displays the data while you're updating the dataview, if so try it without this connection.

    Hope this helps.
    Marcel
    • Marked as answer by Michael K2 Tuesday, March 16, 2010 1:53 PM
    Tuesday, March 16, 2010 7:11 AM
  • The delay is definitely happening on the client side.  Oracle returns the data pretty rapidly.  I have been trying to do this in the Oracle Stored Proc, but there seems to be an issue fetching from a populated cursor that was passed in as a parameter. 

    BeginEdit and EndEdit seem to slow it down a little but I know that if I change the row they get called anyway.

    Finally, no the Dataview is not connected to a control BUT the underlying Data Table is and setting the Datasource and Datamember of the Grid view to null makes it behave reasonably.

    Thank you
    Tuesday, March 16, 2010 1:53 PM