Best way to populate List from database in C#

Question

I have 3 classes inherited from same class like

class S1:X{}
class S2:X{}
class S3:X{}

I need to write method to populate List<X> from Sql database. Currently, i am using SqlDataReader to populate. Each class has about 35 properties and db result also about 50K rows. Population is takes too long time. I am curious about the best way to populate large data into List. I am not able to use 3rd party packages because of corporation rules. Is there faster way than SqlDataReader?

Edit:

Modified code sample is below to describe what i am trying. Firstly, may be i should explain some points. SmartSqlReader is inherited from SqlDataReader, AutoMap method is mapper used Reflection.

using(SmartSqlReader reader = db.ExecuteReader(sp)) {
  while (reader.Read()) {
    bool isFlag1 = reader.GetBool("XX_TO_SEND");
    bool isFlag2 = reader.GetBool("YY_TO_SEND");
    bool isFlag3 = reader.GetBool("ZZ_TO_SEND");

    if (!isFlag1 && !isFlag2 && !isFlag3) {
      continue;
    }

    X x = new X() {
        RecordId = reader.GetInt64("RECORD_ID"),
        PropCxxx = reader.GetInt64("CXXX"),
        PropCxxt = reader.GetInt32("CXXT"),
        PropCxxsn = reader.GetString("CXXSN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXSN"),
        PropCxxn = RemoveDiacritics(reader.GetString("CXXSN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXN").ToLower()),
        PropCxxmn = reader.GetString("CXXSN2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXSN2"),
        PropCxxs = reader.GetString("CXXS").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CXXS"),
        Language = reader.GetString("LANGUAGE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("LANGUAGE"),
        PropSxxx = reader.GetString("SXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("SXXX"),
        MobilePhone1 = reader.GetString("MobilePhone1").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("MobilePhone1"),
        MobilePhone2 = reader.GetString("MobilePhone2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("MobilePhone2"),
        Email1 = reader.GetString("EMAIL1").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("EMAIL1"),
        Email2 = reader.GetString("EMAIL2").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("EMAIL2"),
        Profile = reader.GetString("PROFILE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PROFILE"),
        IsPersonnel = reader.GetString("PROFILE") == "XX" ? true : false,
        IsPrivateBn = reader.GetString("IsOB").IsNullOrEmptyOrFullSpace() ? false : reader.GetBool("IsOB"),
        VIP = reader.GetInt32("VIP_FLAG"),
        Gender = reader.GetString("GENDER").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("GENDER"),
        BusinessLine = reader.GetString("BUSINESSLINE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BUSINESSLINE"),

        WorkPhone = reader.GetString("WORK_PHONE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("WORK_PHONE"),
        HomePhone = reader.GetString("HOME_PHONE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("HOME_PHONE"),
        CompanyName = reader.GetString("COMPANY_NAME").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("COMPANY_NAME"),
        BranchName = reader.GetString("BRANCH_NAME").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BRANCH_NAME"),
        PfNxxx = reader.GetString("PFNXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PFNXXX"),
        Rgxxx = reader.GetString("RGXXX").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("RGXXX"),
        PCBN = reader.GetString("PCBN").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("PCBN"),
        BPH = reader.GetString("BPH").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("BPH"),

        TranValue = reader.GetString("TRAN_VALUE"),
        TranReferenceId = reader.GetString("TRAN_REFERENCE_ID"),
        TranReferenceDate = reader.GetDateTime("TRAN_REFERENCE_DATE"),
        Amount = reader.GetDecimal("AMOUNT"),
        ...
        DynamicFields = new DynamicFields {
          PropCxxx = reader.GetInt64("CXXX"),
            FIELD1 = reader.GetString("DYNAMIC_FIELD1"),
            FIELD2 = reader.GetString("DYNAMIC_FIELD2"),
            FIELD3 = reader.GetString("DYNAMIC_FIELD3"),
            FIELD4 = reader.GetString("DYNAMIC_FIELD4"),
            FIELD5 = reader.GetString("DYNAMIC_FIELD5"),
            FIELD6 = reader.GetString("DYNAMIC_FIELD6"),
            FIELD7 = reader.GetString("DYNAMIC_FIELD7"),
            FIELD8 = reader.GetString("DYNAMIC_FIELD8"),
            FIELD9 = reader.GetString("DYNAMIC_FIELD9"),
            FIELD10 = reader.GetString("DYNAMIC_FIELD10"),
            FIELD11 = reader.GetString("DYNAMIC_FIELD11"),
            FIELD12 = reader.GetString("DYNAMIC_FIELD12"),
            FIELD13 = reader.GetString("DYNAMIC_FIELD13"),
            FIELD14 = reader.GetString("DYNAMIC_FIELD14"),
            FIELD15 = reader.GetString("DYNAMIC_FIELD15")
        },
        CampaignCodeOrLink = reader.GetString("CAMPAIGN_CODE").IsNullOrEmptyOrFullSpace() ? string.Empty : reader.GetString("CAMPAIGN_CODE"),
        ListId = reader.GetInt64("LIST_ID")
    };

    x.ChannelType = isFlag1 ? Enums.Channel.C1 : isFlag2 ? Enums.Channel.C2 : Enums.Channel.C3;

    if (x.ChannelType == Enums.Channel.C1) {
      S1 s1 = CommonUtils.AutoMap <S1> (x);
      s1.S1Prop = reader.GetString("S1Prop");
      xList.Add(s1);
    }
    else if (x.ChannelType == Enums.Channel.C2) {
      S1 s2 = CommonUtils.AutoMap <S2> (x);
      s2.S2Prop = reader.GetString("S2Prop");
      xList.Add(s2);
    } else {
      S3 s3 = CommonUtils.AutoMap <S3> (x);
      s3.S3Prop = reader.GetString("S3Prop");
      xList.Add(s3);
    }

  }
}

Second Edition:

I just changed object initialization from X x = new X(){...} to

X x;
if(isFlag1)
{
    x=new S1();
}

and so on. After that, 80K rows took approximately 10s. It's amazing. In conclusion, when i used CommonUtils.AutoMap method process took ~60m also when i used the second method it decreased to ~10s. This surprised me a lot.

Please show the code you have. Noone can help you improve performance without knowing what you're doing that might be slow. The `SqlDataReader` class itsself probably won't be an issue — Dominik, Mar 18 '21 at 22:57
Nope. But in all likelihood, the problem is not with the reader, it's with how you map column to properties. If you use something like _Dapper_, you get a very efficient mapper for free. It's just a NuGet package; not really a full on "3rd party software". Otherwise, write your own. Hand the mapper factory your DataReader and the type of your `AnyObject`. Get column names, types and ordinal from the schema Dataset buried in the reader. Get property setters via reflection. Create a mapping between column and property. Use that mapping to convert each row to an object. — Flydog57, Mar 18 '21 at 23:02
Fetching big number of data into memory usually would not be fast, so please define what is "too long time" for you. Without code it will be pretty hard to guess, but I would recommend to try at least to prealocate `List` of correct size it should improve performance but not drastically. — Guru Stron, Mar 18 '21 at 23:05
@aydinmehmet did you benchmark already what part of the code shown takes the most time? `CommonUtils.AutoMap` and `SmartSqlReader` is a blackbox to us. — Dominik, Mar 18 '21 at 23:28
@GuruStron it takes about 25s for 400 rows and over 50s for 1000 rows. — aydinmehmet, Mar 18 '21 at 23:30
@Dominik not just yet. but when i read logs, i saw that approximately 10K rows mapped in 5s whereas now takes too much. may be good choice to logs automap run time. thanks — aydinmehmet, Mar 18 '21 at 23:38
"Best way" is the breeding ground for all subjective answers. Avoid using it in the title. — Omar Abdel Bari, Mar 19 '21 at 00:48
@aydinmehmet JIC where is the database located compared to where the code is executed? — Guru Stron, Mar 19 '21 at 13:19
Take a look at your code. You have things like: `RecordId = reader.GetInt64("RECORD_ID")`. That looks something up by string. You do that 35 times per row, and then all of that about 50k times. If you were to clone your project and use Dapper instead (as a quick test), you will like to see a speed up of an order of magnitude (or two - really). Looking for data by column name for every column and row is extremely slow. If you can't use Dapper, you need to write your own Mapper (see my previous comment). I've done this a few times, but unless I can find some code around, I don't have time. — Flydog57, Mar 19 '21 at 15:22

score 0 · Accepted Answer · answered Mar 20 '21 at 00:20

I just changed object initialization method, so i removed CommonUtils.AutoMap that map objects use reflection. After all, 80K rows processed in ~10s instead of ~60m. Here is final code.

using(SmartSqlReader reader = db.ExecuteReader(sp)) {
  while (reader.Read()) {
    bool isFlag1 = reader.GetBool("XX_TO_SEND");
    bool isFlag2 = reader.GetBool("YY_TO_SEND");
    bool isFlag3 = reader.GetBool("ZZ_TO_SEND");

    if (!isFlag1 && !isFlag2 && !isFlag3) {
      continue;
    }

    X x;
    if (isFlag1) {
      var s = new S1();
      s.S1Prop = reader.GetString("S1Prop");
      x = s;
    } else if (isFlag2) {
      var s = new S2();
      s.S2Prop = reader.GetString("S2Prop");
      x = s;
    } else {
      var s = new S3();
      s.S3Prop = reader.GetString("S3Prop");
      x = s;
    }

    x.RecordId = reader.GetInt64("RECORD_ID"),
    x.PropCxxx = reader.GetInt64("CXXX"),
    x.PropCxxt = reader.GetInt32("CXXT"),
    ...
    x.DynamicFields = new DynamicFields {
          FIELD1 = reader.GetString("DYNAMIC_FIELD1"),
          FIELD2 = reader.GetString("DYNAMIC_FIELD2"),
          FIELD3 = reader.GetString("DYNAMIC_FIELD3"),
          FIELD4 = reader.GetString("DYNAMIC_FIELD4"),
          FIELD5 = reader.GetString("DYNAMIC_FIELD5"),
          FIELD6 = reader.GetString("DYNAMIC_FIELD6"),
          FIELD7 = reader.GetString("DYNAMIC_FIELD7"),
          FIELD8 = reader.GetString("DYNAMIC_FIELD8"),
          FIELD9 = reader.GetString("DYNAMIC_FIELD9"),
          FIELD10 = reader.GetString("DYNAMIC_FIELD10"),
          FIELD11 = reader.GetString("DYNAMIC_FIELD11"),
          FIELD12 = reader.GetString("DYNAMIC_FIELD12"),
          FIELD13 = reader.GetString("DYNAMIC_FIELD13"),
          FIELD14 = reader.GetString("DYNAMIC_FIELD14"),
          FIELD15 = reader.GetString("DYNAMIC_FIELD15")
      },
  };
  xList.Add(x);
}
}

Best way to populate List from database in C#

1 Answers1