Optimization of ADO. Net Usage

 

1. Open and Close the database connection. Open the connection when you need to access the database. Close the connection immediately after the database is accessed.

For example, let's look at two code segments:

I.

Dataset DS = new dataset ();

Sqlconnection myconnection = new sqlconnection ("Server = localhost; uid = sa; Pwd =; database = northwind ");

Sqlcommand mycommand = new sqlcommand (strsql, myconnection );

Sqldataadapter myadapter = new sqldataadapter (querystr, connectionstr );

Myconnection. open (); // open the connection

For (INT I = 0; I <1000; I ++) // For Loop Simulation of business logic operations before data is obtained

{

Thread. Sleep (1000 );

}

Myadapter. Fill (DS );

For (INT I = 0; I <1000; I ++) // For Loop Simulation of business logic operations after data is obtained

{

Thread. Sleep (1000 );

}

Myconnection. Close (); // close the connection

II.

Dataset DS = new dataset ();

Sqlconnection myconnection = new sqlconnection ("Server = localhost; uid = sa; Pwd =; database = northwind ");

Sqlcommand mycommand = new sqlcommand (strsql, myconnection );

Sqldataadapter myadapter = new sqldataadapter (querystr, connectionstr );

For (INT I = 0; I <1000; I ++) // For Loop Simulation of business logic operations before data is obtained

{

Thread. Sleep (1000 );

}

Myconnection. open (); // open the connection

Myadapter. Fill (DS );

Myconnection. Close (); // close the connection

For (INT I = 0; I <1000; I ++) /// For Loop Simulation of business logic operations after data is obtained

{

Thread. Sleep (1000 );

}

The display II code is much better than I code, and I occupies the connection early. If there are many users, the connection pool may be full. Serious crashes.

2. Database Query

I. Generate SQL statements directly. SQL Server needs to compile SQL Server every time, which won't be greatly improved in terms of performance. In addition, it is not safe enough. Easy to attack.

Ii. use SQL commands with parameters. In this way, SQL Server only compiles the program once. For different parameters, you can repeat the compiled command. Improved performance.

Iii. use SQL Server Stored Procedures. Compile once. It is independent and easy to modify and maintain. It can send Statements multiple times at a time, reducing network

Traffic. Stored procedures are not necessarily more efficient than statements. If the business logic is complex, sometimes statements are more efficient than stored procedures.