Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use variables to optimize SQL". In daily operation, I believe many people have doubts about how to use variables to optimize SQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to use variables to optimize SQL". Next, please follow the editor to study!

1. What is a variable?

A variable is actually a variable parameter that we define, and its basic syntax is as follows:

-- define a variable named @ I and specify its type as an integer

DECLARE @ I VARCHAR (20)

-- assign the variable @ I to

SET @ I='SQL database development'

-- output the value of @ I

SELECT @ I

Results: SQL database development

Where DECLARE @ part is fixed, @ I is the name of the variable, the variable must be defined as a type, generally defined as character type, integer type, time type and so on.

The SET of the assignment part is also written in a fixed way, that is, the variable @ I is assigned, and the one on the right is the assignment content.

After defining the variable, you can bring it into the query statement. Each time, you only need to modify the assignment part, and the query statement will query the corresponding results according to the assignment content.

two。 Why use variables?

After using variables, if the same query statement only has different assignments, the first execution plan can be reused to achieve the effect of parsing once and reusing many times. Reducing the parsing of the execution plan will improve the query speed accordingly. Let's look at the following example:

SELECT * FROM T1 WHERE ORDER_ID='112'; SELECT * FROM T1 WHERE ORDER_ID='113'

If you execute these two query statements separately, the query optimizer thinks they are different SQL statements that need to be parsed twice. We use variables to modify it.

DECLARE @ ORDER_ID VARCHAR (20) SET @ ORDER_ID='112' SELECT * FROM T1 WHERE ORDER_ID=@ORDER_ID

After execution, you can reuse the above execution plan only by changing the value of @ ORDER_ID to '113. Because the above statement is relatively simple, you may not see the effect, but if you encounter more complex query statements, variable queries often have a good effect.

3. When should / should not use variables

Common online queries can be used to variables, variables can be passed as parameters to the database, you can achieve a query, reuse the execution plan.

If you query a statement alone for a long time, for example, more than half an hour, this use of variables has no obvious effect.

4 variable snooping

Everything has two sides, variable to common query can improve the efficiency of query. But there are exceptions, such as when the field in the WHERE condition is a "skewed field".

"tilted field" means that most of the values in the column are the same, such as the population questionnaire, in which more than 90% of the "nationality" column is Han nationality. So if a SQL statement is to query the population of the Han nationality at the age of 30, then the column of "nationality" must be included in the WHERE condition. There will be a big problem if you use the binding variable @ NATION at this time.

If the first value passed in by @ NATION is "Han", then the entire execution plan must choose a table scan.

DECLARE @ NATION VARCHAR (50) SET @ NATION=' Han 'SELECT * FROM People WHERE AGE=30 AND NATION=@NATION

When the second value is passed in "she nationality", under normal circumstances, the proportion of "she nationality" in the table may only be 1/10000, so index search should be used.

DECLARE @ NATION VARCHAR (50) SET @ NATION=' she nationality 'SELECT * FROM People WHERE AGE=30 AND NATION=@NATION

Since the execution plan of the "Han" parsed for the first time is reused, the table scan method will also be used for the second time. This problem is known as "variable snooping", and it is recommended that you do not use bound variables for "skewed fields".

At this point, the study on "how to use variables to optimize SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.