none
关于文本文件插入数据库速度的问题 RRS feed

  • 问题

  • 我看到网上说VB是C语言写的,用VB把TXT转换为数据库速度超快,可是用C语言一行一行读入数据库速度很慢的,到底是用什么方式实现的? 难道除了一行一行读入还另有其他 方法吗?
    2011年10月7日 11:35

答案

  • 在百度上搜bcp或者sqlbulkcopy,例子很多啊
    bcp是一个命令行工具,示例如下
    bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c
    SqlBulkCopy可以在Visual C++里面用,不是C#的,是.Net的。VB的示例如下:
     Imports System.Data.SqlClient
    Module Module1
    Sub Main()
    Dim connectionString As String = GetConnectionString()
            ' Open a connection to the AdventureWorks database.
    Using sourceConnection As SqlConnection = _
    New SqlConnection(connectionString)
    sourceConnection.Open()
                ' Perform an initial count on the destination table.
    Dim commandRowCount As New SqlCommand( _
    "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
    sourceConnection)
    Dim countStart As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    Console.WriteLine("Starting row count = {0}", countStart)
                ' Get data from the source table as a SqlDataReader.
    Dim commandSourceData As SqlCommand = New SqlCommand( _
    "SELECT ProductID, Name, ProductNumber " & _
    "FROM Production.Product;", sourceConnection)
    Dim reader As SqlDataReader = commandSourceData.ExecuteReader
                ' Open the destination connection. In the real world you would 
    ' not use SqlBulkCopy to move data from one table to the other
    ' in the same database. This is for demonstration purposes only.
    Using destinationConnection As SqlConnection = _
    New SqlConnection(connectionString)
    destinationConnection.Open()
                    ' Set up the bulk copy object. 
    ' The column positions in the source data reader
    ' match the column positions in the destination table,
    ' so there is no need to map columns.
    Using bulkCopy As SqlBulkCopy = _
    New SqlBulkCopy(destinationConnection)
    bulkCopy.DestinationTableName = _
    "dbo.BulkCopyDemoMatchingColumns"
                        Try
    ' Write from the source to the destination.
    bulkCopy.WriteToServer(reader)
                        Catch ex As Exception
    Console.WriteLine(ex.Message)
                        Finally
    ' Close the SqlDataReader. The SqlBulkCopy
    ' object is automatically closed at the end
    ' of the Using block.
    reader.Close()
    End Try
    End Using
                    ' Perform a final count on the destination table
    ' to see how many rows were added.
    Dim countEnd As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    Console.WriteLine("Ending row count = {0}", countEnd)
    Console.WriteLine("{0} rows were added.", countEnd - countStart)
                    Console.WriteLine("Press Enter to finish.")
    Console.ReadLine()
    End Using
    End Using
    End Sub
        Private Function GetConnectionString() As String
    ' To avoid storing the sourceConnection string in your code,
    ' you can retrieve it from a configuration file.
    Return "Data Source=(local);" & _
    "Integrated Security=true;" & _
    "Initial Catalog=AdventureWorks;"
    End Function
    End Module
     

    想不想时已是想,不如不想都不想。
    • 已标记为答案 Honny_yeyh 2011年10月9日 3:02
    2011年10月9日 2:40
    版主

全部回复

  • c这样处理字符串本来就慢.当一个流整体读入比较快.

    另外,最快的方式是BCP。


    想不想时已是想,不如不想都不想。
    2011年10月8日 2:20
    版主
  • c这样处理字符串本来就慢.当一个流整体读入比较快.

    另外,最快的方式是BCP。


    想不想时已是想,不如不想都不想。

    流整体读入数据库具体是什么思路,或者是什么函数?
    2011年10月8日 5:08
  • SqlBulkCopy和bcp性能相当。

    SqlBulkCopy对文件位置没要求,bcp需要服务器直接能够访问文件。

     

    ps,流就是当二进制来读取的,这个需要看你的需求,比如保存一个图片到数据库,这个就适合作为二进制来存,你这里的文本格式要看实际要求了。

     


    family as water
    • 已标记为答案 Honny_yeyh 2011年10月8日 9:40
    • 取消答案标记 Honny_yeyh 2011年10月8日 9:45
    2011年10月8日 5:48
  • SqlBulkCopy和bcp性能相当。

    SqlBulkCopy对文件位置没要求,bcp需要服务器直接能够访问文件。

     

    ps,流就是当二进制来读取的,这个需要看你的需求,比如保存一个图片到数据库,这个就适合作为二进制来存,你这里的文本格式要看实际要求了。

     


    family as water

    我查了下 sqlbulkcopy 是c#的东西,bcp好像是sql server里面的命令,我想用c++来调用函数的思路怎么处理?
    2011年10月8日 9:46
  • bcp需要服务器直接能够访问文件。

    不对,你以为是PDW啊,bcp是在客户端调用的。

    想不想时已是想,不如不想都不想。
    2011年10月8日 11:03
    版主
  • bcp需要服务器直接能够访问文件。

    不对,你以为是PDW啊,bcp是在客户端调用的。

    想不想时已是想,不如不想都不想。

    能不能给个小例子,几个简单的代码就可以,因为百度上实在找不到啊
    2011年10月9日 1:27
  • 在百度上搜bcp或者sqlbulkcopy,例子很多啊
    bcp是一个命令行工具,示例如下
    bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c
    SqlBulkCopy可以在Visual C++里面用,不是C#的,是.Net的。VB的示例如下:
     Imports System.Data.SqlClient
    Module Module1
    Sub Main()
    Dim connectionString As String = GetConnectionString()
            ' Open a connection to the AdventureWorks database.
    Using sourceConnection As SqlConnection = _
    New SqlConnection(connectionString)
    sourceConnection.Open()
                ' Perform an initial count on the destination table.
    Dim commandRowCount As New SqlCommand( _
    "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
    sourceConnection)
    Dim countStart As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    Console.WriteLine("Starting row count = {0}", countStart)
                ' Get data from the source table as a SqlDataReader.
    Dim commandSourceData As SqlCommand = New SqlCommand( _
    "SELECT ProductID, Name, ProductNumber " & _
    "FROM Production.Product;", sourceConnection)
    Dim reader As SqlDataReader = commandSourceData.ExecuteReader
                ' Open the destination connection. In the real world you would 
    ' not use SqlBulkCopy to move data from one table to the other
    ' in the same database. This is for demonstration purposes only.
    Using destinationConnection As SqlConnection = _
    New SqlConnection(connectionString)
    destinationConnection.Open()
                    ' Set up the bulk copy object. 
    ' The column positions in the source data reader
    ' match the column positions in the destination table,
    ' so there is no need to map columns.
    Using bulkCopy As SqlBulkCopy = _
    New SqlBulkCopy(destinationConnection)
    bulkCopy.DestinationTableName = _
    "dbo.BulkCopyDemoMatchingColumns"
                        Try
    ' Write from the source to the destination.
    bulkCopy.WriteToServer(reader)
                        Catch ex As Exception
    Console.WriteLine(ex.Message)
                        Finally
    ' Close the SqlDataReader. The SqlBulkCopy
    ' object is automatically closed at the end
    ' of the Using block.
    reader.Close()
    End Try
    End Using
                    ' Perform a final count on the destination table
    ' to see how many rows were added.
    Dim countEnd As Long = _
    System.Convert.ToInt32(commandRowCount.ExecuteScalar())
    Console.WriteLine("Ending row count = {0}", countEnd)
    Console.WriteLine("{0} rows were added.", countEnd - countStart)
                    Console.WriteLine("Press Enter to finish.")
    Console.ReadLine()
    End Using
    End Using
    End Sub
        Private Function GetConnectionString() As String
    ' To avoid storing the sourceConnection string in your code,
    ' you can retrieve it from a configuration file.
    Return "Data Source=(local);" & _
    "Integrated Security=true;" & _
    "Initial Catalog=AdventureWorks;"
    End Function
    End Module
     

    想不想时已是想,不如不想都不想。
    • 已标记为答案 Honny_yeyh 2011年10月9日 3:02
    2011年10月9日 2:40
    版主
  • SqlBulkCopy在Visual C++里面怎么用,我参照C#代码

    http://www.cnblogs.com/muliang/p/3831797.html

    写的visual C++程序,发现使用SqlBulkCopy出现内存泄漏,主要代码如下:

    SqlConnection^ Conn = gcnew SqlConnection();
    SqlConnection^ Conn->ConnectionString = Marshal::PtrToStringAnsi((IntPtr)cSQLCmd);
    SqlConnection^ Conn->Open();

    DataTable^   Table = gcnew DataTable();

    DataRow^     Row;

    SqlBulkCopy^ BulkCopy = gcnew SqlBulkCopy(Conn);

    Table->Columns->Add("nMachID", System::Type::GetType("System.String"));
    Table->Columns->Add("lSampTime", System::Type::GetType("System.String"));

    Row = Table->NewRow();

    Row["nMachID"] = Marshal::PtrToStringAnsi((IntPtr)cDeviceName);
    Row["lSampTime"] = Marshal::PtrToStringAnsi((IntPtr)cSampTime);

    Table->Rows->Add(Row);

    BulkCopy->DestinationTableName = Marshal::PtrToStringAnsi((IntPtr)cDataSheetName);

    BulkCopy->ColumnMappings->Add("nMachID", "nMachID");//前者为临时表,后者为目标表
    BulkCopy->ColumnMappings->Add("lSampTime", "lSampTime");

    BulkCopy->WriteToServer(Table);
    BulkCopy->Close();
    Table->Rows->Clear();

    请高手看下,如何解决。万分感谢。

    2017年3月7日 9:40