Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version
Go to top

Performance comparisons LinQ to SQL,ADO,C#

, 25 May 2008
To compare existing approaches of accessing data (arrays,tables etc) with the new ones release with c#3.0 and LinQ.
source_file_and_sample_application.zip
Source File and Sample Application
Data
RetrieveUsingADO
RetrieveUsingADO
bin
Debug
RetrieveUsingADO.exe
RetrieveUsingADO.pdb
RetrieveUsingADO.vshost.exe
RetrieveUsingADO.vshost.exe.manifest
Release
RetrieveUsingADO.exe
RetrieveUsingADO.pdb
RetrieveUsingADO.vshost.exe
RetrieveUsingADO.vshost.exe.manifest
obj
Debug
RetrieveUsingADO.exe
RetrieveUsingADO.pdb
TempPE
Release
RetrieveUsingADO.exe
RetrieveUsingADO.pdb
TempPE
Properties
RetrieveUsingADO.suo
RetriveUsingLinQ
RetriveUsingLinQ
bin
Debug
RetriveUsingLinQ.exe
RetriveUsingLinQ.pdb
RetriveUsingLinQ.vshost.exe
RetriveUsingLinQ.vshost.exe.manifest
Release
RetriveUsingLinQ.exe
RetriveUsingLinQ.pdb
RetriveUsingLinQ.vshost.exe
RetriveUsingLinQ.vshost.exe.manifest
obj
Debug
Interop.Microsoft.Office.Core.dll
RetriveUsingLinQ.csproj.ResolveComReference.cache
RetriveUsingLinQ.exe
RetriveUsingLinQ.pdb
TempPE
RetriveUsingLinQfromSQL.designer.cs.dll
Release
Interop.Microsoft.Office.Core.dll
RetriveUsingLinQ.csproj.ResolveComReference.cache
RetriveUsingLinQ.exe
RetriveUsingLinQ.pdb
TempPE
RetriveUsingLinQfromSQL.designer.cs.dll
Properties
Settings.settings
RetriveUsingLinQ.suo
RetriveUsingLinQfromSQL.dbml
RetriveUsingLinQfromSQL.dbml.layout
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml.Linq;



namespace RetriveUsingLinQ
{
    class Program
    {
        static void Main(string[] args)
        {
            displaySettings();
            Console.WriteLine("Generating XML files with performance data");
            Console.WriteLine("Executing GatherDataLinQRead()"); 
            GatherDataLinQRead();
            Console.WriteLine("Executing GatherDataLinQInserting()"); 
            GatherDataLinQInserting();
            Console.WriteLine("Executing GatherDataLinQInsertingDirect()"); 
            GatherDataLinQInsertingDirect();
            Console.WriteLine("Executing GatherDataLinQXMLRead()"); 
            GatherDataLinQXMLRead();
            Console.WriteLine("GatherDataLinQObjects1()"); 
            GatherDataLinQObjects1();
            Console.WriteLine("GatherDatacsharpObjects1"); 
            GatherDatacsharpObjects1();
            Console.WriteLine("GatherDataLinQObjects2"); 
            GatherDataLinQObjects2();
            Console.WriteLine("GatherDataLinQQueryDataset");
            GatherDataLinQQueryDataset();
            Console.WriteLine("Files created in C:\\ LinQRead.xml LinQInsert.xml LinQInsertDirect.xml LinQXmlRead.xml LinQObjects1.xml  csharpObjects1 LinQObjects2.xml  ");
            Console.ReadLine();
        } // end of main()

        # region GatherData

        public static void GatherDataLinQRead()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQRead");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQReading();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQRead.xml");
        } //end of GatherDataLinQRead()
        public static void GatherDataLinQInserting()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQInsert");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQInserting();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQInsert.xml");
        } //end of GatherDataLinQInserting()
        public static void GatherDataLinQInsertingDirect()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQInsertDirect");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQInsertingDirect();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQInsertDirect.xml");
        } //end of GatherDataLinQInserting()
        public static void GatherDataLinQXMLRead()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQXmlRead");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQXmlRead();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQXmlRead.xml");
        } //end of GatherDataLinQXMLRead()
        public static void GatherDataLinQObjects1()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQObjects1");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQObjects1();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQObjects1.xml");
        } //end of GatherDataLinQObjects1
        public static void GatherDatacsharpObjects1()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("csharpObjects1");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = csharpObjects1();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\csharpObjects1.xml");
        } //end of GatherDatacsharpObjects1
        public static void GatherDataLinQObjects2()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQObjects2");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQObjects2();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\LinQObjects2.xml");
        } //end of GatherDataLinQObjects2
        public static void GatherDataLinQQueryDataset()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("LinQQueryDataset");
            dt.Columns.Add(new DataColumn("Pass", typeof(Int32)));
            dt.Columns.Add(new DataColumn("Value", typeof(Int64)));
            dataSet.Tables.Add(dt);

            DataRow dr;

            for (int i = 0; i <= 500; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = LinQQueryDataset();
                dt.Rows.Add(dr);
                Console.WriteLine("Pass {0} Over", i);
            } //end of for
            dataSet.WriteXml("c:\\LinQQueryDataset.xml");
        } //end of GatherDataLinQQueryDataset

        # endregion GatherData

        # region ActualFunctions
        /// <summary>
        /// Gets records from the customer table using linq and then adds all the values from CustomerID
        /// </summary>
        /// <returns></returns>
        private static long LinQReading()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            RetriveUsingLinQfromSQLDataContext db = new RetriveUsingLinQfromSQLDataContext();
            var retrieveRecords = from customerTable in db.Customers
                                  select customerTable;
            double i = 0; ;
            foreach (Customer singleRecord in retrieveRecords)
            {
                i = i + singleRecord.CustomerID;
            }
            db.Dispose();
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));

        } // end of LinQReading()

        /// <summary>
        /// Inserts one row into Cusomer table using storedprocedure via linq
        /// </summary>
        /// <returns></returns>
        private static long LinQInserting()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            RetriveUsingLinQfromSQLDataContext db = new RetriveUsingLinQfromSQLDataContext();
            var result = db.uspInsertIntoCustomer();
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        } // end of LinQInserting()

        /// <summary>
        /// Inserts into customers using Linq , no stored procedure -- direct sql statemet 
        /// </summary>
        /// <returns></returns>
        private static long LinQInsertingDirect()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            RetriveUsingLinQfromSQLDataContext db = new RetriveUsingLinQfromSQLDataContext();
            db.ExecuteCommand("insert into sales.customer(TerritoryID , CustomerType) values(10,'I')");
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        } // end of LinQInsertingDirect()        
        /// <summary>
        /// Reads all lines fom an xml file and then created second subset using linq for a <<condition>> and then alls the first colum of the subset.
        /// </summary>
        /// <returns></returns>
        private static long LinQXmlRead()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            XDocument xmlReferance = new XDocument();
            xmlReferance = XDocument.Load("c:\\LinQInsertDirect.xml", LoadOptions.PreserveWhitespace);
            var linQresult = from xmlRecords in xmlReferance.Descendants("LinQInsertDirect")
                             select new
                             {
                                 NewPass =   Convert.ToInt32(xmlRecords.Element("Pass").Value.Trim()),
                                 NewValue = xmlRecords.Element("Value").Value
                             };
            var linQresultsubSet = from subResults in linQresult
                                   where subResults.NewPass >= 250
                             select new
                             {
                                 FinalPass = subResults.NewPass ,
                                 FinalValue = subResults.NewValue 
                             };

            int i = 0;
            foreach (var rec in linQresultsubSet)
            {
                i = i + rec.FinalPass; 
            }
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        } //end of LinQXmlRead()

        /// <summary>
        /// Reads values from an array using linq. Creates a squared subset of even numbers and then displays sum of the subset
        /// </summary>
        /// <returns></returns>
        private static long LinQObjects1()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            int[] nums = new int[1000];
            for (int i = 0; i < 1000; i++) { nums[i] = i + 1; }

            var getSquaresLessthen500 = from temp in nums
                         where temp == newFunction(temp)
                         select temp*temp;

            double resultaddition = getSquaresLessthen500.Sum();
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
             
        }

        /// <summary>
        /// This function needs no explaination !!
        /// </summary>
        /// <returns></returns>
        private static long csharpObjects1()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            int[] nums = new int[1000];
            for (int i = 0; i < 1000; i++) { nums[i] = i + 1; }

            int[] numsEven = new int[500];
            int evencounter = 0;
            double resultaddition = 0;

            for (int j = 0; j < 1000; j++)
            {
                if (nums[j] % 2 == 0)
                {
                    numsEven[evencounter] = nums[j] * nums[j];
                    resultaddition = resultaddition + numsEven[evencounter];
                    evencounter = evencounter + 1;
                }


            }
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        }

        /// <summary>
        /// Intention same as LinQObjects1 function. but using c#3.0 aggerigates
        /// </summary>
        /// <returns></returns>
        private static long LinQObjects2()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            var nums = new double[1000];
            for (int i = 0; i < 1000; i++) { nums[i] = i + 1; }
            double sum = nums.Aggregate(delegate(double Cursum, double curNum) { if (curNum % 2 == 0) { return (Cursum + (curNum * curNum)); } else { return (Cursum + 0); } });
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));

        }

        /// <summary>
        /// This function will query the dataset using LinQ styled queries
        /// </summary>
        /// <returns></returns>
        private static long LinQQueryDataset()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            RetriveUsingLinQfromSQLDataContext db = new RetriveUsingLinQfromSQLDataContext();
            var retrieveRecords = from customerTable in db.Customers
                                  select new
                                  {
                                      customerTable.CustomerID ,
                                      customerTable.TerritoryID ,
                                      customerTable.AccountNumber ,
                                      customerTable.CustomerType  ,
                                      customerTable.rowguid ,
                                      customerTable.ModifiedDate
                                  };


            DataSet ds  = new DataSet();
            DataTable table = new DataTable() ;  
            table.Columns.Add("CustomerID", typeof(int));
            table.Columns.Add("TerritoryID", typeof(int));
            table.Columns.Add("AccountNumber", typeof(string));
            table.Columns.Add("CustomerType", typeof(char));
            table.Columns.Add("rowguid", typeof(Guid));
            table.Columns.Add("ModifiedDate", typeof(DateTime));

            foreach (var tempRec in retrieveRecords)
            {
                table.LoadDataRow(new Object[] { tempRec.CustomerID, tempRec.TerritoryID, tempRec.AccountNumber, tempRec.CustomerType, tempRec.rowguid, tempRec.ModifiedDate}, true);
            
            }
            ds.Tables.Add(table);

            var  subset = from tempSet in table.AsEnumerable()
                          where tempSet.Field<int>("CustomerID") > 250
                          select new
                          {
                              CustomerID = tempSet.Field<int>("CustomerID") ,
                              TerritoryID = tempSet.Field<int>("TerritoryID"),
                              AccountNumber = tempSet.Field<string>("AccountNumber"),
                              CustomerType = tempSet.Field<char>("CustomerType"),
                              rowguid = tempSet.Field<Guid>("rowguid"),
                              ModifiedDate = tempSet.Field<DateTime>("ModifiedDate")

                          };

            double summ = 0;
            foreach (var tempRow in subset)
            {
                summ = summ + Convert.ToDouble(tempRow.CustomerID);
            }

            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));

        } // end of LinQQueryDataset()


        # endregion ActualFunctions

        # region Helpers
        private static int newFunction(int rec1)
        {
            if (rec1 % 2 == 0)
            {
                return (rec1);
            }
            else
            {
                return (rec1+1);
            }
        } // end of newFunction
        private static void displaySettings()
        {
            if (Stopwatch.IsHighResolution)
            {
                Console.WriteLine("Operations timed using the system's high-resolution performance counter.");
            }
            else
            {
                Console.WriteLine("Operations timed using the DateTime class.");
            }

            long frequency = Stopwatch.Frequency;
            Console.WriteLine("  Timer frequency in ticks per second = {0}",
                frequency);
            long nanosecPerTick = (1000L * 1000L * 1000L) / frequency;
            Console.WriteLine("  Timer is accurate within {0} nanoseconds",
                nanosecPerTick);
        }

        # endregion Helpers


    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

KshitijPandey

Unknown
No Biography provided

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 26 May 2008
Article Copyright 2008 by KshitijPandey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid