Remember the database optimization is not bad

There is a business in the recent project that requires a circular operation of hundreds of tables, starting with the execution of the results to return time to 1.5.

Accidentally synchronizing the database to another machine, after the connection found that the execution time of less than 10 seconds, very confused. Excluding network and machine configuration reasons, it is still not clear what is the difference between the previous database and the synchronized database.

Monitor the execution of the code through stopwatch and use log4net to output the results to a file. After the record analysis found that some of the table query time is longer, because the data in the table is too much, there is no good way.

By searching the database optimization method, we get some simple suggestions, the central idea is to avoid full table scan. For example, to index groups or conditional filter columns, do not use calculation expressions for columns (such as: where NUM/2 > 1, should be written in num > 2), and so on. After parsing the SQL statements, the following optimizations were made:

1. The order of related conditions in conditional statements is adjusted.
2. The judgment of the number column is placed in front, and the judgment of the character columns is placed behind;
3. Index groups or conditional filter columns.

The end result is that when the filter range is less than an hour, the statement executes faster, the results are returned within five seconds, and when the filter range is slightly larger, the execution speed is almost no different than before optimization. So guess, the index is not appropriate, or the index is not the bottleneck that caused the query to be too slow.

Later, using SQL Server's own profier, see:

Remember to tick save to file or save to table, and select tab page to set up related events. When you start running, you can monitor the execution of your SQL statements.

In the Profier monitoring process, the previous circular multi-table query was executed. When the execution is complete, click the Red Quartet button to stop monitoring, see:

Then open Database Engine Tuning Advisor, browse SQL Server profier trace the generated file or table, select the database for workload analysis, and the database that needs to be optimized:

After the analysis is complete, review the analysis report, pick out the time-consuming operations, and refer to the recommendations for optimization.

After the trace file analysis is complete, the query for some of the tables takes a long time, and the recommendation of the Tuning Advisor is to index the multiple column combinations later, rather than independently indexing the grouping or conditional filter columns, respectively.

The index is indexed by reference to the Tuning Advisor, and after the database part of the table is indexed, when the filter range is less than an hour, the statement executes faster, the result is returned within five seconds, and the execution speed is basically within 10 seconds when the filter range is slightly larger; The execution speed is basically maintained between 20-30 seconds. The effect of optimization is obvious, but the time consumption is still relatively large compared with the database which has not been optimized by synchronization. After asking someone, you know that the database that is synchronized is logically the same as the original database (that is, the database structure is the same), but the physical structure (that is, the file organization structure) is different. By performing a (pre-) Shrink operation on the database, it is found that the original database data and the log free space is less than 5%, the database data synchronized and the log free space 20%---40%. A preliminary guess is that the lack of free space in the original database is also a cause of slow queries. Without understanding how to artificially increase the amount of free space available in the database (typically by increasing the free space through the database's self-growth), there is no optimization implemented from this aspect.

Of course, the database now has the space to optimize, but the understanding is still not enough depth, can only be optimized to this point. Welcome to discuss with the garden friends.

Remember the database optimization is not bad