c# - MySQL डाटाबेस में पंक्तियों को सम्मिलित करने का सबसे कुशल तरीका




performance (8)

एक Transaction में कमांड निष्पादित करें और प्रत्येक पुनरावृत्ति के लिए कमांड के समान उदाहरण का पुन: उपयोग करें। आगे के प्रदर्शन अनुकूलन के लिए, एक कमांड में 100 प्रश्न भेजें। समानांतर निष्पादन के लिए जाना बेहतर प्रदर्शन ( Parallel.For ) दे सकता है लेकिन सुनिश्चित करें कि प्रत्येक समानांतर लूप को अपना MySqlCommand उदाहरण मिलता है।

public static void CSVToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);";
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString)) 
    {
        mConnection.Open();
        using (MySqlTransaction trans = mConnection.BeginTransaction()) 
        {
            using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection, trans)) 
            {
                myCmd.CommandType = CommandType.Text;
                for (int i = 0; i <= 99999; i++) 
                {
                    //inserting 100k items
                    myCmd.Parameters.Clear();
                    myCmd.Parameters.AddWithValue("@FirstName", "test");
                    myCmd.Parameters.AddWithValue("@LastName", "test");
                    myCmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
        }
    }
}

मैंने उसके बारे में बहुत सारे सवाल पढ़े हैं, लेकिन मुझे वह नहीं मिला जो काफी तेज है। मुझे लगता है कि MySQL Database में बहुत सारी पंक्तियाँ डालने के बेहतर तरीके हैं

मैं अपने MySQL-डेटाबेस में 100k डालने के लिए निम्न कोड का उपयोग करता हूं:

public static void CSVToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);";
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        mConnection.Open();

        for(int i =0;i< 100000;i++) //inserting 100k items
        using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.Parameters.AddWithValue("@FirstName", "test");
            myCmd.Parameters.AddWithValue("@LastName", "test");
            myCmd.ExecuteNonQuery();
        }
    }
}

यह 100k पंक्तियों के लिए लगभग 40 सेकंड लेता है। मैं इसे कैसे तेज या थोड़ा अधिक कुशल बना सकता हूं?

डेटाटेबल / डाटा एडेप्टर के माध्यम से या एक ही बार में कई पंक्तियाँ डालने के लिए तेज़ हो सकता है:

INSERT INTO User (Fn, Ln) VALUES (@Fn1, @Ln1), (@Fn2, @Ln2)...

सुरक्षा समस्याओं के कारण मैं डेटा को किसी फ़ाइल और MySQLBulkLoad में लोड नहीं कर सकता।


एक बल्क ऑपरेशन उस से नेतृत्व करने का एक अच्छा तरीका होगा। कुछ है जो आपके गुणों को पढ़ते हैं और फिर आपके लिए एक बल्क क्वेरी बनाते हैं ...

वहाँ एक github रिपॉजिटरी है जिसमें दोनों उपयोगी विधियाँ शामिल हैं: MySql और EF6 + का उपयोग करके BulkInsert और BulkUpdate।

BulkUpdate / BulkInsert मूल रूप से आपकी जेनेरिक इकाई से सभी गुणों को पढ़ता है और फिर आपके लिए बल्कक्वारी बनाता है।

Ps: यह मेरी जरूरतों के लिए विकसित किया गया है और इस परियोजना को खोला गया है जो इसे बेहतर बनाने के लिए चिंता करता है या इसे बेहतर समाधान के लिए बदल देता है जो समुदाय के लिए उपयुक्त होगा।

Ps: यदि यह परेशानी को पूरा नहीं करता है, तो जो आप चाहते हैं उसे सुधारने और हासिल करने के लिए प्रोजेक्ट पर बदलाव करने की कोशिश करें, यह कम से कम एक अच्छी शुरुआत है।

कृपया, here


जैसा कि स्टीफन स्टीगर कहते हैं, बल्क इंसर्ट आपकी स्थितियों के लिए उपयुक्त है।

एक और चाल मंचन की मेज का उपयोग कर रही है, इसलिए उत्पादन तालिका में सीधे लिखने के बजाय, आप मंचन एक (जिसमें समान संरचना है) को लिखेंगे। सभी जानकारी लिखने के बाद आप सिर्फ टेबल स्वैप करें। मंचन के साथ आप प्रविष्टि के लिए तालिकाओं को लॉक करने से बचेंगे (अद्यतन के लिए उपयोग किया जा सकता है और बहुत हटा भी सकते हैं), और इस पैटर्न का उपयोग कुछ परियोजनाओं में MySQL के साथ किया जाता है।

इसके अलावा, टेबल कुंजियों को अक्षम करने से सम्मिलन को गति मिल सकती है, लेकिन जब आप उन्हें सक्षम कर सकते हैं तो कुछ समस्याएं भी पेश कर सकते हैं (केवल MyISAM इंजन के लिए)

जोड़ा गया :

मान लें कि आपके पास तालिका Products :

  • उत्पाद आइ डि
  • उत्पाद का नाम
  • उत्पाद की कीमत

मंचन के उद्देश्य के लिए आप उसी चरण के कॉलम के साथ, ProductStaging नामक एक स्टेजिंग टेबल बनाते हैं।

आपका सारा ऑपरेशन आप मंचन की मेज पर करते हैं:

UpdateStagingTable();
SwapTables();
UpdateStagingTable();

क्योंकि स्वैप के बाद आपकी स्टेजिंग टेबल में नया डेटा नहीं होता है, आप एक ही विधि को एक बार फिर से लागू करते हैं। SwapTables() विधि में आप एक SQL कथन निष्पादित करते हैं:

RENAME TABLE Products TO ProductsTemp,
             ProductsStaging TO Products,
             ProductsTemp TO ProductsStagin;

डेटा जोड़तोड़ की गति MySql इंजन (जैसे InnoDB, MyISAM आदि) पर निर्भर करती है, इसलिए आप इंजन को बदलकर आवेषण की गति भी बढ़ा सकते हैं।


मुझे बल्क इंसर्ट के लिए फ़ाइल का उपयोग करने से बचने का तरीका मिल गया है। इस संबंधक में स्ट्रीम से कार्यान्वयनकर्ता लोड था। तो लोडिंग कुछ ऐसा किया जा सकता है

  public void InsertData(string table, List<string> columns, List<List<object>> data) {

  using (var con = OpenConnection() as MySqlConnection) {
    var bulk = new MySqlBulkLoader(con);
    using (var stream = new MemoryStream()) {
      bulk.SourceStream = stream;
      bulk.TableName = table;
      bulk.FieldTerminator = ";";
      var writer = new StreamWriter(stream);

      foreach (var d in data)
        writer.WriteLine(string.Join(";", d));

      writer.Flush();
      stream.Position = 0;
      bulk.Load();
    }
  }
}

मैं EF - MySQL के साथ काम करते समय एक समान समस्या पर लड़खड़ा गया। EF आवेषण वैसे भी धीमी गति से थे और इसलिए द्वारा उल्लिखित दृष्टिकोण का उपयोग किया। शुरू करने के लिए, प्रदर्शन में काफी सुधार हुआ (~ 20K रिकॉर्ड ~ 10 सेकंड में डाला गया था) लेकिन तालिका में आकार बढ़ने के साथ अपमानित किया गया, तालिका में ~ 1M रिकॉर्ड के साथ, प्रविष्टि में ~ 250 सेकंड लगे।

अंत में इस मुद्दे का पता चला! तालिका का PK GUID (UUID - char (36)) प्रकार का था। चूंकि UUIDs क्रमिक रूप से अनुक्रमित नहीं कर सकते हैं और प्रत्येक प्रविष्टि को अनुक्रमणिका को फिर से बनाने की आवश्यकता होती है, यह धीमा हो गया।

फिक्स को पीके को बिगिंट (या इंट) से बदलना था और इसे पहचान स्तंभ के रूप में सेट करना था। इससे प्रदर्शन में सुधार हुआ, सम्मिलन ने तालिका में ~ 2M + रिकॉर्ड के साथ औसतन ~ 12 सेकंड लिया!

मैंने सोचा कि मैं इस खोज को यहाँ साझा करूँगा जब कोई व्यक्ति इसी तरह की समस्या पर फंस जाएगा!


मैंने तीन चीजों MySqlDataAdapter, लेनदेन और UpdateBatchSize का उपयोग करके एक छोटा परीक्षण किया। यह आपके पहले उदाहरण से लगभग 30 गुना तेज है। मैसकल अलग बॉक्स पर चल रहा है इसलिए इसमें विलंबता शामिल है। बैचसाइज़ को कुछ ट्यूनिंग की आवश्यकता हो सकती है। कोड इस प्रकार है:

string ConnectionString = "server=xxx;Uid=xxx;Pwd=xxx;Database=xxx";

string Command = "INSERT INTO User2 (FirstName, LastName ) VALUES (@FirstName, @LastName);";


 using (var mConnection = new MySqlConnection(ConnectionString))
     {
         mConnection.Open();
         MySqlTransaction transaction = mConnection.BeginTransaction();

        //Obtain a dataset, obviously a "select *" is not the best way...
        var mySqlDataAdapterSelect = new MySqlDataAdapter("select * from User2", mConnection);

        var ds = new DataSet();

        mySqlDataAdapterSelect.Fill(ds, "User2");


        var mySqlDataAdapter = new MySqlDataAdapter();

        mySqlDataAdapter.InsertCommand = new MySqlCommand(Command, mConnection);


        mySqlDataAdapter.InsertCommand.Parameters.Add("@FirstName", MySqlDbType.VarChar, 32, "FirstName");
        mySqlDataAdapter.InsertCommand.Parameters.Add("@LastName", MySqlDbType.VarChar, 32, "LastName");
        mySqlDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        var stopwatch = new Stopwatch();
        stopwatch.Start();

        for (int i = 0; i < 50000; i++)
        {
            DataRow row = ds.Tables["User2"].NewRow();
            row["FirstName"] = "1234";
            row["LastName"] = "1234";
            ds.Tables["User2"].Rows.Add(row);
        }

         mySqlDataAdapter.UpdateBatchSize = 100;
         mySqlDataAdapter.Update(ds, "User2");

         transaction.Commit();

         stopwatch.Stop();
         Debug.WriteLine(" inserts took " + stopwatch.ElapsedMilliseconds + "ms");
    }
}

यह तरीका स्ट्रिंगर दृष्टिकोण की तुलना में तेज़ नहीं हो सकता है, लेकिन इसे मानकीकृत किया गया है:

/// <summary>
    /// Bulk insert some data, uses parameters
    /// </summary>
    /// <param name="table">The Table Name</param>
    /// <param name="inserts">Holds list of data to insert</param>
    /// <param name="batchSize">executes the insert after batch lines</param>
    /// <param name="progress">Progress reporting</param>
    public void BulkInsert(string table, MySQLBulkInsertData inserts, int batchSize = 100, IProgress<double> progress = null)
    {
        if (inserts.Count <= 0) throw new ArgumentException("Nothing to Insert");

        string insertcmd = string.Format("INSERT INTO `{0}` ({1}) VALUES ", table,
                                         inserts.Fields.Select(p => p.FieldName).ToCSV());
        StringBuilder sb = new StringBuilder(); 
        using (MySqlConnection conn = new MySqlConnection(ConnectionString))
        using (MySqlCommand sqlExecCommand = conn.CreateCommand())
        {
            conn.Open();
            sb.AppendLine(insertcmd);
            for (int i = 0; i < inserts.Count; i++)
            {
                sb.AppendLine(ToParameterCSV(inserts.Fields, i));
                for (int j = 0; j < inserts[i].Count(); j++)
                {
                    sqlExecCommand.Parameters.AddWithValue(string.Format("{0}{1}",inserts.Fields[j].FieldName,i), inserts[i][j]);
                }
                //commit if we are on the batch sizeor the last item
                if (i > 0 && (i%batchSize == 0 || i == inserts.Count - 1))
                {
                    sb.Append(";");
                    sqlExecCommand.CommandText = sb.ToString();
                    sqlExecCommand.ExecuteNonQuery();
                    //reset the stringBuilder
                    sb.Clear();
                    sb.AppendLine(insertcmd);
                    if (progress != null)
                    {
                        progress.Report((double)i/inserts.Count);
                    }
                }
                else
                {
                    sb.Append(",");
                }
            }
        }
    }

यह नीचे दिए गए सहायक वर्गों का उपयोग करता है:

/// <summary>
/// Helper class to builk insert data into a table
/// </summary>
public struct MySQLFieldDefinition
{
    public MySQLFieldDefinition(string field, MySqlDbType type) : this()
    {
        FieldName = field;
        ParameterType = type;
    }

    public string FieldName { get; private set; }
    public MySqlDbType ParameterType { get; private set; }
}

///
///You need to ensure the fieldnames are in the same order as the object[] array
///
public class MySQLBulkInsertData : List<object[]>
{
    public MySQLBulkInsertData(params MySQLFieldDefinition[] fieldnames)
    {
        Fields = fieldnames;
    }

    public MySQLFieldDefinition[] Fields { get; private set; }
}

और यह सहायक विधि:

    /// <summary>
    /// Return a CSV string of the values in the list
    /// </summary>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    private string ToParameterCSV(IEnumerable<MySQLFieldDefinition> p, int row)
    {
        string csv = p.Aggregate(string.Empty,
            (current, i) => string.IsNullOrEmpty(current)
                    ? string.Format("@{0}{1}",i.FieldName, row)
                    : string.Format("{0},@{2}{1}", current, row, i.FieldName));
        return string.Format("({0})", csv);
    }

शायद सुपर एलिगेंट नहीं है लेकिन यह अच्छी तरह से काम करता है। मुझे प्रगति ट्रैकिंग की आवश्यकता है ताकि मेरे लिए इसे शामिल किया जाए, उस हिस्से को हटाने के लिए स्वतंत्र महसूस करें।

यह आपके इच्छित आउटपुट के समान SQL कमांड का उत्पादन करेगा।

संपादित करें: ToCSV:

        /// <summary>
    /// Return a CSV string of the values in the list
    /// </summary>
    /// <param name="intValues"></param>
    /// <param name="separator"></param>
    /// <param name="encloser"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    public static string ToCSV<T>(this IEnumerable<T> intValues, string separator = ",", string encloser = "")
    {
        string result = String.Empty;
        foreach (T value in intValues)
        {
            result = String.IsNullOrEmpty(result)
                ? string.Format("{1}{0}{1}", value, encloser)
                : String.Format("{0}{1}{3}{2}{3}", result, separator, value, encloser);
        }
        return result;
    }

यहाँ मेरा "एकाधिक आवेषण" -code है।

100k पंक्तियों का सम्मिलन 40 सेकंड के बजाय केवल 3 सेकंड लिया गया !!

public static void BulkToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");           
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        List<string> Rows = new List<string>();
        for (int i = 0; i < 100000; i++)
        {
            Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
        }
        sCommand.Append(string.Join(",", Rows));
        sCommand.Append(";");
        mConnection.Open();
        using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.ExecuteNonQuery();
        }
    }
}

बनाया गया SQL- स्टेटमेंट इस तरह दिखता है:

INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;

अपडेट : धन्यवाद सलमान ए मैंने कोड इंजेक्शन से बचने के लिए MySQLHelper.EscapeString जोड़ा जो आंतरिक रूप से तब उपयोग किया जाता है जब आप मापदंडों का उपयोग करते हैं।







performance