locked
LINQ with AsParallel() vs without vs straight SQL Reader RRS feed

  • Question

  • this was an interesting performance test. I thought the results were fascinating.

    the LINQ code with AsParallel()

                  watch.Reset();
                   watch.Start();
    
                   SqlQuery1 = "SELECT * FROM GroupTable Order By [Group ID]; ";
                   myCommand = new SqlCommand(SqlQuery1, MyMSsqlDatabase);
                   myReader = myCommand.ExecuteReader();
    
                   dataTable.Load(myReader, LoadOption.OverwriteChanges);
                   myReader.Close();
    
                   SqlQuery1 = "SELECT * FROM GroupTable2 Order By GroupId2; ";
                   myCommand = new SqlCommand(SqlQuery1, MyMSsqlDatabase);
                   myReader = myCommand.ExecuteReader();
    
                   dataTable2.Load(myReader, LoadOption.OverwriteChanges);
                   myReader.Close();
    
    
    
                   var query = from zTable1 in dataTable.AsEnumerable().AsParallel()
                               join zTable2 in dataTable2.AsEnumerable().AsParallel() on zTable1.Field<Int32>("Group ID") equals zTable2.Field<Int32>("GroupId2")
                               where zTable1.Field<Int32>("Group ID") > 200
                               select new
                               {
                                   zGroupid = zTable1.Field<Int32>("Group ID"),
                                   zGrpItem = zTable1.Field<String>("Group Item"),
                                   zDT = zTable1.Field<DateTime>("TimeStamp"),
    
                                   zGroupId2 = zTable2.Field<Int32>("GroupId2")                               
                               };
    
    
                   int ii = 0;
                   foreach (var item in query)
                   {
                       int GroupID = item.zGroupid;
                       String GroupItem = item.zGrpItem;
                       DateTime DT = item.zDT;
                       int GroupID2 = item.zGroupId2;
                       //Console.WriteLine("{0}=> Group ID ({1}) Group Item ({2}) TimeStamp({3}) GroupId2 ({4})", ii.ToString(), GroupID.ToString(), GroupItem, DT, GroupID2);
    
                       ii++;
                   }
    
                   watch.Stop();
    
                   Console.WriteLine("ii = {0}, Time = {1}", ii.ToString(), watch.ElapsedMilliseconds.ToString());

    the code with straight SQL Reader

                   watch.Reset();
                   watch.Start();
    
                   SqlQuery1 = "SELECT * FROM GroupTable g1 INNER JOIN GroupTable2 g2 ON g1.[Group ID] = g2.GroupID2 WHERE g2.GroupID2 > 200 Order By g1.[Group ID]; ";
                   SqlCommand myCommand2 = new SqlCommand(SqlQuery1, MyMSsqlDatabase);
                   SqlDataReader myReader2 = myCommand2.ExecuteReader();
                   
                   ii = 0;
                   while (myReader2.Read())
                   {
                       int GroupId = myReader2.GetInt32(myReader2.GetOrdinal("Group ID"));
                       String Groupitem = myReader2.GetString(myReader2.GetOrdinal("Group Item"));
                       DateTime DT = myReader2.GetDateTime(myReader2.GetOrdinal("TimeStamp"));
                       int GroupID2 = myReader2.GetInt32(myReader2.GetOrdinal("GroupId2"));
    
                       //Console.WriteLine("\t{0}\t{1}", GroupId, Groupitem);
                       ii++;
                   }
    
                   watch.Stop();
    
                   Console.WriteLine("ii = {0}, Time = {1}", ii.ToString(), watch.ElapsedMilliseconds.ToString());

    the stopwatch timings:

    ii = 115600, Time = 459 <= LINQ with AsParallel
    ii = 115600, Time = 533 <= SqlReader (serial processing)
    ii = 115600, Time = 177 <= AsParallel
    ii = 115600, Time = 353
    ii = 115600, Time = 219 <= AsParallel
    ii = 115600, Time = 515
    ii = 115600, Time = 200 <= AsParallel
    ii = 115600, Time = 352
    
    without the AsParallel()
    
    ii = 115600, Time = 455 <= LINQ
    ii = 115600, Time = 350 <= SqlReader (serial processing)
    ii = 115600, Time = 307 <= LINQ
    ii = 115600, Time = 512
    ii = 115600, Time = 445 <= LINQ
    ii = 115600, Time = 404
    ii = 115600, Time = 312 <= LINQ
    ii = 115600, Time = 347
    ii = 115600, Time = 302 <= LINQ
    ii = 115600, Time = 350
    

    question 1: is this a fair comparison? Meaning did I set the test up correctly?

    question 2: results about right? (reasonable)?   both database tables contain 10K records.

    Tuesday, August 14, 2012 3:48 PM

Answers