Differences between SQL Server and Oracle database in query optimization (II.)

Query optimization on SQL statements.

The foreground application works on the database server, and ultimately relies on the SQL statements in the application. According to incomplete statistics, SQL statements consume about 80% of the database server's resources. Therefore, how to improve the execution efficiency of SQL statements is a problem that must be considered in database query optimization.

However, in the actual work, many programmers have a misunderstanding, they think that database query optimization is not their business, but should be the task of the database management system, which is a misconception of many programmers. They mistakenly believe that the performance of the applications they develop is not related to the SQL statements they write. A good query statement can often increase the performance of the application by dozens of times times, and as the record volume increases, the effect will increase exponentially.

In addition, SQL statements are independent of program design logic, that is, no matter how your business logic is designed, and finally decomposed into SQL statements, is a few syntax, so, relative to the application source code optimization, SQL query statement optimization in time and risk, are much lower.

For the optimization of SQL statements, there are similarities and differences between Oracle databases and SQL Server databases. The following is a comprehensive introduction to the differences and similarities between the two.

1, through the index to improve the execution efficiency of SQL statements.

In general, for some tables that often need to be queried, such as product information tables, we can improve query efficiency by establishing foreign keys. However, it does not mean that each field is specified as a foreign key. For fields that do not specify foreign keys, we can index them to improve the query efficiency of the datasheet.

Generally, in the following cases, we can index the table to improve the execution efficiency of the SQL statement.

First, for some tables that need to be queried frequently, we do not set a foreign key for some reason, but instead set the index to increase query efficiency for the table. In database tables, the setting of foreign keys is often limited, and the index is much less restrictive than the foreign key. So, in the case of not using a foreign key, we can use the index to improve the query efficiency for the table.

Second, in the need to frequent sorting or grouping of tables, indexing, can greatly improve query efficiency. If the ERP system is designed, it may be necessary to inquire frequently about the purchase order details, and this report needs to be sorted according to the number of the purchase order. In this case, in the database design, you can set the number of purchase orders as an index, each time the purchase order details operation, the performance of the front-office ERP program will be much higher. And sometimes, may need to according to the supplier to calculate the day's purchase amount, at this time, it is best to be in the purchase schedule, to add the index to the Supplier field, which to improve the day of Purchase summary table operation Efficiency, will have a very big help. All in all, setting up indexes is an important function to improve the overall performance of the application on the table of grouped queries or sorted queries.

Third, if there are multiple columns to be sorted, you need to set up a composite index on those columns. The foreground application needs to be sorted by vendor, purchase order number, and product number, when it generates the day's purchase schedule. At this point, in other words, when the report is generated for the inventory schedule, the three fields are sorted. When this happens, it is a good choice to build a composite index of these fields to improve the efficiency of the query.

These SQL statements are optimized and can be implemented both by Oracle databases and SQL Server databases. Although the specific implementation statements may be slightly different, but are the same, there is no essential difference.

2, the index and data files stored in different disks.

When indexes or database files are large, putting them on the same disk increases the competition of input and output, thus offsetting the effect of indexing. In order to solve the problem of low input and output efficiency caused by too many indexes, it is best to build the index and the user's table space on different disk in the database design. If the table space of the database is built on a hard disk, the index is built on another hard disk. In this way, the input and output competition can be significantly reduced. In other words, this design, as the index increases, does not result in low input and output efficiency.

However, according to the author's understanding, the index and data files are stored on different disks, it seems that only the Oracle database can do, and Microsoft's SQL Server database does not achieve this.

This may be the root of their different positioning. Oracle's database system is designed for large database applications, so the efficiency of the query is more demanding.