SQL data reader reading data performance test，readingperformance

SQL data reader reading data performance test，readingperformance

/*Author: Jiangong SUN*/

As I've manipulated a lot of data using SQL data reader in recent project. And people says it's not good to access the data by column name.

So I've made an performance test in reading data from SQL data reader.

Firstly, I've created a table with different data types, like int, varchar, date time etc.

CREATE TABLE UserInformation(Id BIGINT, FirstName NVARCHAR(255), LastName NVARCHAR(255), ValidDate DATETIME, Identification UNIQUEIDENTIFIER)

Then, I've filled the table with 9024728 lines data. 

Why is it the exact number? It's because the sql server management studio crashes after 9024728 lines' insertion. :-)

Then, I'll use 3 methods to read the 9 millions lines data.

Method 1: Get data by column index

public void DataReaderGetDataByColumnIndex()        {            using (_dbConnection)            {                var sqlCommand = new SqlCommand(_commandText, _dbConnection);                _dbConnection.Open();                SqlDataReader reader = sqlCommand.ExecuteReader();                var user = new UserInformationEntity();                _GetByIndexTime.Start();                while (reader.Read())                {                    user.Id = reader.GetInt64(0);                    user.FirstName = reader.GetString(1);                    user.LastName = reader.GetString(2);                    user.ValidDate = reader.GetDateTime(3);                    user.Identification = reader.GetGuid(4);                }                _GetByIndexTime.Stop();                Console.WriteLine(string.Format("GetByIndexTime total time:{0}", _GetByIndexTime.Elapsed));                _dbConnection.Close();            }        }

Method 2: Get data by column name

public void DataReaderGetDataByColumnName()        {            using (_dbConnection)            {                var sqlCommand = new SqlCommand(_commandText, _dbConnection);                _dbConnection.Open();                SqlDataReader reader = sqlCommand.ExecuteReader();                var user = new UserInformationEntity();                _GetByNameTime.Start();                while (reader.Read())                {                    user.Id = Convert.ToInt64(reader["Id"]);                    user.FirstName = reader["FirstName"].ToString();                    user.LastName = reader["LastName"].ToString();                    user.ValidDate = Convert.ToDateTime(reader["ValidDate"]);                    user.Identification = new Guid(reader["Identification"].ToString());                }                _GetByNameTime.Stop();                Console.WriteLine(string.Format("GetByNameTime total time:{0}", _GetByNameTime.Elapsed));                _dbConnection.Close();            }        }

Method 3: Get column ordinal by column name, then Get data by column ordinal

public void DataReaderGetColumnIndexByColumnNameThenGetData()        {            using (_dbConnection)            {                var sqlCommand = new SqlCommand(_commandText, _dbConnection);                _dbConnection.Open();                SqlDataReader reader = sqlCommand.ExecuteReader();                var user = new UserInformationEntity();                var id = reader.GetOrdinal("Id");                var firstName = reader.GetOrdinal("FirstName");                var lastName = reader.GetOrdinal("LastName");                var validDate = reader.GetOrdinal("ValidDate");                var identification = reader.GetOrdinal("Identification");                _GetByNameThenIndexTime.Start();                while (reader.Read())                {                    user.Id = reader.GetInt64(id);                    user.FirstName = reader.GetString(firstName);                    user.LastName = reader.GetString(lastName);                    user.ValidDate = reader.GetDateTime(validDate);                    user.Identification = reader.GetGuid(identification);                }                _GetByNameThenIndexTime.Stop();                Console.WriteLine(string.Format("GetByNameThenIndexTime total time:{0}", _GetByNameThenIndexTime.Elapsed));                _dbConnection.Close();            }        }

When I run the program to get the execution time:

You can see that Method1 and Method3 has almost the same result, and Method2 are about 3 times longer.

So the prefered approach will be the third.

Enjoy coding!

SqlDataReader怎使用來顯示資料!!--aspnet

Lesson 04: Reading Data with the SqlDataReader

使用SqlDataReader讀取資料

This lesson explains how to read data with a SqlDataReader object. Here are the objectives of this lesson:

本課解釋了如何使用SqlDataReader對象讀取資料。以下是本課的目標：

Learn what a SqlDataReader is used for.
Know how to read data using a SqlDataReader.
Understand the need to close a SqlDataReader.
學習SqlDataReader的用法
瞭解如何使用SqlDataReader讀取資料
瞭解如何關閉SqlDataReader的必要性
Introduction

介紹

A SqlDataReader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. SqlDataReaders are often described as fast-forward firehose-like streams of data.

SqlDataReader是對於大多數有效情況下讀取資料的好的方式。你不能使用它來寫入資料。SqlDataReaders通常作為快速的只向前讀的資料流。

You can read from SqlDataReader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.

你能夠以只向前的順序方式從SqlDataReader對象中進行讀取。只要你已經讀取了某些資料，你必須儲存它們，因為你將不能夠返回並再一次讀取它。

The forward only design of the SqlDataReader is what enables it to be fast. It doesn't have overhead associated with traversing the data or writing it back to the data source. Therefore, if your only requirement for a group of data is for reading one time and you want the fastest method possible, the SqlDataReader is the best choice. Also, if the amount of data you need to read is larger than what you would prefer to hold in memory beyond a single call, then the streaming behavior of the S......餘下全文>>
 
通過SQLDataReader讀取資料的過程

//...前面省略
using System.Data.SqlClient; //1:引入命名空間

namespace Test
{
public partial class Form1 : Form
{
static string conStr = "server=.; database = myqq; uid=sa; pwd=0000;"; //2: 定義連接字串
SqlConnection con = new SqlConnection(conStr); //3: 定義資料庫連接對象

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string sql = "select * from Users"; //要查詢的sql語句
SqlCommand cmd = new SqlCommand(sql, con); //4：建立sqlcommand對象， 執行sql語句
con.Open(); //5: 開啟資料庫連接
SqlDataReader dr = cmd.ExecuteReader(); //6： 執行sql語句並返回 sqldatareader對象 <相當賦值>
while (dr.Read()) //7：利用sqldatareader讀取資料
{
MessageBox.Show("姓名是："+dr["Uname"].ToString()); //只是舉了一個例子
}
dr.Close(); //8：關閉sqldatareader
con.Close(); //9：關閉資料庫連接

// 利用sqldatareader讀取資料的時候，資料連線必須處於開啟狀態； 讀取方式是 一行一行向前讀取

}
}
}