SQL Statement Optimization Questions, Several Methods of SQL Optimization Interview

With a view or an index.

create index

Create an index for a given table or view.

Only the owner of a table or view can create an index for a table. The owner of a table or view can create an index at any time, regardless of whether there is data in the table. You can create an index for a table or view in another database by specifying a qualified database name.

Grammar. create [ unique ] clustered | nonclustered ] index index_name

on (column [ asc | desc ] n ]with < index_option > n] ]on filegroup ]

index_option > =

pad_index |

fillfactor = fillfactor |ignore_dup_key |

drop_existing |

statistics_norecompute |sort_in_tempdb

This is the basic syntax, indexing, only for the speed of queries and some updates and deletions, like the gender column, if there are 1000 rows in the table, if only 1 row is male, so it must be high with indexing, if there are 990 rows male, then it is better to scan directly, this is selective.

1.As I understand it, there is a mistake in your SQL statement itself, so many or and and mixed together, don't you think it should be parenthesed?

2.If it's not efficient, take your ones or take them out and use union

In the process of software development, the use of databases is very important, but there are many kinds of databases, and different databases are used in different ways. In the process of software development, it is necessary to know at least one way to use the database. SQL database syntax is simple, easy to operate and efficient, which is the best choice for many people, but SQL statements will be affected by different database functions, and the calculation time and language efficiency need to be optimized and adjusted according to the actual situation.

The following computer training will introduce you to the optimization method of SQL database.

1. Proper indexing.

An index is basically a data structure that helps speed up the entire data retrieval process. A unique index is an index that creates columns of data that do not overlap. The right index can access the database faster, but too many or no indexes can lead to wrong results.

IT training believes that without indexing, processing becomes very slow.

2. Only relevant data is indexed.

Specify the precision of the data to be retrieved. Use the command * and limit instead of select*. When tuning a database, you must use the required dataset rather than the entire dataset, especially if the data source is very large, specifying the desired dataset can save most of the time.

3. Use or avoid temporary tables as needed.

If you can write it in a simple way, never make a temporary table complicated. Of course, if the data has a specific program that requires multiple queries, Beida Jade Bird recommends using temporary tables in this case. Temporary tables are often alternated by subqueries.

4. Avoid coding loops.

It is very important to avoid coding loops as it slows down the entire sequence. Coding loops are avoided by using unique update or insert commands with a single line, and the where command ensures that stored data is not updated, making it easier to find matches and pre-existing data.

The goal of SQL performance tuning is to reduce the number of data reads and writes and reduce CPU compute.

There is actually only one way to achieve the above two goals, and that is to change the SQL execution plan, so that it can "avoid detours" as much as possible, and try to find the data it needs through various "shortcuts".

1. Analyze complex SQL statements and improve them.

2. Enable cached queries to speed up the same queries.

3. Make static tables faster, use joins as little as possible for complex multi-tables, and sort as little as possible.

4. Optimize from the overall situation, rather than one-sided adjustment.

1.You can understand everything you learned in school, and you can explain the basic concepts and principles, such as SQL optimization, which is to index the database, how to improve the search speed, and so on. It's all said in the textbooks.

2.The interview does not mean that you must know everything to go to the interview, you need to find a little more interview experience, what questions are often asked in the interview, such as pointers, linked lists, etc., and prepare for it in a targeted manner, so you don't have to look at it all in one go.

3.Accumulate experience and programming works, do more projects, and write more programs yourself. The interview has a bargaining chip.