none
DataTable Select performance System.String vs System.Byte[] RRS feed

  • Question

  • I'm having problems with DataTable Select performance when the Column being Selected is a System.Byte[] type.

    My tests show it to take around 10 times longer than when the Select is on a type Int64 Column or 4 times longer than the Select is on a type String Column.

     

    Because of the type of data I have in this table I have to have it sorted by byte, previously  I could get away with it being type String. With this latest requirement I can't get away with the performance hit..

    Anyone got any ideas ?

     

    Thanks,

    John

     

    Example code is

     

    void Main()

    {

    Console.WriteLine("Loading Data...");

    //add bunch of data

    DateTime start;

    DataTable table = new DataTable();

    table.Columns.Add(new DataColumn("Col0", typeof(string)));

    table.Columns.Add(new DataColumn("Col1", typeof(Int64)));

    table.Columns.Add(new DataColumn("Col2", typeof(Byte[])));

    table.BeginLoadData();

    Random rnd = new Random();

    for (int i = 0; i < 500000; i++)

    {

    DataRow row = table.NewRow();

    row.BeginEdit();

    row["Col0"] = rnd.Next().ToString();

    row["Col1"] = rnd.Next();

    row["Col2"] = rnd.Next();

    table.Rows.Add(row);

    row.EndEdit();

    }

    table.EndLoadData();

    Console.WriteLine("starting test...");

    Console.WriteLine("String Select First");

    start = DateTime.Now;

    DataRow[] rows = table.Select(null ,"Col0 DESC");

    Console.WriteLine("String Select took " + (DateTime.Now - start).TotalMilliseconds + "ms");

    Console.WriteLine("Now int64 Select");

    start = DateTime.Now;

    rows = table.Select(null ,"Col1 DESC");

    Console.WriteLine("Int64 Select took " + (DateTime.Now - start).TotalMilliseconds + "ms");

    Console.WriteLine("Now Byte Select");

    start = DateTime.Now;

    rows = table.Select(null ,"Col2 DESC");

    Console.WriteLine("Byte Select took " + (DateTime.Now - start).TotalMilliseconds + "ms");

    }

     

    Results are

     

    Loading Data...
    starting test...
    String Select First
    String Select took 6406.25ms
    Now int64 Select
    Int64 Select took 2937.5ms
    Now Byte Select
    Byte Select took 27953.125ms

     

    Monday, July 16, 2007 4:38 AM

All replies

  • I believe sorting is not supported on BLOB types (binary, text) at all. If you need to sort on this field, you would need to convert to string representation of the binary content anyway.
    Monday, July 16, 2007 10:23 AM
    Moderator
  • Well one of the reasons behind this was to get something like 'bill's billet bills can't cannot cant co-op con coop' to be returned in the byte collating sequence and this happens when I set the column to Sytem.Byte[] - albeit rather slowly, When the column is String the " ' " and " - " are ignored for the purpose of collation.

    Wednesday, July 18, 2007 6:06 AM