程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> SQL批量拔出數據幾種計劃的機能具體比較

SQL批量拔出數據幾種計劃的機能具體比較

編輯:MSSQL

SQL批量拔出數據幾種計劃的機能具體比較。本站提示廣大學習愛好者:(SQL批量拔出數據幾種計劃的機能具體比較)文章只能為提供參考,不一定能成為您想要的結果。以下是SQL批量拔出數據幾種計劃的機能具體比較正文


公司技巧配景:數據庫拜訪類(xxx.DataBase.Dll)挪用存儲進程完成數據庫的拜訪。

技巧計劃一:

緊縮時光下法式員寫出的第一個版本,僅僅為了完成義務,沒有從法式上做任何優化,完成方法是應用數據庫拜訪類挪用存儲進程,應用輪回逐條拔出。很顯著,這類方法效力其實不高,因而有了後面的兩位同事評論辯論效力低的成績。

技巧計劃二:

因為是斟酌到年夜數據量的批量拔出,因而我想到了ADO.NET2.0的一個新的特征:SqlBulkCopy。有關這個的機能,很早之前我是親身做過機能測試的,效力異常高。這也是我向公司同事推舉的技巧計劃。

技巧計劃三:

應用SQLServer2008的新特征--表值參數(Table-Valued Parameter)。表值參數是SQLServer2008才有的一個新特征,應用這個新特征,我們可以把一個表類型作為參數傳遞到函數或存儲進程裡。不外,它也有一個特色:表值參數在拔出數量少於 1000 的行時具有很好的履行機能。

技巧計劃四:

關於單列字段,可以把要拔出的數據停止字符串拼接,最初再在存儲進程中拆分紅數組,然後逐條拔出。查了一下存儲進程中參數的字符串的最年夜長度,然後除以字段的長度,算出一個值,很顯著是可以知足請求的,只是這類方法跟第一種方法比起來,仿佛沒甚麼進步,由於道理都是一樣的。

技巧計劃五:

斟酌異步創立、新聞隊列等等。這類計劃不管從設計上照樣開辟上,難度都是有的。

技巧計劃一確定是要被否失落的了,剩下的就是在技巧計劃二跟技巧計劃三之間做一個決定,鑒於公司今朝的情形,技巧計劃四跟技巧計劃五就先不斟酌了。

接上去,為了讓年夜家對表值參數的創立跟挪用有更理性的熟悉,我將寫的更具體些,文章能夠也會稍長些,不存眷細節的同伙們可以選擇騰躍式的浏覽方法。

再說一下測試計劃吧,測試總共分三組,一組是拔出數目小於1000的,別的兩組是拔出數據量年夜於1000的(這裡我們分離取10000跟1000000),每組測試又分10次,取均勻值。怎樣做都明確了,Let's go!

1.創立表。

為了簡略,表中只要一個字段,以下圖所示:

2.創立表值參數類型

我們翻開查詢剖析器,然後在查詢剖析器中履行以下代碼:

Create Type PassportTableType as Table
(
PassportKey nvarchar(50)
)

履行勝利今後,我們翻開企業治理器,按次序順次睜開以下節點--數據庫、睜開可編程性、類型、用戶自界說表類型,便可以看到我們創立好的表值類型了以下圖所示:

解釋我們創立表值類型勝利了。

3.編寫存儲進程

存儲進程的代碼為:

USE [TestInsert]
GO
/****** Object: StoredProcedure [dbo].[CreatePassportWithTVP] Script Date: 03/02/2010 00:14:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:    <Kevin>
-- Create date: <2010-3-1>
-- Description:    <創立通行證>
-- =============================================
Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

能夠在查詢剖析器中,智能提醒會提醒表值類型有成績,會湧現白色下劃線(見下圖),不消理睬,持續運轉我們的代碼,完成存儲進程的創立
 
4.編寫代碼挪用存儲進程。

三種數據庫的拔出方法代碼以下,因為時光比擬緊,代碼能夠不那末易讀,特殊代碼我加了些正文。

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using com.DataAccess;
namespace ConsoleAppInsertTest
{
class Program
{
static string connectionString = SqlHelper.ConnectionStringLocalTransaction; //數據庫銜接字符串
static int count = 1000000; //拔出的條數
static void Main(string[] args)
{
//long commonInsertRunTime = CommonInsert();
//Console.WriteLine(string.Format("通俗方法拔出{1}條數據所用的時光是{0}毫秒", commonInsertRunTime, count));
long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();
Console.WriteLine(string.Format("應用SqlBulkCopy拔出{1}條數據所用的時光是{0}毫秒", sqlBulkCopyInsertRunTime, count));
long TVPInsertRunTime = TVPInsert();
Console.WriteLine(string.Format("應用表值方法(TVP)拔出{1}條數據所用的時光是{0}毫秒", TVPInsertRunTime, count));
}
/// <summary>
/// 通俗挪用存儲進程拔出數據
/// </summary>
/// <returns></returns>
private static long CommonInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
SqlParameter[] sqlParameter = { new SqlParameter("@passport", passportKey) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter);
}
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
/// <summary>
/// 應用SqlBulkCopy方法拔出數據
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private static long SqlBulkCopyInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "Passport";
sqlBulkCopy.BatchSize = dataTable.Rows.Count;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
private static long TVPInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}
SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
private static DataTable GetTableSchema()
{
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PassportKey") });
return dataTable;
}
}
}

比擬奧秘的代碼其實就上面這兩行,該代碼是將一個dataTable做為參數傳給了我們的存儲進程。簡略吧。

SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);
5.測試並記載測試成果
第一組測試,拔出記載數1000

第二組測試,拔出記載數10000

第三組測試,拔出記載數1000000

經由過程以上測試計劃,不難發明,技巧計劃二的優勢照樣蠻高的。不管是從通用性照樣從機能上斟酌,都應當是
優先被選擇的,還有一點,它的技巧龐雜度要比技巧計劃三要簡略一些,

假想我們把一切表都創立一遍表值類型,任務量照樣有的。是以,我仍然保持我開端時的決議,
向公司推舉應用第二種技巧計劃。

寫到此,本文就算完了,然則對新技巧的研究依然還在赓續持續。要做的器械照樣挺多的。

為了便利年夜家進修和交換,代碼文件曾經打包並上傳了,迎接配合進修商量。
代碼下載
作者:深山老林
出處:http://wlb.cnblogs.com/

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved