A drop of performance testing-SQL Optimization 1

When we talk about SQL optimization, we will fell afraid about it as it is a very senior job needing senior ones to do. But I will give the answer: NOT. Just raise up you courage and face it.

     About SQL Optimization, we can know about it  through many little sides. Through my job undergoing, I  will describe it in many separate items. (take database ORACLE as instance)

 

Item 1:

   

       When a SQL statement  in using have many ‘FROM’  tables,  then we should sort the tables list in a rule that: one table whose records number is minimum should be placed in last position of the list.

Because SQL interpreter  read table from the right to the left, then put the little table as basic table can reduce the connection internal  tables.

     For example :

 sql1:

select qhdc.origin_asset,qhdc.quote_name, qidc.total_cost,qidc.trn_id from quote_header_dc4 qhdc,quote_item_dc4 qidc
where qhdc.quote_number=qidc.quote_number and rownum<3000;

sql2:

 select qhdc.origin_asset,qhdc.quote_name, qidc.total_cost,qidc.trn_id from quote_item_dc4 qidc ,quote_header_dc4 qhdc
where qhdc.quote_number=qidc.quote_number and rownum<3000;

 

but records in qidc in larger than that in qhdc table. So we could clearly conclude that: sql2 is more better than sql on performance. And the test result is the same:

the same to select 3000records, sql1 costs 8.705 second  vs sql2  8.503 second.

 

to be continue: