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

Performance comparisons LinQ to SQL,ADO,C#

By , 25 May 2008
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.Generic;
//using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml;
using System.Xml.XPath;

namespace RetrieveUsingADO
{
    class Program
    {
        static void Main(string[] args)
        {
            displaySettings();
            Console.WriteLine("Executing GatherDataADORead");
            GatherDataADORead();
            Console.WriteLine("Executing GatherDataADOInsert");
            GatherDataADOInsert();
            Console.WriteLine("Executing GatherDataADOInsertDirect");
            GatherDataADOInsertDirect();
            Console.WriteLine("Executing GatherDataXmlReading");
            GatherDataXmlReading();
            Console.WriteLine("Executing GatherDataADODataSetQuery");
            GatherDataADODataSetQuery();
            Console.WriteLine("Files created ");

        } // end of main

        # region GatherData
        public static void GatherDataADOInsert()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("ADOInsert");
            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] = ADOInserting();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\ADOInsert.xml");



        }// end of GatherDataADOInsert
        public static void GatherDataADORead()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("ADORead");
            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] = ADOReading();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\ADORead.xml");
        } //end of GatherDataADORead()
        public static void GatherDataADOInsertDirect()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("ADOInsertDirect");
            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] = ADOInsertingDirect();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\ADOInsertDirect.xml");
        } //end of GatherDataADOInsertDirect()
        public static void GatherDataXmlReading()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("csharpxmlReading");
            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] = xmlReading();
                dt.Rows.Add(dr);
            } //end of for
            dataSet.WriteXml("c:\\csharpxmlReading.xml");
        } //end of GatherDataXmlReading()
        public static void GatherDataADODataSetQuery()
        {
            DataSet dataSet = new DataSet();
            DataTable dt = new DataTable("ADODataSetQuery");
            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] = ADODataSetQuery();
                dt.Rows.Add(dr);
                Console.WriteLine(" Passs {0} Over ", i); 
            } //end of for
            dataSet.WriteXml("c:\\ADODataSetQuery.xml");
        } //end of GatherDataXmlReading()

        # endregion GatherData

        # region ActualFunctions
        /// <summary>
        /// This function reads from customer table using ado and then sums up the values of the first column.
        /// </summary>
        /// <returns></returns>
        private static long ADOReading()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();
            
            SqlConnection Conn;
            String connectionstring;
            connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
                                "Database=AdventureWorks;" +
                                "user id=sa;" +
                                "password=sa;" +
                                "Connection timeout = 5 ;" +
                                "Pooling='false';";
            Conn = new SqlConnection(connectionstring);
            try
            {
                Conn.Open();
            }
            catch (Exception ex)
            {
                // Connection failed
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                return(-1);
            }
            SqlCommand cmd = new SqlCommand("select * from sales.customer", Conn);
            SqlDataReader dr = cmd.ExecuteReader();
            double i = 0;
            while (dr.Read())
            {
                i = i + Convert.ToDouble(dr[0]);
            }
            dr.Close();
            cmd.Dispose();
            Conn.Close();
            //Console.WriteLine(i.ToString());
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100))   ;
        }//end ADO Reading
        /// <summary>
        /// This function inserts using ADO into customers table using a stored procedure
        /// </summary>
        /// <returns></returns>
        private static long ADOInserting()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            SqlConnection Conn;
            String connectionstring;
            connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
                                "Database=AdventureWorks;" +
                                "user id=sa;" +
                                "password=sa;" +
                                "Connection timeout = 5 ;" +
                                "Pooling='false';";
            Conn = new SqlConnection(connectionstring);
            try
            {
                Conn.Open();
            }
            catch (Exception ex)
            {
                // Connection failed
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                return (-1);
            }
            SqlCommand cmd = new SqlCommand("Sales.uspInsertIntoCustomer", Conn);
            cmd.CommandType = CommandType.StoredProcedure ;
            int runResult = cmd.ExecuteNonQuery();
            cmd.Dispose();
            Conn.Close();
            //Console.WriteLine(i.ToString());
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        
        
        }//end ADO Inserting
        /// <summary>
        /// This function inserts into customer table without stored procedure using ADO
        /// </summary>
        /// <returns></returns>
        private static long ADOInsertingDirect()
        {
            
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            SqlConnection Conn;
            String connectionstring;
            connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
                                "Database=AdventureWorks;" +
                                "user id=sa;" +
                                "password=sa;" +
                                "Connection timeout = 5 ;" +
                                "Pooling='false';";
            Conn = new SqlConnection(connectionstring);
            try
            {
                Conn.Open();
            }
            catch (Exception ex)
            {
                // Connection failed
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                return (-1);
            }
            //INSERT into [Sales].[Customer] (TerritoryID , CustomerType)values(10,'I')
            SqlCommand cmd = new SqlCommand("insert into sales.customer(TerritoryID , CustomerType) values(10,'I')", Conn);
            cmd.CommandType = CommandType.Text; 
            int runResult = cmd.ExecuteNonQuery();
            cmd.Dispose();
            Conn.Close();
            //Console.WriteLine(i.ToString());
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));


        }//end ADOInsertingDirect
        /// <summary>
        /// This function loads  a sample xml file and then appies a condition(xpath) to create a subset. It then sums the  values in first column of the subset
        /// </summary>
        /// <returns></returns>
        private static long xmlReading()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            XPathNavigator nav;
            XPathDocument docNav;
            XPathNodeIterator NodeIter;
            String strExpression;

            // Open the XML.
            docNav = new XPathDocument(@"c:\LinQInsertDirect.xml");
            // Create a navigator to query with XPath.
            nav = docNav.CreateNavigator();
            //strExpression = "/bookstore/book/title[../price>10.00]";
            strExpression = "/NewDataSet/LinQInsertDirect/Pass[../Pass>250]";
            // Select the node and place the results in an iterator.
            NodeIter = nav.Select(strExpression);
            int i = 0 ;
            while (NodeIter.MoveNext())
            {
                 i  =  i + NodeIter.Current.ValueAsInt;
            };
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        }//end xmlReading


        /// <summary>
        /// This function reads from customer table using ado into s dataset then sums up the values of the first column.
        /// </summary>
        /// <returns></returns>
        private static long ADODataSetQuery()
        {
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
            stopWatch.Start();

            SqlConnection Conn;
            String connectionstring;
            connectionstring = @"Server=B4-2F-333-F2Z9\SQL2005;" +
                                "Database=AdventureWorks;" +
                                "user id=sa;" +
                                "password=sa;" +
                                "Connection timeout = 5 ;" +
                                "Pooling='false';";

            SqlDataAdapter dAdapter = new SqlDataAdapter("select * from sales.customer", connectionstring);
            dAdapter.TableMappings.Add("sales.customer", "customer");

            DataSet ds = new DataSet();
            dAdapter.Fill(ds);

            DataRow[] customerDataRow = ds.Tables[0].Select( "CustomerID > 250");
            double summ = 0 ;
            foreach( DataRow tempRow in customerDataRow )
            {
                summ = summ + Convert.ToDouble(tempRow[0]);
            }
            stopWatch.Stop();
            return ((long)(stopWatch.ElapsedTicks / 100));
        }//end ADO Reading

        # endregion ActualFunctions

        # region Helpers
        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 use 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)

About the Author

KshitijPandey

Unknown
No Biography provided

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