Speed issue with my BLB/BB, Please Help.

RedShiftZ posted on Thursday, January 07, 2010

I have a BLB that is taking forever to come back... Executing the stored procedure it calls directly is fast. less than 1 second fast. Executing "BLB myBLB = BLB.GetBLB()" takes 25 seconds to return. Then running a foreach loop "foreach (BB myBB in myBLB)" takes 1min 28secs.

Here is what I'm using...

CSLA 3.6.3 N2 from JohnnyBee
MS SQL Stored Procedures
ADO.NET
Local Dataportal

AppointmentList.cs
private void DataPortal_Fetch()
    {
      RaiseListChangedEvents = false;
      using (var mgr = ConnectionManager<SqlConnection>.GetManager("DataStat"))
      {
        using (var cmd = mgr.Connection.CreateCommand())
        {
          cmd.CommandType = System.Data.CommandType.StoredProcedure;
          cmd.CommandText = "Appointments_SelectList";

          DateTime dtStart = DateTime.Now;
          using (var dr = new SafeDataReader(cmd.ExecuteReader()))
          {
            DateTime dtExec = DateTime.Now;
            TimeSpan tsExec = dtExec - dtStart; "<= 202 milliseconds"

            dtStart = DateTime.Now;
            while (dr.Read())
            {
              Appointment apt = Appointment.GetAppointment(dr);
              this.Add(apt);
            }
            DateTime dtDone = DateTime.Now;
            TimeSpan tsRead = dtDone - dtStart; "<= 23.5 Seconds WTF? Seriously?"

            RaiseListChangedEvents = true;
          }
        }//using
      }//using

      OnFetched();
    }

Appointment.cs
    public static Appointment GetAppointment(SafeDataReader dr)
    {
      return new Appointment(dr);
    }
    internal Appointment(SafeDataReader dr)
    {
      LoadData(dr);
    }

private void LoadData(SafeDataReader dr)
    {
      _tStamp = (byte[])dr.GetValue("TStamp");
      LoadProperty(AppointmentIDProperty, dr.GetInt32("AppointmentID"));
      LoadProperty(TypeProperty, dr.GetInt32("Type"));
      LoadProperty(StartDateProperty, dr.GetSmartDate("StartDate"));
      LoadProperty(EndDateProperty, dr.GetSmartDate("EndDate"));
      LoadProperty(SubjectProperty, dr.GetString("Subject"));
      {.... More LoadProperties ...}
      MarkOld();
}

tmg4340 replied on Thursday, January 07, 2010

Well... I did a little research using Reflector to try and confirm what I thought was happening. The way I read the code, when you open a DataReader, .NET doesn't return all the rows - it's basically a streaming cursor. So the fact that the ExecuteReader returns rather quickly doesn't really tell you much, because the data isn't actually read from the database until the Read() statement is called.

So, then we move on to the next set of questions:

1. How many rows does your SP return?

2. How big are the rows? You show a few properties, but how many do you actually have? And what datatypes are we dealing with?

3. Is there any extra processing you've put in the "Add" method?

Aside from that, you might try changing your DB call from returning a DataReader to returning a DataTable/DataSet. Not as a permanent change, but as a test to give you a baseline for how long it takes to actually get all the data back to your client. Then you can maybe get a better idea for where the bottleneck is.

HTH

- Scott

RedShiftZ replied on Thursday, January 07, 2010

1. How many rows does your SP return?
1329 rows

2. How big are the rows? You show a few properties, but how many do you actually have? And what datatypes are we dealing with?
46 columns = 17 Int32s, 5 SmartDates, 7 Bools, 16 Strings, 1 TimeStamp
11 are private backing fields (readonly fields), the rest are PropertyInfos
SQL Query Analyzer returns all data in under 1 second.

3. Is there any extra processing you've put in the "Add" method?
No. No overrides.

Aside from that, you might try changing your DB call from returning a DataReader to returning a DataTable/DataSet.
Not as a permanent change, but as a test to give you a baseline for how long it takes to actually get all the data back to your client.
Then you can maybe get a better idea for where the bottleneck is.

SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataTable sdt = new DataTable();
sda.Fill(sdt);

DateTime dtExec = DateTime.Now; "<= Still 25 Seconds"

tmg4340 replied on Thursday, January 07, 2010

Well... now you know where the bottleneck is.

1,329 rows doesn't sound like a ton of data, and if my math is right, 3 MB means the rows aren't all that big (just under 2K/row). But you're only getting about 53 rows/sec, regardless of how you're pulling the data. If a DataTable fill takes the same amount of time as your CSLA list fill, then the slowdown is not because of your code. So now you can turn to network issues, configuration, etc.

- Scott

RedShiftZ replied on Thursday, January 07, 2010

tmg4340:
Well... now you know where the bottleneck is.

1,329 rows doesn't sound like a ton of data, and if my math is right, 3 MB means the rows aren't all that big (just under 2K/row). But you're only getting about 53 rows/sec, regardless of how you're pulling the data. If a DataTable fill takes the same amount of time as your CSLA list fill, then the slowdown is not because of your code. So now you can turn to network issues, configuration, etc.

I was afraid you were going to say that... And I did a test... I put the DataTable fill directly in a TForm and called it without any CSLA... Same Time Problem with this code...

    private void Go2()
    {
      SqlConnection conn = new SqlConnection("Data Source=OHSSQL;Initial Catalog=MySQLServer;Integrated Security=True");
      try
      {
        DateTime dteBegin = DateTime.Now;

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "Appointments_Select";

        conn.Open();
        SqlDataAdapter sda = new SqlDataAdapter();
        sda.SelectCommand = cmd;
        DataTable sdt = new DataTable();
        sda.Fill(sdt);

        DateTime dteEnd = DateTime.Now;

        TimeSpan tsGetData = dteEnd - dteBegin; "<= 25 Seconds"
      }
      finally
      {
        conn.Close();
      }
    }

Since SQL Query Analyzer on my machine returned the results so fast, would it not be safe to say that the connection from my machine to the sql server is fine. That only leaves .NET configuration as the culprit doesn't it? So it's probably a good time to change venues for this conversation since this is no longer a CSLA question. Thanks. I guess I'll take this over to stackoverflow and see if anyone there can help me figure this out...

tmg4340 replied on Thursday, January 07, 2010

While Sql Query Analyzer and .NET aren't exactly an apples-to-apples comparison (.NET is very likely doing more with the data returned than SQA), I would probably agree that something in your .NET configuration may be the culprit. IIRC, the .NET SqlClient objects uses the native SQL Server communication mode, so you shouldn't be getting any OLEDB/ODBC overhead. But that might be something to investigate, just to be sure. I wouldn't 100% rule out the SQL Server configuration, but I'd expect that to affect more than just this one query. You probably should review the table setup and SP, just to make sure. If there were issues there, I'd expect to see them when you run SQA, but it couldn't hurt.

- Scott

JonnyBee replied on Friday, January 08, 2010

Hi,

It seems that the problem is not within the CSLA part of your code.

What columns/datatypes are returned from the query? Are there any BLOBs returned? Even if you are not loading all fields into the BO they would still be returned from the database.

How long time does it take to run this code (just iterating over the resultset):
   using (var mgr = ConnectionManager<SqlConnection>.GetManager("DataStat"))
      {
        using (var cmd = mgr.Connection.CreateCommand())
        {
          cmd.CommandType = System.Data.CommandType.StoredProcedure;
          cmd.CommandText = "Appointments_SelectList";

          DateTime dtStart = DateTime.Now;
          using (var dr = new SafeDataReader(cmd.ExecuteReader()))
          {
            DateTime dtExec = DateTime.Now;
            TimeSpan tsExec = dtExec - dtStart; "<= 202 milliseconds"

             // just iterating over the result
            dtStart = DateTime.Now;
            while (dr.Read())
            {
              // Appointment apt = Appointment.GetAppointment(dr);
              // this.Add(apt);
            }
            DateTime dtDone = DateTime.Now;
            TimeSpan tsRead = dtDone - dtStart; "<= 23.5 Seconds WTF? Seriously?"

            RaiseListChangedEvents = true;
          }
        }//using
      }//using

Have you tried to use the Query Express - as this tiny app runs .Net code on the client?
http://www.albahari.com/queryexpress.aspx