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

This article will explain in detail what the principles of SQL optimization are, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

I. the raising of questions

In the initial stage of the development of the application system, due to the lack of database data, the performance of various writing methods of SQL statements can not be realized by querying SQL statements and writing complex views, but if the application system is submitted to the practical application, with the increase of data in the database, the response speed of the system has become one of the most important problems that the system needs to solve. A very important aspect of system optimization is the optimization of SQL statements. For large amounts of data, the speed difference between low-quality SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that for a system, it is not simply to achieve its function, but to write high-quality SQL statements to improve the availability of the system.

In most cases, Oracle uses indexes to traverse tables faster, and the optimizer mainly improves performance based on defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, it will cause the optimizer to delete the index and use a full table scan. Generally speaking, this kind of SQL statement is the so-called inferior SQL statement. When writing SQL statements, we should know what principles the optimizer uses to delete indexes, which helps to write high-performance SQL statements.

II. Problems needing attention in the compilation of SQL sentences

The following is a detailed introduction to the problems that should be paid attention to in the writing of where clauses of some SQL statements. In these where clauses, even if some columns have an index, the system cannot use the index when running the SQL statement because of the poor SQL, and the full table scan is also used, which greatly reduces the response speed.

1. IS NULL and IS NOT NULL

You cannot use null as an index, and any column that contains a null value will not be included in the index. Even if the index has multiple columns, as long as one of these columns contains null, the column is excluded from the index. That is, if a column has a null value, even indexing that column will not improve performance.

Any statement optimizer that uses is null or is not null in the where clause does not allow the use of indexes.

two。 Join column

For columns with joins, the optimizer does not use indexes even if the final join value is a static value. Let's take a look at an example. Suppose there is a job list (employee). For an employee whose last name and first name are stored in two columns (FIRST_NAME and LAST_NAME), we now want to query a bill. An employee of Bill Cliton.

The following is a SQL statement that uses a join query

Select

* from employss where first_name | |''| | last_name

= 'Beill Cliton'

The above statement can well find out if there is an employee named Bill Cliton, but it is important to note that the system optimizer does not use indexes created based on last_name.

When writing the following SQL statement, the Oracle system can adopt an index created based on last_name.

* where first_name = 'Beill'

And last_name

= 'Cliton'

. Like statement with wildcard (%)

Also use the above example to see this situation. The current requirement is to query the employee table for people whose names contain cliton. You can use the following query SQL statement:

Select

* from employee

Where last_name

Like

'% cliton%'

Here, because the wildcard (%) appears at the beginning of the search word, the Oracle system does not use last_name 's index. This may not be avoided in many cases, but be sure to keep in mind that using wildcards in this way can slow down queries. However, when wildcards appear elsewhere in the string, the optimizer can take advantage of the index. The index is used in the following query:

Select

* from employee

Where last_name

Like

'c%'

4. Order by statement

The ORDER BY statement determines how Oracle sorts the returned query results. The Order by statement does not have any special restrictions on the columns to be sorted, and you can add functions to the columns (such as joins or attachments, etc.). Any non-indexed items or expressions that are evaluated in the Order by statement will slow down the query.

Carefully examine the order by statement to find non-index items or expressions, which can degrade performance. The solution to this problem is to rewrite the order by statement to use the index, or to create another index on the column used, while absolutely avoiding the use of expressions in the order by clause.

5. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, not equal, and so on. We can also use and (and), or (or), and not (not). NOT can be used to reverse any logical operation symbol. Here is an example of a NOT clause:

... Where

Not (status

= 'VALID')

If you are using NOT, you should precede the inverted phrase with parentheses and the NOT operator before the phrase. The NOT operator is contained in another logical operator, which is not equal to the () operator. In other words, even if the NOT word is not explicitly added to the query where clause, NOT is still in the operator, as shown in the following example:

... Where status

'INVALID'

For this query, you can rewrite it to not use NOT:

Select

* from employee

Where salary3000

Although the results of the two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes on salary columns, while the first query cannot.

Although the results of the two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes on salary columns, while the first query cannot.

=

We should not only write SQL, but also write excellent SQL. The following is the author's study, excerpt, and summary of some materials to share with you!

(1) choose the most efficient order of table names (valid only in rule-based optimizers):

ORACLE's parser processes the table names in the FROM clause from right to left, and the last table written in the FROM clause (the underlying table driving table) will be processed first. in the case of multiple tables in the FROM clause, you must select the table with the least number of records as the base table. If there are more than 3 table join queries

Then you need to choose a crosstab (intersection table) as the base table.

A crosstab is a table that is referenced by other tables.

(2) the connection order in the WHERE clause.

ORACLE parses WHERE clauses in a bottom-up order. According to this principle, joins between tables must be written before other WHERE conditions.

Conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

(3) avoid using'*'in the SELECT clause:

In the process of parsing, ORACLE will put'*'

Convert to all column names in turn, which is done by querying the data dictionary

This means that it will take more time.

(4) reduce the number of visits to the database:

ORACLE does a lot of work internally:

Parsing SQL statements to estimate the utilization of the index

Bind variables, read data blocks, etc.

(5)

Reset ARRAYSIZE parameters in SQL*Plus, SQL*Forms, and Pro*C

You can increase the amount of data retrieved per database access. The recommended value is 200.

(6)

Use the DECODE function to reduce processing time:

Use the DECODE function to avoid repeatedly scanning the same records or repeatedly joining the same tables.

(7) simple and unrelated database access:

If you have several simple database queries, you can integrate them into a single query (even if there is no relationship between them).

(8) Delete duplicate records:

The most efficient way to delete duplicate records (

Because the ROWID) example is used:

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN (X.ROWID))

FROM EMP X WHERE X.EMP_NO = E.EMP_NO)

(9)

Replace DELETE with TRUNCATE:

When deleting a record in a table, under normal circumstances

The rollback section (rollback segments) is used to store information that can be recovered.

If you do not have a COMMIT transaction, ORACLE will restore the data to the state it was before it was deleted (exactly where it was before the delete command was executed).

And when using TRUNCATE,

The rollback section no longer stores any recoverable information. When the command runs, the data cannot be recovered. As a result, few resources are called and the execution time is very short. (translator's note: TRUNCATE is only applicable for deleting the whole table. TRUNCATE is DDL, not DML).

(10) use COMMIT as much as possible:

Whenever possible, use COMMIT as much as possible in your program

In this way, the performance of the program is improved, and the requirements are reduced because of the resources released by COMMIT:

Resources released by COMMIT:

a. The information used to recover data on the rollback segment.

b. A lock acquired by a program statement

C. Space in redo log buffer

D. ORACLE is used to manage the internal expenses of the above three resources

(11) replace the HAVING clause with Where clause:

Avoid using the HAVING clause, HAVING

The result set is filtered only after all records have been retrieved. This process requires sorting, totals, and so on.

If you can limit the number of records through the WHERE clause, you can reduce the overhead. (in non-oracle) among the three clauses on, where and having that can be conditioned, on is the first to execute, where is the second, and having is the last, because on is the first to put no.

Qualified records can be filtered before statistics, it can reduce the intermediate operation to deal with the data, in theory, the speed should be the fastest, where should also be faster than having, because it filters the data before sum, in the two table join only to use on, so in a table, only where and having comparison. In the case of querying statistics in this single table, if the conditions to be filtered do not involve the fields to be calculated, then their results are the same, except that where can use rushmore technology, while having cannot, and the latter should be slow in speed if the calculated words are involved.

Paragraph, which means that the value of this field is uncertain before it is calculated. According to the workflow written in the previous article, the action time of where is completed before calculation, while having is used only after calculation, so in this case, the results of the two will be different. On works earlier than where in multi-table join queries. First of all, according to the join conditions between the tables, the system combines several tables into a temporary table, then filters it by where, then calculates it, and then filters it by having after calculation. It can be seen that for the filter condition to work correctly, we must first understand when the condition should work, and then decide where to put it.

(12) reduce queries on tables:

In SQL statements with subqueries, special attention should be paid to reducing queries on tables. Example:

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

(13) improve the efficiency of SQL through internal functions.

Complex SQL often sacrifices executive efficiency.

To be able to master the above methods of using functions to solve problems is very meaningful in practical work.

(14) use the alias of the table (Alias):

When joining multiple tables in a SQL statement

Please use the alias of the table and prefix the alias on each Column. In this way, parsing time and syntax errors caused by Column ambiguity can be reduced.

(15) replace IN with EXISTS and NOT IN with NOT EXISTS:

In many queries based on underlying tables, it is often necessary to join another table in order to meet one condition. Under the circumstances,

Using EXISTS (or NOT EXISTS) will usually improve the efficiency of the query.

In a subquery, the NOT IN clause performs an internal sort and merge.

In either case, NOT IN is the least efficient (because it performs a full table traversal on the tables in the subquery).

To avoid using NOT IN, we can rewrite it as an external connection (Outer Joins) or NOT EXISTS.

Example:

(efficient) SELECT * FROM EMP (basic table) WHERE EMPNO > 0 AND EXISTS (SELECT'X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

(inefficient) SELECT * FROM EMP (basic table) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB')

(16) identify the SQL statement of 'inefficient execution':

Although there are a variety of graphical tools for SQL optimization, writing your own SQL tool to solve the problem is always the best way:

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS

ROUND ((BUFFER_GETS-DISK_READS) / BUFFER_GETS,2) Hit_radio

ROUND (DISK_READS/EXECUTIONS,2) Reads_per_run

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS > 0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS) / BUFFER_GETS

< 0.8 ORDER BY 4 DESC; （17） 用索引提高效率： 索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.： ALTER INDEX REBUILD 18） 用EXISTS替换DISTINCT： 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子： (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); （19） sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行 （20） 在java代码中尽量少用连接符"＋"连接字符串！ （21） 避免在索引列上使用NOT 通常，　 我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE"遇到"NOT,他就会停止使用索引转而执行全表扫描. （22） 避免在索引列上使用计算． WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描． 举例: 低效： SELECT … FROM DEPT WHERE SAL * 12 >

25000

Efficient:

SELECT... FROM DEPT WHERE SAL > 25000 Universe 12

(23) replace > with > =

Efficient:

SELECT * FROM EMP WHERE DEPTNO > = 4

Inefficient:

SELECT * FROM EMP WHERE DEPTNO > 3

The difference between the two is that the former DBMS will jump directly to the first record with DEPT equal to 4, while the latter will first locate the record of DEPTNO=3 and scan forward to the first record with DEPT greater than 3.

(24) replace OR with UNION (for index columns)

In general, replacing OR in the WHERE clause with UNION will have a better effect.

Using OR on index columns will result in a full table scan.

Note that the above rules are valid only for multiple index columns. If any column is not indexed

Query efficiency may be reduced because you do not choose OR.

In the following example, indexes are built on both LOC_ID and REGION.

Efficient:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE REGION = "MELBOURNE"

Inefficient:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10 OR REGION = "MELBOURNE"

If you insist on using OR, you need to return the index column with the least number of records at the top.

(25) replace OR with IN

This is a simple and easy-to-remember rule, but the actual execution effect still needs to be tested. Under ORACLE8i, the two execution paths seem to be the same.

Inefficient:

SELECT... . FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

High efficiency

SELECT... FROM LOCATION WHERE LOC_IN IN (10, 10, 20, 30)

(26) avoid using IS NULL and IS NOT NULL on index columns

Avoid using any nullable column in the index. ORACLE will not be able to use the index. For a single-column index, if the column contains a null value, the record will not exist in the index.

For a composite index, if each column is empty, the record also does not exist in the index. if at least one column is not empty, the record exists in the index. for example:

If the uniqueness index is built on columns An and B of the table

And there is a record in the table with A, B value (123), ORACLE will not accept the next record (insert) with the same A, B value (123).

However, if all index columns are empty, ORACLE will assume that the entire key value is empty and empty is not equal to empty.

So you can insert 1000 records with the same key value, but of course they are all empty!

Because null values do not exist in index columns, comparing index columns with null values in the WHERE clause causes ORACLE to deactivate the index.

Inefficient: (index invalidation)

SELECT... FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL

Efficient: (index is valid)

SELECT... FROM DEPARTMENT WHERE DEPT_CODE > = 0

(27) always use the first column of the index:

If the index is built on multiple columns, the optimizer will choose to use the index only if its first column (leading column) is referenced by the where clause.

This is also a simple but important rule. When referencing only the second column of the index, the optimizer uses a full table scan and ignores the index.

28) replace UNION with UNION-ALL (

If possible):

When the SQL statement requires two query result sets to be UNION, the two result sets are merged as UNION-ALL

It is then sorted before the final result is output. If you use UNION ALL instead of UNION

In this way, sorting is not necessary. As a result, efficiency will be improved.

It is important to note that UNION ALL will repeatedly output the same records in both result sets.

Therefore, you still need to analyze the feasibility of using UNION ALL from the business requirements. UNION

The result set will be sorted and the SORT_AREA_SIZE memory will be used for this operation.

It is also very important to optimize this piece of memory. The following SQL can be used to query the sorted consumption

Inefficient:

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE ='31 color DECMI 95'

UNION

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE ='31 color DECMI 95'

Efficient:

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE ='31 color DECMI 95'

UNION ALL

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE ='31 color DECMI 95'

(29) replace ORDER BY with WHERE:

The ORDER BY clause uses the index only under two strict conditions.

All columns in the ORDER BY must be contained in the same index and kept in order in the index.

All columns in the ORDER BY must be defined as non-empty.

The index used in the WHERE clause and the index used in the ORDER BY clause cannot be juxtaposed.

For example:

The table DEPT contains the following:

DEPT_CODE competes for NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

Inefficient: (index is not used)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

Efficient: (using indexes)

SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

(30) avoid changing the type of index column.

When comparing data of different data types, ORACLE automatically performs simple type conversions on columns.

Suppose EMPNO is an index column of a numeric type.

SELECT... FROM EMP WHERE EMPNO = '123'

In fact, after ORACLE type conversion

The statement is converted to:

SELECT... FROM EMP WHERE EMPNO = TO_NUMBER ('123')

Fortunately, type conversion does not occur on index columns, and the purpose of the index has not been changed.

Now, suppose EMP_TYPE is an index column of character type.

SELECT... FROM EMP WHERE EMP_TYPE = 123

This statement is converted by ORACLE to:

SELECT... FROM EMP WHERETO_NUMBER (EMP_TYPE) = 123

This index will not be used because of the internal type conversion!

To prevent ORACLE from implicitly converting your SQL

It is best to show the type conversion explicitly. Note that when comparing characters with numeric values, ORACLE gives priority to converting numeric types to character types

(31) the WHERE clause that needs to be careful:

The WHERE clause in some SELECT statements does not use an index.

Here are some examples.

In the following example, (1)'! = 'will not use the index.

Remember, indexes can only tell you what exists in the table, not what doesn't exist in the table. (2) 'linking' is a character concatenation function.

Like other functions, the index is disabled. (3)'+'is a mathematical function.

Like other mathematical functions, the index is disabled. (4) the same index columns cannot be compared with each other, which will enable full table scanning.

(32) a. If you retrieve the number of records in a table with more than 30% of the data. There will be no significant efficiency gains in the use of indexes.

b. In certain cases, using an index may be slower than a full table scan

But this is the difference of the same order of magnitude. In general, using an index is several or even thousands of times more block than a full table scan!

(33) avoid resource-consuming operations:

The SQL statement with DISTINCT,UNION,MINUS,INTERSECT,ORDER BY starts the SQL engine

Perform resource-consuming sorting (SORT) functions. DISTINCT requires a sort operation

Others need to be sorted at least twice. Usually

SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways.

If the SORT_AREA_SIZE of your database is well provisioned

Using UNION, MINUS and INTERSECT can also be considered.

After all, they are very readable.

(34) optimize GROUP BY:

Improve the efficiency of GROUP BY statements

You can filter out unwanted records before GROUP BY. The following two queries return the same results, but the second one is significantly faster.

Inefficient:

SELECT JOB, AVG (SAL)

FROM EMP

GROUP by JOB

HAVING JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

Efficient:

SELECT JOB, AVG (SAL)

FROM EMP

WHERE JOB = 'PRESIDENT'

OR JOB = 'MANAGER'

GROUP by JOB

= =

= =

If you are working on a SQL Server-based project, or if you are new to SQL Server, you may face some database performance problems, and this article will provide you with some useful guidance (most of which can also be used in other DBMS).

Here, I'm not going to introduce the tricks of using SQL Server, nor can I offer a panacea. What I do is sum up some experience-about how to form a good design. These experiences come from the lessons I have learned over the past few years, and I have seen many of the same design mistakes repeated over and over again.

First, understand the tools you use

Don't underestimate this. This is the most important point I've talked about in this article. You may also see that there are a lot of SQL Server programmers who don't know all the T-SQL commands and the useful tools provided by SQL Server.

"what? I'm going to waste a month learning SQL commands that I'll never use." Oh, you might say that. That's right. You don't have to do that. But you should browse all the T-SQL commands in one weekend. Your task here is to understand that in the future, when you design a query, you will remember, "by the way, there is a command that can fully implement the function I need," so go to MSDN

Check the exact syntax of this command.

Do not use cursors

Let me repeat: do not use cursors. If you want to destroy the performance of the entire system, they are your most effective first choice. Most beginners use cursors without realizing their impact on performance. They take up memory, lock tables in their incredible ways, and they are like snails. And worst of all, they make all the performance optimizations your DBA can do equal to none. Do you know that every execution of FETCH is equal to the execution of a SELECT command? This means that if your cursor has 10000 records, it will execute SELECT 10000 times! If you

It would be much more efficient to use a set of SELECT, UPDATE, or DELETE to do the corresponding work.

Beginners generally think that using cursors is a familiar and comfortable way of programming, but unfortunately, this can lead to poor performance. Obviously, the overall goal of SQL is what you want to achieve, not how to achieve it.

I once rewrote a cursor-based stored procedure with T-SQL. The table had only 100000 records. The original stored procedure took 40 minutes to execute, while the new stored procedure only took 10 seconds. Here, I think you can see what an incompetent programmer is doing!

We can write a Mini Program to get and process the data and update the database, which is sometimes more efficient. Remember: there is nothing T-SQL can do about loops.

Let me remind you again: using cursors is no good. Apart from DBA's work, I've never seen anything done effectively with cursors.

Standardize your data sheet

Why not standardize the database? There are probably two excuses: for performance reasons and simply for laziness. As for the second point, you will have to pay for it sooner or later. When it comes to performance, you don't need to optimize things that aren't slow at all. I often see programmers "de-normalize" databases on the grounds that "the original design is too slow", but the result is often that they make the system slower. DBMS is designed to handle canonical databases

Therefore, remember: design the database in accordance with the requirements of standardization.

Do not use SELECT *

It's not easy to do. I know it too well, because I often do it myself. However, if you specify the columns you want in SELECT, you will have the following benefits:

1 reduce memory consumption and network bandwidth

2 you can get a safer design

3 give the query optimizer the opportunity to read all the required columns from the index

Understand what you are going to do with the data

It's a virtue to create a robust index for your database. But to do this is simply an art. Every time you add an index to a table, SELECT gets faster, but INSERT

And DELETE are much slower because creating a maintenance index requires a lot of extra work. Obviously, the crux of the question here is what you want to do with this table. This problem is not easy to grasp, especially when it comes to DELETE and UPDATE, because these statements often contain SELECT commands in the WHERE section.

Do not create an index for the "gender" column

First, we must understand how indexes speed up access to tables. You can think of an index as a way to partition a table based on certain criteria. If you create an index on a column like "gender", you simply divide the table into two parts: male and female. You are dealing with a table with 1000000 records. What is the point of such a division? Remember: maintaining an index is time-consuming. When you design an index, follow this rule: rank from more to less according to the number of columns that may contain different contents, such as name + province + gender.

VII. Use of transactions

Use transactions, especially if the query is time-consuming. If there is something wrong with the system, this will save your life. Generally speaking, some experienced programmers will know that you will often encounter unexpected situations that will cause stored procedures to crash.

Watch out for deadlocks

Access your watch in a certain order. If you lock table A first and then table B, lock them in this order in all stored procedures. If you (inadvertently) lock table B first and then table An in a stored procedure, this may lead to a deadlock. Deadlocks are not easy to find if the locking sequence is not designed in detail in advance.

Do not open large datasets

One question that is often asked is: how can I quickly add 100000 records to ComboBox? It's not right. You can't and you don't have to. Quite simply, your user will curse you if he has to browse through 100000 records to find the one he needs. What you need here is a better UI, and you need to display no more than 100 or 200 records for your users.

Do not use server-side cursors

Compared with server-side cursors, client-side cursors can reduce the system overhead of the server and network, and also reduce the locking time.

11. Use parameter query

Sometimes I see questions like this in the CSDN technology forum: "SELECT * FROM a WHERE a.id='A'B, what should I do because there is an exception in the single quote query?" The common answer is to use two single quotation marks instead of single quotation marks This is wrong. This will cure the symptoms rather than the root cause, because you will encounter such problems on some other characters, not to mention that it will lead to serious bug, in addition, it will also make SQL

Server's buffering system does not work as it should. Using parameter queries to get to the bottom of the problem, all of these problems do not exist.

Use a database with a large amount of data when coding a program

The test database used by programmers in development is generally small, but often the end user has a large amount of data. What we usually do is wrong, and the reason is simple: hard drives are not very expensive now, but why do performance problems wait until they are irreparable?

Do not use INSERT to import large amounts of data

Please don't do this unless it's necessary. Use UTS or BCP, so you can have both flexibility and speed at one stroke.

Pay attention to the problem of timeout

When querying a database, the default of the database is generally small, such as 15 seconds or 30 seconds. Some queries take longer to run, especially when the amount of data in the database is getting larger and larger.

Don't ignore the problem of modifying the same record at the same time

Sometimes, two users modify the same record at the same time, so that the latter modifier modifies the operation of the previous modifier, and some updates are lost. Dealing with this situation is not difficult: create a timestamp field, check it before writing, merge changes if allowed, and prompt the user if there is a conflict.

Do not execute SELECT MAX (ID) on the main table when inserting a record in the detail table

This is a common error that can lead to errors when two users insert data at the same time. You can use SCOPE_IDENTITY,IDENT_CURRENT and IDENTITY. If possible, do not use IDENTITY, because it can cause some problems in the case of triggers (see the discussion here for details).

Avoid setting the column as NULLable

If possible, you should avoid setting the column to NULLable. The system allocates an extra byte to each row of the NULLable column, resulting in more system overhead when querying. In addition, setting the columns to NULLable complicates the coding because they must be checked every time they are accessed.

I'm not saying that NULLS is the source of the trouble, although some people think so. I think setting the column to NULLable sometimes works well if you allow "empty data" in your business rules, but if you use NULLable in situations like the following, you're asking for it.

CustomerName1

CustomerAddress1

CustomerEmail1

CustomerName2

CustomerAddress2

CustomerEmail3

CustomerName1

CustomerAddress2

CustomerEmail3

If this happens, you need to standardize your watch.

Try not to use the TEXT data type

Unless you use TEXT to deal with a large amount of data, don't use it. Because it is not easy to query, slow, not good use will waste a lot of space. In general, VARCHAR can handle your data better.

Try not to use temporary watches

Try not to use temporary watches unless you have to. Generally, subqueries can be used instead of temporary tables. Using temporary tables brings overhead, and if you are programming in COM+, it can also cause you a lot of annoyance, because COM+ uses database connection pooling while temporary tables exist from beginning to end. SQL Server provides some alternatives, such as the Table data type.

Learn to analyze and inquire

SQL Server query Analyzer is your good partner, through which you can understand how queries and indexes affect performance.

21. Use referential integrity

Defining keys, uniqueness constraints, and foreign keys can save a lot of time.

=

[IT168 technical documentation] everything has its source. To solve the problem, you have to start from the source. There are many sources that affect the performance of ORACLE, including the following aspects: hardware configuration of database: CPU, memory, network conditions.

1. CPU: in any machine, the data processing capacity of CPU is often a symbol of computer performance, and ORACLE is a database system that provides parallel capabilities, so the requirements in CPU are even higher. If the number of run queues exceeds the number of CPU processes, the performance will decline. The problem we need to solve is to increase the number of CPU appropriately. Of course, we can also KILL processes that require a lot of resources.

two。 Memory: another measure of machine performance is the amount of memory. The memory in ORACLE is exchanged with the swap area in our database under construction. When reading the data, the disk Imax O has to wait for the physical Imax O operation to be completed. When there is a memory bottleneck in ORACLE, the first thing we need to consider is to increase memory, because the response time of ORACLE O is the main parameter affecting ORACLE performance. I will explain this in detail.

3. Network conditions: NET*SQL is responsible for the flow of data on the network, a large number of SQL will slow down the network. For example, 10m network cards and 100m network cards have a very obvious impact on NET*SQL, as well as switches, hubs and other network equipment performance has an obvious impact on the network. It is recommended that you do not try to use three hubs to interconnect network segments in any network.

Setting of OS parameters

The following table shows the parameter settings and instructions for OS. DBA can set these parameters according to actual needs.

Kernel parameter name

Description

Bufpages

The buffer space is not allocated statically, but dynamically allocated, so that the bufpages value is dynamically allocated to the buffer space along with the nbuf.

Create_fastlinks

Allow fast symbolic links for the HFS file system

Dbc_max_pct

Increase the maximum dynamic buffer space as a percentage of physical memory to meet the read-write hit ratio of the application system.

Dbc_min_pct

Set the minimum dynamic buffer space as a percentage of physical memory

Desfree

Raise the minimum free memory limit to start swapping operations to ensure the stability of the system and prevent unforeseen system crashes (Crash).

Fs_async

Allow disk asynchronous operations to improve CPU and disk utilization

Lotsfree

Increase the upper limit of the free memory for the system to release the paging operation to ensure that the application has enough available memory space.

Maxdsiz

In view of the large amount of data in the system, the size of the most big data section is increased to meet the needs of the application. (32-bit)

Maxdsiz_64bit

Maximum process data segment size for 64_bit

Maxssiz

Increase the size of the maximum stack segment. (32_bit)

Maxssiz_64bit

Increase the size of the maximum stack segment. (64_bit)

Maxtsiz

Increase the maximum code segment size to meet the application requirements

Maxtsiz_64bit

The original value is too large and should be adjusted small.

Minfree

Increase the upper limit of free memory to stop swapping operations

Shmem

Allow memory sharing to improve memory utilization

Shmmax

Set the size of the maximum shared memory segment to fully meet current needs

Timeslice

Since the bottleneck of the system is mainly reflected in the disk I _ peg O, reducing the size of the time slice, on the one hand, can avoid the waiting of CPU caused by the disk I _ hand O is not smooth, thus improving the comprehensive utilization of CPU. On the other hand, the blocking amount of the process is reduced.

Unlockable_mem

The size of the unlocked memory is increased, and the memory space available for paging and swapping is expanded to meet the memory management requirements of the system.

User SQL quality

The above is all about hardware. Under limited conditions, we can adjust the SQL quality of the application:

1. Do not perform full table scans (Full Table Scan): full table scans result in a large number of Icano

two。 Try to build and use indexes well: indexing is also fastidious, and when indexing, the more indexes the better. When a table has more than 4 indexes, the performance of ORACLE may not be improved, because the performance of OLTP system degrades when each table has more than 5 indexes, and Oracle can never use more than 5 indexes in a sql. When we use GROUP BY and ORDER BY, ORACLE will sort the data automatically, and ORACLE determines the size of the sort_area_ size area in INIT.ORA. When sorting cannot be done in our given sorting area, ORACLE will sort on disk, that is, in the temporary table space.

Too much disk sorting will make the value of free buffer waits higher, and this range is not just for sorting. I would like to give the following advice to developers:

1) the subquery in the select,update,delete statement should regularly find less than 20% of the table rows. If a statement looks for more than 20% of the total number of rows, it will not be able to improve performance by using indexes.

2) the index may be fragmented because when the record is deleted from the table, it is deleted from the index of the table accordingly. The space freed by the table can be reused, while the space freed by the index can no longer be used. Indexed tables with frequent deletions should be rebuilt periodically to avoid causing space debris and affecting performance. Under permissible conditions, you can also periodically truncate the table. The truncate command deletes all records in the table, as well as index fragments.

3) when using the index, be sure to refer to the corresponding fields in the order of the index.

4) using (+) is more efficient than using NOT IN.

Reduce ORACLE competition:

Let's start with a few parameters of ORACLE, which are related to the competition of ORACLE:

1), freelists and freelist groups: they are responsible for the spatial management of ORACLE processing tables and indexes

2), pctfree and pctused: this parameter determines the behavior of freelists and freelist groups, and the sole purpose of the pctfree and pctused parameters is to control how blocks move in and out of freelists

Setting up pctfree and pctused is very important for block removal and reading in freelists.

Settings of other parameters

1), including the SGA area (system global area): the system global area (SGA) is a control information memory segment assigned to the Oracle database containing an Oracle instance.

Mainly includes database cache (the database buffer cache)

Replay log cache (the redo log buffer)

Shared Pool (the shared pool)

Data dictionary cache (the data dictionary cache) and other aspects of information

2) the data accessed by db_block_buffers (data high-speed buffer) is placed in this area of memory. The larger the parameter, the more likely it is that Oracle will find the same data in memory, that is, it will speed up the query.

3), share_pool_size (SQL shared buffer pool): this parameter is the cache of the library cache and the data dictionary.

4), Log_buffer (replay log buffer)

5), sort_area_size (sort area)

6), processes (number of processes connected simultaneously)

7), db_block_size (database block size): Oracle defaults to 2KB, which is too small, because if we have a 8KB data, the database of 2KB block needs to read disk 4 times before it can be read, while the database of 8KB block only needs to be read once, which greatly reduces the number of Icano operations. After the database installation is complete, you can no longer change the value of db_block_size. You can only re-establish the database and choose to install the database manually when building the database.

8), open_links (number of links opened at the same time)

9), dml_locks

10), open_cursors (number of open cursors)

11), dbwr_io_slaves (number of backend writing processes)

6. IN and EXISTS

Sometimes a column is compared to a series of values. The easiest way is to use subqueries in the where clause. You can use subqueries in two formats in the where clause.

The first format is to use the IN operator:

... Where

Column in (select

* from...

Where.)

The second format is to use the EXIST operator:

... Where

Exists (select

'X'

From... where...)

This is the end of this article on "what are the principles of SQL optimization?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.