SQL improves performance

SQL improves performance:

1. index creation of external keys, significantly improving performance when there is a large amount of data

2. Try to use inner join as little as possible. Using left join is a better choice.

3. Create an index for the where condition. The column order in the where condition is consistent with the index order. If you use a prefix fuzzy query, the index is invalid.

4. Sometimes union is more efficient than join.

5. Sometimes user-defined functions are more effective than long SQL statements.

6. Use the exists (not exists) function to replace in (not in), which significantly improves the timeliness of large data volumes.

# Too many indexes. Too many indexes will slow down insert, update, and delete operations.

Note the following when using SQL in C:

1. Establish as few connections as possible

2. Execute as few commands as possible

3. transaction should be started as soon as possible and terminated as soon as possible

4. Use prefix fuzzy search as few as possible

5. Use stored procedures if necessary

 

Use the exists (not exists) function to replace in (not in) to improve execution efficiency.

Select roomtypeid from roomtype where roomtypeid not in
(Select roomtype from fun_selectprice ('2017-10-28 ', '2017-12-11', 2007 ))

Select roomtypeid from roomtype where not exists
(Select * From fun_selectprice ('2017-10-28 ', '2017-12-11', 2007) where roomtypeid = roomtype)