none
DataReader taking hours to load data. RRS feed

  • Question

  • One of our Application runs on Microsoft's HPC grid.

    This application loads lots of looks up from the database and caches them for later use. What we have noticed a couple of days back was that one particular lookup took 5 hours to load. This lookup loads data from a view that returns almost 1.3 million rows

    Below is the code loads the view in to the lookup

    List<NettingCollateralLookup> l= new List<NettingCollateralLookup>();
    
    While(reader.Read())
    
    l.Add(new NettingCollateralLookupDR(reader));

    private class NettingAndCollateralDR : NettingAndCollateral
            {
                private const string fld_razorNettingSysId = "razorNettingSysId";
                private const string fld_nettingAgreementId = "nettingAgreementId";
                private const string fld_razorNettingCode = "razorNettingCode";
                private const string fld_razorCSACode = "razorCSACode";
                private const string fld_razorCounterpartyCode = "razorCounterpartyCode";
                private const string fld_nettingInternalUnit = "nettingInternalUnit";
                private const string fld_nettingProductCode = "nettingProductCode";
                private const string fld_nettingCountryCode = "nettingCountryCode";
                private const string fld_razorNettingType = "razorNettingType";
                private const string fld_collateralCCY = "collateralCCY";
                private const string fld_callPeriodDays = "callPeriodDays";
                private const string fld_collateralMTA = "collateralMTA";
                private const string fld_nettingMaturityDate = "nettingMaturityDate";
                private const string fld_collateralThreshold = "collateralThreshold";
                private const string fld_collateralAgreeementId = "collateralAgreementId";
                private const string fld_anzCallInd = "anzCallInd";
                private const string fld_counterpartyLagCalendarDays = "counterpartyLagCalendarDays";
    
                public NettingAndCollateralDR(IDataReader reader)
                {
                    this.NettingAgrementId = reader.GetNullableInt64(FieldIndexes[fld_nettingAgreementId]);
                    this.RazorNettingSysId = reader.GetNullableInt64(FieldIndexes[fld_razorNettingSysId]);
                    this.RazorNettingCode = reader.GetNullableString(FieldIndexes[fld_razorNettingCode]);
                    this.RazorCounterpartyCode = reader.GetNullableString(FieldIndexes[fld_razorCounterpartyCode]);
                    this.RazorNettingType = reader.GetNullableString(FieldIndexes[fld_razorNettingType]);
                    this.NettingMaturityDate = reader.GetNullableDateTime(FieldIndexes[fld_nettingMaturityDate]);
                    this.NettingCountryCode = reader.GetNullableString(FieldIndexes[fld_nettingCountryCode]);
                    this.NettingProductCode = reader.GetNullableString(FieldIndexes[fld_nettingProductCode]);
                    this.NettingInternalUnit = reader.GetNullableString(FieldIndexes[fld_nettingInternalUnit]);
                    this.RazorCSACode = reader.GetNullableString(FieldIndexes[fld_razorCSACode]);
                    this.CollateralCCY = reader.GetNullableString(FieldIndexes[fld_collateralCCY]);
                    this.CallPeriodDays = (int?)reader.GetNullableInt16(FieldIndexes[fld_callPeriodDays]);
                    this.CollateralMTA = (float?)reader.GetNullableDecimal(FieldIndexes[fld_collateralMTA]);
                    this.CollateralThreshold = (float?)reader.GetNullableDecimal(FieldIndexes[fld_collateralThreshold]);
                    this.CollateralAgreementId = reader.GetNullableInt64(FieldIndexes[fld_collateralAgreeementId]);
                    this.AnzCallInd = reader.GetNullableString(FieldIndexes[fld_anzCallInd]);
                    this.CounterpartyLagCalendarDays = reader.GetNullableInt16(FieldIndexes[fld_counterpartyLagCalendarDays]);
                }
    
            }

    What i don't understand is that application loaded this lookup in 150 seconds on a couple of compute nodes however it took almost 5 hours for the remaining compute nodes to load data.

    I tried removing schema reading part from data reader but this didn't help either as i read somewhere that datareader["ColumnName"] tends to hang sometimes.


    this.NettingAgrementId = reader.GetNullableInt64(0);
                        this.RazorNettingSysId = reader.GetNullableInt64(1);
                        this.RazorNettingCode = reader.GetNullableString(2);
                        this.RazorCounterpartyCode = reader.GetNullableString(3);
                        this.RazorNettingType = reader.GetNullableString(4);
                        this.NettingMaturityDate = reader.GetNullableDateTime(5);
                        this.NettingCountryCode = reader.GetNullableString(6);
                        this.NettingProductCode = reader.GetNullableString(7);
                        this.NettingInternalUnit = reader.GetNullableString(8);
                        this.RazorCSACode = reader.GetNullableString(9);
                        this.CollateralCCY = reader.GetNullableString(10);
                        this.CallPeriodDays = (int?)reader.GetNullableInt16(11);
                        this.CollateralMTA = (float?)reader.GetNullableDecimal(12);
                        this.CollateralThreshold = (float?)reader.GetNullableDecimal(13);
                        this.CollateralAgreementId = reader.GetNullableInt64(14);
                        this.AnzCallInd = reader.GetNullableString(17);
                        this.CounterpartyLagCalendarDays = reader.GetNullableInt16(18);


    This situation is not always reproducible. Is it datareader related problem ( if it is , why isnt it consistent) or is it database related issue in that when two or more compute nodes try to open and access the datareader ,database is slowing up and hence the datareader is slowing down ?

    Whats the best way to load this huge data in memory. DataTable ? I need all the rows from the view and need to cache the lookup.

    Update: BTW i tried to run trace on sql server , i saw a couple of existing connections still open in the trace (TCP IP connections)(.NET data provider), i haven't been able to see any sql associated with them which means probably the server has finished runnning the sql and it is the .net data adapter code is still trying to push row by row from server to the client ? But why is it taking so long. Is there some database setting that is causing the .NET data adapter this degraded performance ?









    • Edited by vijay_g Monday, October 28, 2013 5:48 AM
    Monday, October 28, 2013 3:34 AM

Answers

  • Hello,

    >>Whats the best way to load this huge data in memory. DataTable ?

    Please have a look at the article:

    Best Practices for Using ADO.NET: http://msdn.microsoft.com/en-us/library/ms971481.aspx

    >>I saw a couple of existing connections still open in the trace.

    It may be that in the program we have not dispose the connection timely.

    So I recommend we can write codes using try-catch block as below:

    try
    
                {
    
                    connection.Open();
    
                }
    
                catch (Exception ex)
    
                {
    
                    Console.WriteLine(ex.Message.ToString());
    
                    flag = false;
    
                }
    
                finally
    
                {
    
                    connection.Close();
    
                    connection.Dispose();
    
                }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 28, 2013 7:44 AM
    Moderator