Stored Procedure experience and Optimization Measures

A stored procedure is one or more SQL commands stored as executable objects in the database.
Definition is always abstract. The stored procedure is actually a set of SQL statements that can complete certain operations, but these statements are stored in the database (Here we only talk about SQL Server ). If we create a stored procedure and call it in ASP, we can avoid mixing SQL statements with ASP code. There are at least three advantages:
First, greatly improve efficiency. Stored procedures are executed very quickly, and calling stored procedures can greatly reduce the number of interactions with the database.
Second, improve security. If SQL statements are mixed in ASP code, once the code is out of password, it also means that the database structure is out of password.
Third, it is conducive to the reuse of SQL statements.

In ASP, the stored procedure is generally called through the command object. Based on different situations, this article also introduces other calling methods. For convenience, the following simple classification is made based on the input and output of the stored procedure:
1. Only the stored procedure of a single record set is returned.
Suppose there are the following stored procedures (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code, not to mention ):

/* SP1 */
Create procedure DBO. getuserlist
As
Set nocount on
Begin
Select * From DBO. [userinfo]
End
Go

The above Stored Procedure Retrieves all records in the userinfo table and returns a record set. The ASP code for calling the stored procedure through the command object is as follows:

'** Call the stored procedure through the command object **
Dim mycomm, myrst
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "getuserlist" 'specifies the name of the stored procedure.
Mycomm. commandtype = 4 'indicates that this is a stored procedure
Mycomm. Prepared = true 'requires that the SQL command be compiled first
Set myrst = mycomm. Execute
Set mycomm = nothing

The set of records obtained by the stored procedure is assigned to myrst. Next, you can operate on myrst.
In the preceding code, the commandtype attribute indicates the request type. The values and descriptions are as follows:
-1 indicates that the commandtext parameter type cannot be determined

1 indicates that commandtext is a common command type
2. The commandtext parameter is an existing table name.
4. The commandtext parameter is the name of a stored procedure.

You can also call the stored procedure through the connection object or recordset object. The methods are as follows:

 

'** Call the stored procedure through the connection object **
Dim myconn, myrst
Set myconn = server. Createobject ("ADODB. Connection ")
Myconn. Open myconstr 'myconstr is the database connection string
Set myrst = myconn. Execute ("getuserlist",) 'the last parameter is the same as commandtype
Set myconn = nothing

'** Call the stored procedure through the recordset object **
Dim myrst
Set myrst = server. Createobject ("ADODB. recordset ")
Myrst. Open "getuserlist", myconstr, 0, 1, 4
'Myconstr is the database connection string. The last parameter is of the same meaning as commandtype.

2. Stored Procedure without input/output
See the following stored procedure:

/* SP2 */
Create procedure DBO. deluserall
As
Set nocount on
Begin
Delete from DBO. [userinfo]
End
Go

In this stored procedure, all records in the userinfo table are deleted without any input or output. The Calling method is basically the same as described above, but the record set is not required:

'** Call the stored procedure through the command object **
Dim mycomm
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "deluserall" 'specifies the name of the stored procedure
Mycomm. commandtype = 4 'indicates that this is a stored procedure
Mycomm. Prepared = true 'requires that the SQL command be compiled first
Mycomm. Execute 'you do not need to retrieve the record set here

Set mycomm = nothing

 

Of course, this type of stored procedure can also be called through the connection object or recordset object, but the recordset object is created to obtain the record set. If no record set is returned, use the command object.

3. Stored Procedures with returned values
When performing SP2-like operations, SQL Server's powerful transaction processing functions should be fully utilized to maintain data consistency. In addition, we may need to return the execution status of the stored procedure. For this reason, modify SP2 as follows:

/* SP3 */
Create procedure DBO. deluserall
As
Set nocount on
Begin
Begin transaction
Delete from DBO. [userinfo]
If @ error = 0
Begin
Commit transaction
Return 1
End
Else
Begin
Rollback transaction
Return 0
End
Return
End
Go

In the preceding stored procedure, 1 is returned when the delete operation is executed successfully. Otherwise, 0 is returned and the rollback operation is performed. To obtain the return value in ASP, you must use the parameters set to declare the parameter:

'** Call a stored procedure with a returned value and obtain the returned value **
Dim mycomm, mypara
Set mycomm = server. Createobject ("ADODB. Command ")
Mycomm. activeconnection = myconstr 'myconstr is the database connection string
Mycomm. commandtext = "deluserall" 'specifies the name of the stored procedure
Mycomm. commandtype = 4 'indicates that this is a stored procedure
Mycomm. Prepared = true 'requires that the SQL command be compiled first
'Declared Return Value
Set mypara = mycomm. createparameter ("return", 2, 4)

Mycomm. Parameters. append mypara
Mycomm. Execute
'Get the return value
Dim retvalue
Retvalue = mycomm (0) 'or retvalue = mycomm. parameters (0)
Set mycomm = nothing

In mycomm. createparameter ("return", 2, 4), the meanings of parameters are as follows:
The first parameter ("Reture") is the parameter name. The parameter name can be set arbitrarily, but it should be the same as the parameter name declared in the stored procedure. Here is the return value. I used to set it to "Reture ";
The second parameter (2) indicates the Data Type of the parameter. For details about the type code, see the ADO reference. The following describes the common type code:
Adbigint: 20;
Adbinary: 128;
Adboolean: 11;
Adchar: 129;
Addbtimestamp: 135;
Adempty: 0;
Adinteger: 3;
Adsmallint: 2;
Adtinyint: 16;
Advarchar: 200;
For return values, only integer values can be taken and-1 to-99 are reserved values;
The third parameter (4) indicates the nature of the parameter. Here 4 indicates that this is a return value. The value of this parameter is described as follows:
0: The type cannot be determined; 1: input parameter; 2: input parameter; 3: input or output parameter; 4: Return Value

The above ASP code should be the complete code, that is, the most complex code. In fact

 

Set mypara = mycomm. createparameter ("return", 2, 4)
Mycomm. Parameters. append mypara

Can be simplified

Mycomm. Parameters. append mycomm. createparameter ("return", 2, 4)

It can even be simplified and will be explained later.
For stored procedures with parameters, you can only use the command object to call (it can also be called through the connection object or recordset object, but I have not tried it ).

4. Stored Procedures with Input and Output Parameters
The returned value is actually a special output parameter. In most cases, we use a stored procedure with both input and output parameters. For example, if we want to obtain the username of an ID in the user information table, there is an input parameter ---- user ID, and an output parameter ---- user name. The stored procedure for implementing this function is as follows:

/* SP4 */
Create procedure DBO. GetUserName
@ Userid int,
@ Username varchar (40) Output
As
Set nocount on
Begin
If @ userid is null return
Select @ username = Username
From DBO. [userinfo]

{
Diggit (507302,19247, 1)
} "> 0 {
Diggit (507302,19247, 1)
} "> 1. Suitable for readers: Database developers, who have a large amount of data in the database, involve project developers who are interested in SP (Stored Procedure) optimization.

II. Introduction: Complex business logic and database operations are often encountered during database development. At this time, database operations are encapsulated using SP. If there are many SP projects and there is no certain specification for writing, it will affect the difficulties of system maintenance and the difficulty of understanding the big SP logic in the future, in addition, if the database has a large amount of data or the project has high performance requirements for the SP, you will encounter optimization problems. Otherwise, the speed may be slow. After hands-on experience, an Optimized SP is hundreds of times more efficient than an optimized SP with poor performance.

Iii. content: 　

1. If developers use tables or views of other databases, they must create a view in the current database to perform cross-database operations. It is best not to directly use "Databse. DBO. table_name ", because sp_depends cannot display the cross-database table or view used by the SP, it is not convenient to verify.

2. Before submitting the SP, the developer must have used set showplan on to analyze the query plan and perform its own query optimization check.

3. High program running efficiency and application optimization. Pay attention to the following points during SP writing:

A) SQL usage Specification:

I. Avoid large transaction operations as much as possible. Use the holdlock clause with caution to improve the system concurrency capability.

Ii. Try to avoid repeated accesses to the same or several tables, especially tables with large data volumes. You can consider extracting data to a temporary table based on the conditions and then connecting it.

III. avoid using a cursor whenever possible because the cursor is inefficient. If the cursor operation contains more than 10 thousand rows of data, it should be rewritten. If the cursor is used, try to avoid table join operations in the cursor loop.

IV. note that when writing where statements, the order of statements must be taken into account. The order before and after condition clauses should be determined based on the index order and range size, and the field order should be consistent with the index order as much as possible, the range is from large to small.

V. do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause. Otherwise, the system may not be able to correctly use the index.

VI. use exists instead of select count (1) to determine whether a record exists. The count function is used only when all the rows in the statistical table are used, and count (1) is more efficient than count.

VII. Try to use "> =" instead of "> ".

VIII. Note the replacement between the or clause and the union clause.

IX. Pay attention to the data types connected between tables to avoid the connection between different types of data.

X. Pay attention to the relationship between parameters and data types in stored procedures.

XI. Pay attention to the data volume of insert and update operations to prevent conflicts with other applications. If the data volume exceeds 200 data pages (400 Kb), the system will update the lock and the page lock will be upgraded to the table lock.

B) index usage Specification:

I. You should consider creating indexes in combination with applications. We recommend that you create a large OLTP table with no more than six indexes.

Ii. Try to use the index field as the query condition, especially the clustered index. If necessary, you can use index index_name to forcibly specify the index.

Iii. Avoid performing table scan when querying large tables. If necessary, create an index.

IV. when using an index field as a condition, if the index is a joint index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used.

V. Pay attention to index maintenance, rebuild indexes periodically, and recompile the stored procedure.

C) use of tempdb:

I. Avoid using distinct, order by, group by, having, join, and cumpute as much as possible, because these statements will increase the burden on tempdb.

Ii. Avoid frequent creation and deletion of temporary tables and reduce the consumption of system table resources.

III. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid logs and increase the speed. If the data volume is small, in order to ease the system table resources, we recommend that you first create table and then insert.

IV. if the temporary table has a large amount of data and requires an index, you should place the process of creating a temporary table and creating an index in a single sub-storage process, in this way, the system can use the index of the temporary table.

V. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.

Vi. Use caution when connecting large temporary tables to other large tables to query and modify them, reducing the burden on the system table, because this operation will use the tempdb system table multiple times in one statement.

D) Reasonable algorithm usage:

Based on the SQL optimization technology mentioned above and the SQL Optimization content in the ASE tuning manual, combined with practical applications, a variety of algorithms are used for comparison to obtain the method with the least resource consumption and the highest efficiency. Specific ASE optimization commands are available: Set statistics Io on, set statistics time on, set showplan on, and so on.