locked
A very very strange problem about method GetString(...) of class SqlDataReader RRS feed

  • Question

  • I developed a library, 2 of these classes in my lib are show below, of course only is a sample: 

    public class A
    {
          public virtual void FetchData(DbDataReader cDR)
          {
                 return;
          }
    }
    public class B
    {
            protected void OneMethod()
            {
                 // Here, I do something, such as connect database
                 // execute sql and get a DataReader(named cDR) that type is SqlDataReader
                 ...
                 cDR = ...;
                 ...
                 // I reflect my exe file and create a object cC that type is class C actually.
                 cC = ...;
                 ...
                 // this loop will be executed 1,000,000 times
                 while (cDR.Read())
                 {
                      string strValue = (string)cDR.GetString(3); 
                      cC.FetchData(cDR);
                 }
            }
    }

    Then I developed another program that can be built as EXE file.
    There is a class named C, listed below
    public class C : A
    {
          // Actual the type of cDR is SqlDataReader
          public override void FetchData(DbDataReader cDR)
          {
                 string strValue = (string)cDR.GetString(3);
          }
    }

    I used dotTrace to profile my program, then dotTrace shown:
    string strValue = (string)cDR.GetString(3); // here consumed time 128,664ms
    cC.FetchData(cDR);                                      // here consumed time 15,774ms

    Who can tell me that's why and how to figure out this problem.
    Thank you very very very much
    Monday, December 21, 2009 8:39 AM

All replies

  • First of all, there is no need to cast (GetString already returns a string). I cannot see the type of cC, so I cannot see why it takes so long to retrieve the data (remote db with slow connection?).
    Geert van Horrik - CatenaLogic
    Visit my blog: http://blog.catenalogic.com

    Looking for a way to deploy your updates to all your clients? Try Updater!
    Monday, December 21, 2009 11:17 AM
  • Hi, Geert van Horrik
    Thanks for your reply.

    I already show codes of class C, that is the type of cC, list it below again:
    public class C : A
    {
          // Actual the type of cDR is SqlDataReader
          public override void FetchData(DbDataReader cDR)
          {
                 string strValue = (string)cDR.GetString(3);
          }
    }

    Database is SQLServer that is in my PC, local database. My program executes
    string strValue = (string)cDR.GetString(3);
    first, then executes
    cC.FetchData(cDR);
    That means these two lines code use same SqlConnect, SqlCommand and SqlDataReader to
    fetch all data. The only difference is my program executes first line code in a lib and execute
    second line code out of my lib , in a independent EXE file.


    Tuesday, December 22, 2009 1:09 AM
  • Hi, Geert van Horrik

    I did some work and find something is interesting. I connect database and run sql to get a SqlDataReader, these
    code list below

    string strConn = @"Data Source=BWL\SQLEXPRESS;Initial Catalog=ABC;Integrated Security=True";
    string strSQLText = "SELECT ID, Name, Age, Addr FROM Person";
    SqlConnection Connection = new SqlConnection(strConn);
    SqlCommand Command = new SqlCommand();

    Connection.Open();
    if (Connection.State != ConnectionState.Open)
          return false;

    Command.Connection = Connection;
    Command.CommandText = strSQLText;
    SqlDataReader DataReader = Command.ExecuteReader();

    Then I use different way to get all data:
    First way:
    long nID            = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = (long)DataReader.GetInt32(2);
    string strAddr   = (string)DataReader.GetString(3);


    dotTrace show all time consmued below:
    ...SqlDataReader.GetString... - 95,332ms - 2,000,000 calls
    ...SqlDataReader.GetInt32...  - 36,925ms - 1,000,000 calls
    ...SqlDataReader.GetInt64...  - 36,974ms - 1,000,000 calls


    Second way:
    string strAddr   = (string)DataReader.GetString(3);
    long nID           = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = (long)DataReader.GetInt32(2);

    dotTrace show all time consmued below:
    ...SqlDataReader.GetString... - 116,486ms - 2,000,000 calls
    ...SqlDataReader.GetInt32...  - 13,525ms   - 1,000,000 calls
    ...SqlDataReader.GetInt64...  - 13,010ms   - 1,000,000 calls


    That's why ?

    And, I think the reasonable result should like below
    The result I want
    ...SqlDataReader.GetString... - 30,000ms - 2,000,000 calls  // I guess
    ...SqlDataReader.GetInt32...  - 13,525ms - 1,000,000 calls
    ...SqlDataReader.GetInt64...  - 13,010ms - 1,000,000 calls


    By the way I traced into these source code of .Net Framework, and I am sure
    in different way the process is different too in .Net Framework. But to understand
    all source code of Framework is big work.So could you or anybody tell me how to
    get the result I want ?

    Thanks very much !!!












    First time I execute my code
    Tuesday, December 22, 2009 3:52 AM
  • OMG, Nobody help me ?
    Wednesday, December 23, 2009 12:57 AM
  • Hello UMLerChina,

    Tools like dotTrace, Visual Studio Profiler etc. just find the bottlenecks in our code, it's our programmer's task to improve the performance. And there are many ways to improve the performance of our code (please refer to the C# programming Guide for more information). Why do you think 30,000ms is a reasonable result if I may ask?
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Thursday, December 24, 2009 11:10 AM
  • Thanks Roahn Luo.

    I list my code again:
    Second way:
    string strAddr   = (string)DataReader.GetString(3);
    long nID           = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = (long)DataReader.GetInt32(2);

    I can change these code like this
    string strAddr   = (string)DataReader.GetString(3);
    long nID           = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = SetAgeValue(DataReader);

    protected long SetAgeValue(DbDataReader cDR)
    {
            return (long)
    cDR.GetInt32(2);
    }

    For this new case above, dotTrace shows inofrmation below
    ...SqlDataReader.GetString... - 106,000ms - 1,000,000 calls  // almost this much
    ...SqlDataReader.GetInt32...  - 13,525ms - 1,000,000 calls
    ...SqlDataReader.GetInt64...  - 13,010ms - 1,000,000 calls

    ... SetAgeValue ...                     - 14,000ms - 1,000,000 calls   // almost this much

    That why I think 30,000ms for 2,000,000 calls of GetString methond is reasonable
    Friday, December 25, 2009 12:40 AM
  • Thanks Roahn Luo.

    I list my code again:
    Second way:
    string strAddr   = (string)DataReader.GetString(3);
    long nID           = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = (long)DataReader.GetInt32(2);

    I can change these code like this
    string strAddr   = (string)DataReader.GetString(3);
    long nID           = (long)DataReader .GetInt64(0);
    string strName = (string)DataReader.GetString(1);
    long nAge         = SetAgeValue(DataReader);

    protected long SetAgeValue(DbDataReader cDR)
    {
            return (long)
    cDR.GetInt32(2);
    }

    For this new case above, dotTrace shows inofrmation below
    ...SqlDataReader.GetString... - 106,000ms - 1,000,000 calls  // almost this much
    ...SqlDataReader.GetInt32...  - 13,525ms - 1,000,000 calls
    ...SqlDataReader.GetInt64...  - 13,010ms - 1,000,000 calls

    ... SetAgeValue ...                     - 14,000ms - 1,000,000 calls   // almost this much

    That why I think 30,000ms for 2,000,000 calls of GetString methond is reasonable
    http://www.google.co.uk/search?hl=en&q=GetString(...)+of+class+SqlDataReader+C%23&btnG=Search&meta=&aq=f&oq=
    Just Be Humble Malange!
    Friday, December 25, 2009 7:31 PM