Performance testing Common SQL Tips _oracle

For a period of time performance test, put oneself in the performance test process, use the Oracle used in the SQL statement to organize, do a memo;

(1) Multiple fields are spliced in some format

"||" string concatenation characters;

Example: concatenation of "id" and "code" with commas: select T.id| | ', ' | | T.code from otable t;

Note: MySQL uses the concat () function, select Concat (t.id, ', ', t.code) from mtable t;

(2) Count the number of dimensions in a field

Count + GROUP by;

Example: Statistics on the number of data for a creator: select T.creatorname,count (1) from otable t (where you omit the Where Condition query) group by T.creatorname;

(3) Statistics of the number of data generated per second and sorted by quantity from large to small-----------results are often used to determine the reference value of the performance test target TPS;

Count + GROUP by + order by; (Modify the format of TO_CHAR in the following SQL to modify the precision to min or hour)

Example: Select To_char (t.createtime, ' yyyy-mm-dd hh24:mi:ss '), COUNT (1) from otable t where

To_char (T.createtime, ' yyyy-mm-dd hh24:mi:ss ') >= ' 2016-01-01 00:00:00 ' and

To_char (T.createtime, ' yyyy-mm-dd hh24:mi:ss ') <= ' 2016-12-31 23:59:59 '

Group BY To_char (T.createtime, ' yyyy-mm-dd hh24:mi:ss ')

Order by count (1) desc;

MySQL Implementation method:

date_format+ GROUP by + order by; (Modify the second parameter in the Date_format to modify the precision to min or hour)

Example: Select Date_format (start_time, '%y%m%d%h%i%s ') Isecond,count (ID) as icount from Mtable t where

T.start_time between ' 2017-06-01 00:00:00 ' and ' 2017-06-01 23:59:59 '

GROUP BY Isecond

ORDER BY icount Desc;

Performance testing Common SQL Tips _oracle