Advantages of using parameters instead of splicing SQL statements _ MySQL-mysql tutorial

Source: Internet
Author: User
Tags oracleconnection
Benefits of Using parameters instead of splicing SQL statements bitsCN.com

In actual development, you often need to access the database. The most common development method is similar:

String SQL = "select * from table1 where name = '" + name + "'";

This method has the danger of being injected into the attack (what is injection, search for it, too many)

Therefore, there are two solutions:

1. change to: string SQL = "select * from table1 where name = '" + name. replace ("'", "'' ") +"' "; // Replace one single quotation mark with two single quotation marks

2. use parameterized form. for example, in Oracle, execute it as follows:

String SQL = "select * from table1 where name =: vName ";

OracleParameter para = new OracleParameter ("vName", OracleType. VarChar );

Para. Value = name;

OracleConnection con = new OracleConnection (constr );

Con. Open ();

OracleCommand com = con. CreateCommand ();

Com. CommandText = SQL;

Com. Parameters. Add (para );

Com. ExecuteReader ();

In this case, the use of parameterization is much more complex, and the replacement method is much simpler.

So I have always been dealing with replacement methods, and yesterday I found another benefit of using parameters (derived from: http://bbs.bc-cn.net/redirect.php? Tid = 242233 & goto = lastpost)

Only then can we know that using parameterization can also improve the performance of Oracle (I don't know if SQL Server has similar benefits)

Optimize the SQL statement by analyzing the SQL statement execution plan
Shared SQL statements
To avoid repeated parsing of the same SQL statement (because the parsing operation is more resource-consuming, it will lead to performance degradation), after the first parsing, ORACLE stores SQL statements and execution plans after parsing in the memory. The memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if the statement is exactly the same as a previous statement, in addition, if the previously executed statement and its execution plan still exist in the memory, ORACLE does not need to analyze the statement and directly obtain the execution path of the statement. This function greatly improves SQL execution performance and saves memory usage. The key to using this function is to put the executed statements into the memory as much as possible, so this requires a large shared pool (by setting the shared buffer pool parameter value) and try to use the method of binding variables to execute SQL statements.
When you submit an SQL statement to ORACLE, ORACLE first checks whether the same statement exists in the shared memory. It should be noted that ORACLE adopts a strict match between the two. to achieve sharing, the SQL statements must be identical (including spaces and line breaks ).

The following steps are used to determine whether an SQL statement is the same as an SQL statement in the shared memory:
1. hashed the text string of the issued statement. If the hash value is the same as the hash value of the SQL statement already in the shared pool, perform Step 1:
2. compare the text string (including case, white space, and comment) of the issued statement with all
Compare existing SQL statements.

For example:
SELECT * FROM emp WHERE empno = 1000;
Different from each of the following
SELECT * from emp WHERE empno = 1000;
SELECT * from emp where empno = 1000;
SELECT * FROM emp WHERE empno = 2000;
In the preceding statements, the column values are in direct SQL statements. in the future, we will convert these SQL statements into hard-coded SQL statements.
Or literal SQL
Bind variables must be used in SQL statements that bind variables ),

For example:
A. The two SQL statements are considered the same
Select pin, name from people where pin =: blk1.pin;
Select pin, name from people where pin =: blk1.pin;
B. The two SQL statements are considered different.
Select pin, name from people where pin =: blk1.ot _ ind;
Select pin, name from people where pin =: blk1.ov _ ind;
In the future, we will refer to the preceding statements as binding variable SQL.

3. compare the objects involved in the issued statement with the objects involved in the existing statement identified in step 1.
For example:
If both user user1 and user user2 have an EMP table
Statement issued by user1: SELECT * from emp;
Statement issued by user2: SELECT * from emp;
Is considered to be different statements,
Because the EMP referenced in the two statements does not refer to the same table.

4. the bind types of variable used in SQL statements must be consistent.
If the statement is equivalent to another statement in the current shared pool, Oracle does not perform syntax analysis on it. Directly executing this statement improves the execution efficiency because syntax analysis is resource-consuming.
Note that a new CURSOR_SHARING parameter is introduced from oracle 8i. The main purpose of this parameter is to solve the hard-coded SQL problem that has been used in a large number of programming procedures. In actual development, many programmers adopt the following development methods to improve the development speed:
Str_ SQL string;
Int_empno int;
Int_empno = 2000;
Str_ SQL = 'select * FROM emp WHERE empno = '+ int_empno;
............
Int_empno = 1000;
Str_ SQL = 'select * FROM emp WHERE empno = '+ int_empno;
The above code actually uses hard-coded SQL, so that we cannot use the shared SQL function. The result is that the database efficiency is not high. However, from the two statements above, the hard-coded SQL statements are generated only with different column values, and the other statements are the same, it would be a pity that these two statements cannot be shared because of different column values. to solve this problem, the CURSOR_SHARING parameter is introduced so that shared SQL can be used for such problems, so that such development can also use the shared SQL function. It sounds good. ORACLE is really a concern for users, so that users can use the SQL sharing function without changing the code. Is that true? The sky won't drop a pie for no reason. ORACLE describes the use of this parameter. we recommend that you change the value of this parameter after the actual test (by default, the value of this parameter is EXACT, and shared SQL is used only when the statements are completely consistent ). It may be because after this value is changed, your hard-coded SQL can use shared SQL, but the database performance will decrease. I have encountered this situation in practical applications. Therefore, it is recommended that developers who need to run programs stably use the variable-bound SQL statement at the beginning.

BitsCN.com
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.