c implements the SQL pool multithreaded timed batch execution of SQL statements method

  • 2020-05-19 05:38:21
  • OfStack

In the actual project development, the processing speed of the business logic layer is often very fast, especially in the development of Socket communication service, the network transmission is very fast, but once the database operation is added, the performance drops by thousands, and the efficiency of the database operation often becomes the bottleneck of the overall performance of a system. What shall we do in the face of this problem? Ok, let me introduce you to one method: build the SQL pool, separate the business logic layer from the data access layer, and free the business logic layer from inefficient database operation, so as to improve the overall performance of the system.

(1) SQL pool

The SQL pool is the SQL container for the SQL statements thrown at the business logic layer. The SQL pool mainly provides the following methods:

1) internal string Pop(), take SQL from the pool.

2) internal void Push(string item), add 1 SQL to the pool.

3) internal string[] Clear(), empty SQL pool, clear SQL pool, return all SQL statements in SQL pool.

Note 1 that the SQL pool is multithreaded oriented, so you must lock the common resource SQL. A mutex is used here. When the business logic layer thread throws SQL statements into the SQL pool, the SQL execution thread is not allowed to execute SQL statements. Conversely, when the SQL execution thread executes SQL statements, the business logic layer thread is not allowed to throw SQL statements into the SQL pool. Why would you do that? Because the SQL execution thread is executing SQL statements in batches, all SQL statements will be taken out of the pool before the batch execution of SQL statements. If the business logic layer thread drops SQL statements into the SQL pool at this time, these SQL statements will be lost and cannot be executed.

Here is the SQL pool code:


using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Threading; 

namespace Test1 
{ 
    sealed class SQLPool 
    { 
        // The mutex  
        public static Mutex mutexSQLPool = new Mutex(); 

        //SQL pool  
        Stack<string> pool; 

        /// <summary> 
        ///  Initialize the SQL pool  
        /// </summary> 
        internal SQLPool() 
        { 
            this.pool = new Stack<string>(); 
        } 

  
        /// <summary> 
        ///  To obtain SQL The number  
        /// </summary> 
        internal Int32 Count 
        { 
            get { return this.pool.Count; } 
        } 

  
        /// <summary> 
        ///  Remove from the pool SQL 
        /// </summary> 
        /// <returns></returns> 
        internal string Pop() 
        { 
            lock (this.pool) 
            { 
                return this.pool.Pop(); 
            } 
        } 

  
        /// <summary> 
        ///  increase 1 a SQL To the pool  
        /// </summary> 
        /// <param name="item"></param> 
        internal void Push(string item) 
        { 
            if (item.Trim() == "") 
            { 
                throw new ArgumentNullException("Items added to a SQLPool cannot be null"); 
            } 

            // Here to SQL In the pool push SQL Must be connected to Clear The mutex  
            mutexSQLPool.WaitOne(); 
            try
            { 
                this.pool.Push(item);    // If there is an error here, it will not be executed ReleaseMutex , will be deadlocked  
            } 
            catch
            {  
            } 
            mutexSQLPool.ReleaseMutex(); 
        } 

  
        /// <summary> 
        ///  empty SQL pool  
        ///  Clear unprecedented, return SQL In the pool all SQL Statements,  
        /// </summary> 
        internal string[] Clear() 
        { 
            string[] array = new string[] { }; 

            // Here must be associated with Push The mutex  
            mutexSQLPool.WaitOne(); 
            try
            { 
                array = this.pool.ToArray();     // If there is an error here, it will not be executed ReleaseMutex , will be deadlocked  
                this.pool.Clear(); 
            } 
            catch
            {  
            } 
            mutexSQLPool.ReleaseMutex(); 

            return array; 
        } 
    } 
} 

(2) SQL pool management

SQL pool management is primarily used to manage the SQL pool, providing interfaces to the business logic layer threads and the SQL execution threads.

The business logic layer thread calls the public void PushSQL(string strSQL) method to drop the SQL statement into the SQL pool.

The SQL execution thread calls the public void ExecuteSQL(object obj) method for the bulk execution of SQL statements in the SQL pool.

Note that the SQL pool management class USES a singleton model. Why use a singleton model? Because the SQL pool can only exist one instance, no matter it is the business logic layer thread or the SQL execution thread, only this one instance will be operated. Otherwise, the SQL pool will be not only 1, and SQL execution will be invalid.

Here is the code for the SQL pool management class:


using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 

namespace Test1 
{ 
    class SQLPoolManage 
    { 
        // Single case model  
        public static readonly SQLPoolManage sqlPoolManage = new SQLPoolManage(); 

        #region  attribute  
        SQLPool poolOfSQL; 
        #endregion 

  
        #region  The constructor  
        /// <summary> 
        ///  Initialize the  
        /// </summary> 
        public SQLPoolManage() 
        { 
            this.poolOfSQL = new SQLPool(); 
        } 
        #endregion 

  
        #region  methods  
        /// <summary> 
        ///  will SQL Statements to join SQL In the pool  
        /// </summary> 
        /// <param name="strSQL"></param> 
        public void PushSQL(string strSQL) 
        { 
            this.poolOfSQL.Push(strSQL); 
        } 

  
        /// <summary> 
        ///  every 1 Period of time, triggered ExecuteSQL 
        /// ExecuteSQL Used to perform SQL In the pool SQL statements  
        /// </summary> 
        /// <param name="obj"></param> 
        public void ExecuteSQL(object obj) 
        { 
            if (this.poolOfSQL.Count > 0) 
            { 
                string[] array = this.poolOfSQL.Clear(); 
                // traverse array , the implementation of SQL 
                for (int i = 0; i < array.Length; i++) 
                { 
                    if (array[i].ToString().Trim() != "") 
                    { 
                        try
                        { 
                            // Database operation  
                            //...... 
                        } 
                        catch
                        {  
                        } 
                    } 
                } 
            } 
        } 
        #endregion 

    } 
} 

(3) the SQL execution thread is triggered periodically

There are three ways to summarize:
Method 1: the thread is called to execute the method, and an infinite loop is implemented in the method. The time is set for each loop Sleep.

Method 2: use the System.Timers.Timer class;

Method 3: use System.Threading.Timer;

The code is as follows:


using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 

using System.Threading; 

namespace Test1 
{ 

    class Program 
    { 
        static void Main(string[] args)
        { 
            // to SQL Into the pool SQL statements  
            SQLPoolManage.sqlPoolManage.PushSQL("delete from tbl_test where id = 1"); 

            // Timing trigger SQL The thread of execution  
            System.Threading.Timer threadTimer = new System.Threading.Timer(new System.Threading.TimerCallback(SQLPoolManage.sqlPoolManage.ExecuteSQL), null, 0, 100); 

            Console.ReadLine(); 
        } 
    } 
} 


Related articles: