C # implement regular batch execution of SQL statements using SQL pools and Multithreading

In actual project development, the processing speed of the business logic layer is usually very fast, especially when the socket communication service is developed, the network transmission is very fast, but once the database operation is added, the performance will plummet, the efficiency of database operations often becomes a bottleneck of the overall system performance. What should we do in the face of this problem? Well, I will introduce you to a method as follows: Build an SQL pool, separate the business logic layer from the data access layer, and free the business logic layer from inefficient database operations, to improve the overall system performance.

(1) SQL pool

The SQL pool is an SQL container used to store SQL statements thrown by the business logic layer. The SQL Pool provides the following methods:

1) internal string POP (), which extracts the SQL statement from the pool.

2) Internal void push (string item), add an SQL statement to the pool.

3) internal string [] Clear (): clears the SQL pool. before clearing the SQL pool, all SQL statements in the SQL pool are returned.

Note that the SQL pool is multi-threaded, so you must adopt a lock mechanism for SQL statements of public resources. The mutex lock is used here. When the business logic layer thread throws an SQL statement into the SQL pool, the SQL Execution thread is prohibited from executing the SQL statement. If the SQL Execution thread executes the SQL statement, the business logic layer threads are not allowed to throw SQL statements into the SQL pool. Why? Because the SQL Execution thread executes SQL statements in batches, all SQL statements are retrieved from the pool before the SQL statements are executed in batches. If the business logic layer thread throws SQL statements into the SQL pool at this time, these SQL statements are lost and cannot be executed.

The following 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
{
// Mutex lock
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>
/// Obtain the number of SQL pools
/// </Summary>
Internal int32 count
{
Get {return this. Pool. Count ;}
}


/// <Summary>
/// Retrieve the SQL statement from the pool
/// </Summary>
/// <Returns> </returns>
Internal string POP ()
{
Lock (this. Pool)
{
Return this. Pool. Pop ();
}
}


/// <Summary>
/// Add an SQL statement 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 ");

}

// The push SQL statement to the SQL pool must be mutually exclusive with clear.
Mutexsqlpool. waitone ();
Try
{
This. Pool. Push (item); // if an error occurs here, releasemutex will not be executed and a deadlock will occur.
}
Catch
{
}
Mutexsqlpool. releasemutex ();
}


/// <Summary>
/// Clear the SQL pool
/// Return all SQL statements in the SQL pool before clearing,
/// </Summary>
Internal string [] Clear ()
{
String [] array = new string [] {};

// This must be mutually exclusive with push
Mutexsqlpool. waitone ();
Try
{
Array = This. Pool. toarray (); // if an error occurs, releasemutex is not executed and a deadlock occurs.
This. Pool. Clear ();
}
Catch
{
}
Mutexsqlpool. releasemutex ();

Return array;
}
}
}
 

(2) SQL pool management

SQL pool management is mainly used to manage SQL pools and provide interfaces to business logic layer threads and SQL Execution threads.

The business logic layer thread calls the public void pushsql (string strsql) method to throw an SQL statement into the SQL pool.

The SQL Execution thread calls the public void executesql (Object OBJ) method to execute SQL statements in the SQL pool in batches.

Note: The SQL pool management class adopts the singleton model. Why is the singleton model used? Because the SQL pool can only have one instance, whether it is a business logic layer thread or an SQL Execution thread, only this instance is operated. Otherwise, the SQL pool is not unique and the SQL Execution is invalid.

The following is the SQL pool management code:

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. text;

Namespace test1
{
Class sqlpoolmanage
{
// Singleton Model
Public static readonly sqlpoolmanage = new sqlpoolmanage ();


# Region attributes
Sqlpool poolofsql;
# Endregion


# Region Constructor
/// <Summary>
/// Initialization
/// </Summary>
Public sqlpoolmanage ()
{
This. poolofsql = new sqlpool ();
}
# Endregion


# Region Method
/// <Summary>
/// Add SQL statements to the SQL pool
/// </Summary>
/// <Param name = "strsql"> </param>
Public void pushsql (string strsql)
{
This. poolofsql. Push (strsql );
}


/// <Summary>
/// Triggers executesql at intervals
/// Executesql is used to execute SQL statements in the SQL pool.
/// </Summary>
/// <Param name = "OBJ"> </param>
Public void executesql (Object OBJ)
{
If (this. poolofsql. Count> 0)
{
String [] array = This. poolofsql. Clear ();
// Traverse the array and execute SQL
For (INT I = 0; I <array. length; I ++)
{
If (array [I]. tostring (). Trim ()! = "")
{
Try
{
// Database operations
//......
}
Catch
{
}
}
}
}
}
# Endregion

}
}
 

 

(3) regularly trigger the SQL Execution thread

The following three methods are provided: http://www.cnblogs.com/tianzhiliang/archive/2010/08/31/1813928.html:

Method 1: Call the thread execution method to implement an endless loop in the method. Set the sleep time for each loop;

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)
{
// Throw an SQL statement into the SQL pool
Sqlpoolmanage. sqlpoolmanage. pushsql ("delete from tbl_test where id = 1 ");


// Regularly trigger the SQL Execution thread
System. Threading. Timer threadtimer = new system. Threading. Timer (new system. Threading. timercallback (sqlpoolmanage. sqlpoolmanage. executesql), null, 0,100 );


Console. Readline ();
}
}
}