sqlbulkcopy mem。管理

datatable memory sqlbulkcopy

我正在使用SQLBULKCOPY將一些數據表複製到數據庫表中,但是,因為我複制的文件大小有時會超過600mb,所以我的內存不足。

我希望在將其提交到數據庫之前獲得有關管理表大小的一些建議,這樣我就可以釋放一些內存來繼續編寫。

以下是我的代碼的一些示例(為簡單起見,刪除了一些列和行)

            SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(ServerConfiguration); //Define the Server Configuration
        System.IO.StreamReader rdr = new System.IO.StreamReader(fileName);

        Console.WriteLine("Counting number of lines...");
        Console.WriteLine("{0}, Contains: {1} Lines", fileName, countLines(fileName));

        DataTable dt = new DataTable();

        sqlbulkCopy.DestinationTableName = "[dbo].[buy.com]"; //You need to define the target table name where the data will be copied
        dt.Columns.Add("PROGRAMNAME");
        dt.Columns.Add("PROGRAMURL");
        dt.Columns.Add("CATALOGNAME");

        string inputLine = "";
        DataRow row; //Declare a row, which will be added to the above data table

        while ((inputLine = rdr.ReadLine()) != null) //Read while the line is not null
            {
                i = 0;
                string[] arr;

                Console.Write("\rWriting Line: {0}", k);
                arr = inputLine.Split('\t'); //splitting the line which was read by the stream reader object (tab delimited)
                row = dt.NewRow();
                row["PROGRAMNAME"] = arr[i++];
                row["PROGRAMURL"] = arr[i++];
                row["CATALOGNAME"] = arr[i++];
                row["LASTUPDATED"] = arr[i++];
                row["NAME"] = arr[i++];
                dt.Rows.Add(row);
                k++;
        }

        // Set the timeout, 600 secons (10 minutes) given table size--damn that's a lota hooch
        sqlbulkCopy.BulkCopyTimeout = 600;
        try
        {
            sqlbulkCopy.WriteToServer(dt);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        sqlbulkCopy.Close();//Release the resources
        dt.Dispose();

        Console.WriteLine("\nDB Table Written: \"{0}\" \n\n", sqlbulkCopy.DestinationTableName.ToString());

    }

我繼續遇到讓SQLBulkCopy工作的問題,我意識到我需要在每條記錄進入數據庫之前對它做更多的工作,所以我開發了一個簡單的LinQ to Sql方法來記錄更新記錄,所以我可以編輯其他信息,並在運行時創建更多記錄信息,

問題:這種方法運行速度很慢(即使在Core i3機器上),關於如何加速(線程化?)的任何想法 - 在單處理器核心上,1gb內存崩潰或有時需要6-8小時編寫與一個需要片刻的SQLBulkCopy相同數量的數據。它確實更好地管理內存。

            while ((inputLine = rdr.ReadLine()) != null) //Read while the line is not null
        {
            Console.Write("\rWriting Line: {0}", k);
            string[] arr;              
            arr = inputLine.Split('\t');

            /* items */
            if (fileName.Contains(",,"))
            {
                Item = Table(arr);
               table.tables.InsertOnSubmit(Item);

                /* Check to see if the item is in the db */
                bool exists = table.tables.Where(u => u.ProductID == Item.ProductID).Any();

                /* Commit */
                if (!exists)
                {
                    try
                    {
                        table.SubmitChanges();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                        // Make some adjustments.
                        // ...
                        // Try again.
                        table.SubmitChanges();
                    }
                }
            }

使用輔助方法:

    public static class extensionMethods
{
    /// <summary>
    /// Method that provides the T-SQL EXISTS call for any IQueryable (thus extending Linq).
    /// </summary>
    /// <remarks>Returns whether or not the predicate conditions exists at least one time.</remarks>
    public static bool Exists<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
    {
        return source.Where(predicate).Any();
    }
}

一般承認的答案

嘗試將BatchSize屬性指定為1000,這將在1000個記錄批次而不是整個批次中批量插入。您可以調整此值以查找最佳值。我使用sqlbulkcopy來獲得類似大小的數據,效果很好。


熱門答案

面對同樣的問題,發現OutOfMemory Exception的問題是在DataTable.Rows中的最大數量限制。解決了重建表,限制最多500000行。希望,我的解決方案將有所幫助:

var myTable = new System.Data.DataTable();
myTable.Columns.Add("Guid", typeof(Guid));
myTable.Columns.Add("Name", typeof(string));

int counter = 0;

foreach (var row in rows)
{
    ++counter;

    if (counter < 500000)
    {
        myTable.Rows.Add(
            new object[]
            {
                row.Value.Guid,
                row.Value.Name
            });
    }
    else
    {
        using (var dbConnection = new SqlConnection("Source=localhost;..."))
        {
            dbConnection.Open();
            using (var s = new SqlBulkCopy(dbConnection))
            {
                s.DestinationTableName = "MyTable";

                foreach (var column in myTable.Columns)
                    s.ColumnMappings.Add(column.ToString(), column.ToString());

                try
                {
                    s.WriteToServer(myTable);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    s.Close();
                }
            }
        }

        myTable = new System.Data.DataTable();
        myTable.Columns.Add("Guid", typeof(Guid));
        myTable.Columns.Add("Name", typeof(string));

        myTable.Rows.Add(
            new object[]
            {
                row.Value.Guid,
                row.Value.Name
            });

        counter = 0;

    }
}


許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow