Improve SQL Server database performance from the client

First: Write query statements that limit the search scope.

As we all know, it is directly related to the efficiency of the query when the database query returns the number of records. Therefore, in the client through a certain conditional statement, limit the scope of the search, often can greatly improve the efficiency of the query.

If the user queries the database on the client, in the query statement, add the top statement to show the previous 50 or 100 records. As a result of experience, users in the query data, about 60% to see is the previous record. Especially in some historical transaction information tables, such as in the ERP System inventory transactions table, you can display only the previous hundreds of records, without the need to display all the records. When the user feel that the record is not enough, you can press "all", then the client then go to the server to query all the results. This design can be very effective to improve the query performance of the database.

If you can set the default conditional statement on the client. such as in the ERP system, there is a purchase order form, in the background database, corresponding to the purchase order such a table. By default, when you inquire about purchase orders, you will find the purchase orders that are not settled. In this case, even if the user in the query purchase orders, no input purchase order number or the order date and other restrictions, the client when submitting query statements to the server, the default will be restricted conditional statement added. Thus, it is helpful to improve the efficiency of the first query of the database.

Of course, whether you are using the top statement or using the WHERE statement to set the default restrictions is not a random setup. This often needs to be determined according to the customer's usage habits and the nature of the form. As with the Customer information table, the number of customers is not much, so there is no need to set limits to search the scope of the query statement. But for the inventory transaction schedule, one months down, there is a possibility of thousands of records. Such a large amount of data, if not set restrictions, then query up, the user will wait longer. Therefore, in this case, we can default to show only the previous 500 records or only the last 30 days of transaction information.

In summary, it is a preferred method to increase the performance of the database server on the client side by appropriately adding a query statement that restricts the search scope.

Second: Try not to use complex stored procedures.

Although the SQL Server database provides a strong store procedure capability, it is best not to call the database's stored procedures frequently when the foreground application is designed. This is mainly because the stored procedure is convenient, but its execution speed is not ordinary application, such as C language so fast.

From a functional perspective, many stored procedures can be completed by the function of the foreground application can be fully implemented. such as in some invoicing management system, often need to convert lowercase amount to uppercase amount, in the purchase order printed out. This function can be realized through the stored procedure of the database, or through the foreground application. However, according to the author's observation, it is found that the performance of the storage function of the database is not ideal. If the stored procedure is slightly more complex, if the parameters are more, the client response time will be slower. Conversely, if you do not implement this function in the background of the database, but directly in the foreground to implement the application, it will be much faster.

In addition, if a stored procedure is established in the background database, the workload of the server will be increased. Imagine, now the procurement department has 10 employees, if in a period of time, are maintaining the purchase order, it is necessary to call this stored procedure, then the server's resources will be "contention." On the other hand, if this function is implemented on the client side, because it is executed on the client, the server resources will not have to scramble.

Therefore, the author in the database design time, rarely use stored procedures. Can be implemented using the client application, the foreground application is implemented. If you really want to adopt a stored procedure, you also need to use those stored procedures that reduce contention and increase concurrency.