Paip. program function time performance optimization and SQL statement Optimization

Paip. program function time performance optimization and SQL statement Optimization

 

Author attilax
,
1466519819@qq.com

 

Tracking Tool xdebug ..
1

Profiler log analysis tool wincachegrind.
2

Obtain the complete SQL statement from the code for testing...
2

Test SQL statement time...
2

Disable SQL cache...
2

SQL statement optimization tool SQL tuning & Lecco SQL exper2

Manual Optimization process and ideas...
3

SQL Statement Formatting tool...
4

Debugging error SQL statement...
4

SQL statement complex condition priority formatting explain ..
4

See...
4

 

 

Sometimes, a feature may take 26 seconds to complete. I think there is a problem with performance. How can we optimize the performance ..

ASP. Net website, you can use the code analysis tool vs2010 to find the node...

Java website, you can use the corresponding tools

There are also corresponding tools for PHP. Here we use PHP as an example ..

 

Tracking Tool xdebug

First, we need to track the methods in the code that have encountered problems. We need a tracing tool. Here we use xdebug as an example ..

Enable profiler ::

Xdebug. profiler_enable = on

Xdebug. profiler_output_dir = "D: \ xdebug"

Xdebug. profiler_output_name = "script"

; Xdebug. profiler_aggregate = on

Xdebug. profiler_append = on

Xdebug. profiler_enable_trigger = on

 

Note: Do not set the xdebug. profiler_aggregate parameter. Otherwise, no error is reported when Apache is started, but the website cannot be accessed .. My XD version is v2.1.0 and PHPV
5.2.5, Apache version 2.2.x

It generates detailed logs, although human readable, but for better analysis, an analysis tool needs to be loaded.

 

If the code language used does not have a good tracking tool, you can use the time function to calculate the time value using the binary lookup method .. Generally, performance is poor when an SQL statement is called.

 

Profiler log analysis tool wincachegrind

Switch to the overall tab page to view the number of function calls and total time .. sort by total time consumed ..

Select one of the above items. The following shows the number of lines of code files that call this function in detail ..

Find the function with the longest time ..

 

Return to the top, select this function, and the detailed call information and number of lines of the file code page will be displayed below.

 

In this way, the result is an SQL statement call error.

 

Obtain the complete SQL statement from the code for testing.

The SQL statements in the Code are often incomplete, and there are various variables. Load them with a debugger and break the breakpoint. After interruption, watch express functions to obtain the complete SQL statement.

Test SQL statement time

It can be placed in the GUI manager to get the execution time of the SQL statement, for example, 13 seconds .. if the second execution time is greatly shortened, it is because the cache is enabled on the server. You can use the keyword SQL _no_cache to disable the cache of this SQL statement.

Disable SQL Cache

Because the website and SQL Server usually have cache, it will affect the tracking and needs to be disabled ..

Or delete the cached file in the directory... You can use the svn red exclamation point to find and delete the latest modified file.

Restart the SQL Server and clear the SQL Cache

You can also use the keyword "select SQL _no_cache count (*) from
Xxx "to use no cache in SQL statements

 

 

SQL statement optimization tool SQL tuning &Lecco SQL exper

Lecco SQL expert artificial intelligence automatic SQL Optimization

Lecco SQL expert adopts this artificial intelligence technology. Its SQL expert supports Oracle, Sybase, and Ms
SQL Server and IBM DB2 database platform. Its prominent feature is the automatic optimization of SQL statements. In addition, you can use the AI knowledge base "feedback-based search engine" to rewrite SQL statements and find all equivalent SQL statements and possible execution plans, automatically find the best performance SQL statements for applications and databases through test run

 

 

 SQL Tuning
Introduction
SQL turning is a tool in quest central software developed by quest.
Quest Central (figure 1) is an integrated, graphical, and cross-platform database management solution that allows you to manage both Oracle and DB2
And SQL Server databases. It contains the following tools:
? Database Management (DBA)
? Database monitoring (Monitoring pack)
? Spotlight diagnostics)
? Database Analysis)
? SQL Tuning)
? Space Management)
? Load Generator)
? Data Generator)
? PL/SQL Development (TOAD)

 

Reference: SQL optimization tool _ Chenxi sunshine _ Sina Blog

 

 

Manual Optimization process and ideas

Manual Optimization is required when there is no suitable automation tool

You can use the explain function to view the captured rows. If there are too many rows, the performance will be affected ..

 

A. Left join multi-table join?

Check whether the SQL statement is connected to multiple tables, especially left join (more than two tables )..

If left join is used to join two tables, performance is generally not affected .. However, the connection between the three tables will greatly affect the performance.

You can first connect two or two SQL statements and then combine them .. In this process, you need to use the SQL formatting tool for debugging ..

 

After such optimization, the SQL Execution time is reduced from 13 seconds to 50 ms. Success ..

 

B. Split and then Union

If it is not left join, the best way is to split the SQL statements one by one and execute them separately to find the cause .. Use Inner join for join, and then separate union ..

 

 

 

C. Execute Multiple SQL statements using code.

If there is no way in SQL, you can split the SQL statement into multiple small statements, and then execute them separately with the code, and then perform union at the program end.
, Sort or something .. You can ..

SQL Statement Formatting Tool

Http://www.orczhou.com/sqlparser/sqlbeautifier/index.php

This online tool is very powerful. Incorrect SQL statements can also be formatted .. Syntax keyword highlighting ..

 

It is for MySQL, which is written by the author from phpMyAdmin, PHP-SQL-format
,

 

Sqlpp can only format correct SQL statements

 

 

Incorrect SQL statement debugging

 

Because the format of complex SQL query statements is often unclear, the SQL executor reports errors only on which line it is, and it is difficult to find out the cause ..

 

For statements with complex conditions, after formatting, place them in the MySQL GUI tool
, You can easily get the row number of the wrong statement

 

SQL statement complex condition priority formatting explain

Sometimes, some complex statements have many conditions, and
, Or, and so on. If you cannot tell the priority, You Can format it, debug it correctly, and then use the explain function to obtain an SQL statement with a formatted condition priority .. The condition priority is enclosed in correct brackets. Now we can see the priority.

 

Reference

Automatic Optimization of SQL statements _ MySQL _ China Network Management Alliance bitscn_com_php.mht

MySQL high-speed cache-
Wang Huida-51cto technical blog

SQL formatting tool set _ MySQL _ script house

SQL optimization tool _ Chenxi sunshine _ Sina Blog