What is the fastest way to get data

Question

User-1971168174 posted

Hi Support,

I am using following code to fetch data,please help me with the code if there is more efficient and fastest .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ServiceProcess;
using System.Net;
using System.Net.NetworkInformation;
using System.Net.Sockets;
using System.Net.Http;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {


        static void Main(string[] args)
        {


          DataTable dt= GetDataTableFast("select  * from table");



        }

        public static DataTable GetDataTableFast(string query)
        {
            
            string strcon = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

            SqlConnection con = new SqlConnection(strcon);

            con.Open();
            
            DateTime start = DateTime.Now;
            DataTable dt = new DataTable();
            new SqlDataAdapter(query, con).Fill(dt);
            TimeSpan ts = DateTime.Now.Subtract(start);
            System.Diagnostics.Trace.Write("Time Elapsed in Adapter: " + ts.TotalMilliseconds);
            con.Close();
            Console.ReadLine();
            return dt;
            
        }






    }
}

Answer 1

User-821857111 posted

The fastest way is to use a Reader rather than a DataSet, and to only select the columns of data that you need.

Answer 2

User1120430333 posted

You don't use a datatable. 

https://dzone.com/articles/reasons-move-datatables

http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

Answer 3

User-857013053 posted

writing LINQ to Entities to make sure they run quickly and effectively.  When working with a large collection of data and binding it with a database  you use LINQ so its getting data faster than SQL. LINQ is less time consuming.

Answer 4

User1120430333 posted

[e-mail]

writing LINQ to Entities to make sure they run quickly and effectively.  When working with a large collection of data and binding it with a database  you use LINQ so its getting data faster than SQL. LINQ is less time consuming.

Linq is not faster than T-SQL. A Linq-to-Entities query generates T-SQL to pull data from the database. A datareader in a  while loop reads the results of the executed T-SQL, creates a EF model object, populates the EF model object from the datareader, loads the model object into a List<T> and returns the collection of objects. Speed is lost in the EF object materialization in using Linq-2-Entities as compared to the speed in  just running T-SQL for the query at the database level.    

BTW, the fastest means to read data from a database using a read-only forward cursor is by using a datareader

Answer 5

User-821857111 posted

writing LINQ to Entities to make sure they run quickly and effectively.  When working with a large collection of data and binding it with a database  you use LINQ so its getting data faster than SQL. LINQ is less time consuming.

This is incorrect. LINQ to Entities/SQL is a (pretty heavy) layer on top of ADO.NET. It does not get data faster.