none
Performance Issue with Sql dataReader RRS feed

  • Question

  • Hi , 

    I am using MVP pattern for development of application . In data access layer , stored procedure is called , reader is looped over  and entities are populated. 

    I am facing a performance issue with one of stored procedures.  Pseudo code looks like 

    while(dr.read())

    {

    RebateItemDetail _rebateItem = new RebateItemDetail

    {

    property1 = reader.getstring(0),

    property2 = trader.getint32(1).....and go on 

    }

    list.add(rebateItem);

    }

    There are around 2000 records so it takes lot of time . 

    Strange thing is , there is another SP which returns almost 4000 + records but it gets executed within short time than first one only difference is 

    while(dr.read())

    {

    RebateItemDetail _rebateItem = new RebateItemDetail();

    _rebateItem.Property1 = reader.getstring(0);

    _rebateItem.Property2 = reader.getint32(1);

    .... and go on

    list.add(rebateItem);

    }

    Does it make any difference in performance because of different ways used to map reader values with entities ? 

    Any help will be really appreciated. 

    Thanks 

    Siddhesh 


    Siddhesh Sawant

    Friday, October 5, 2012 5:03 PM

Answers


  • Does it make any difference in performance because of different ways used to map reader values with entities ? 

    Siddhesh Sawant

    It shouldn't.  The two options are effectively the same, and should perform almost identically.

    The difference has to be in your stored procedure or elsewhere.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, October 5, 2012 6:26 PM

All replies


  • Does it make any difference in performance because of different ways used to map reader values with entities ? 

    Siddhesh Sawant

    It shouldn't.  The two options are effectively the same, and should perform almost identically.

    The difference has to be in your stored procedure or elsewhere.


    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, October 5, 2012 6:26 PM
  • Hi Read , 

    Thanks for answer. 

    But strange thing is when I debug both snippets of data reader , first one takes lesser time than second . And first dataset is more than 5000 + records and more columns than second . 

    Thanks 

    Siddhesh


    Siddhesh Sawant

    Sunday, October 7, 2012 10:14 AM
  • Hi Read ,

    You are correct . Issue is at stored procedure end . Sp has simple select with Left outer JOIN which returns 2000 + records.

    Stored procedure performance is improved if I write (NoLock) after a table in select statement .

    Strange things is , SP already has transaction isolation level as Read Uncommitted .

    Not sure why it does not apply and explicit No Lock works ?

    thanks

    Siddhesh


    Siddhesh Sawant

    Monday, October 8, 2012 10:07 AM