Make your SQL run faster

----people in the use of SQL often fall into a misunderstanding, that is too focused on the results are correct, and ignore the different implementation methods may exist between
Performance variance, which is particularly evident in large or complex database environments, such as online transaction processing OLTP or decision support system DSS
Explicitly In the work practice, the author finds that bad SQL often comes from improper index design, inadequate connection condition and the WHERE clause which is not optimized. In the
After they have been properly optimized, their operating speed has been significantly improved! I will summarize these three aspects separately:
----to illustrate the problem more intuitively, the SQL run time in all instances is tested, and no more than 1 seconds is represented as (< 1 seconds).
----test environment--
----Host: HP LH II
----Frequency: 330MHZ
----Memory: 128 MB
----Operating system: Operserver5.0.4
----Database: Sybase11.0.3
First, unreasonable index design
----Example: The table record has 620000 rows, and the following SQL runs under different indexes:
----1. A non-clustered index was built on date
Select COUNT (*) from the record where date >
' 19991201 ' and date < ' 19991214 ' and amount >
2000 (25 seconds)
Select Date,sum (amount) from record group by date
(55 seconds)
Select COUNT (*) from the record where date >
' 19990901 ' and place in (' BJ ', ' SH ') (27 seconds)
----Analysis:
----date has a large number of duplicate values, the data is physically randomly placed on the data page under Non-clustered indexes, and a table scan must be performed once the range is searched
To find all the rows in this range.
----2. A clustered index on date
Select COUNT (*) from the record where date >
' 19991201 ' and date < ' 19991214 ' and amount >
2000 (14 seconds)
Select Date,sum (amount) from record group by date
(28 seconds)
Select COUNT (*) from the record where date >
' 19990901 ' and place in (' BJ ', ' SH ') (14 seconds)
----Analysis:
----under the cluster index, the data is physically sequentially on the data page, and the duplicate values are grouped together, so that when you look in the range, you can first find the
The end point, and only in this range scan data pages, avoid a large range of scanning, improve the query speed.
----3. Combined index on Place,date,amount
Select COUNT (*) from the record where date >
' 19991201 ' and date < ' 19991214 ' and amount >
2000 (26 seconds)
Select Date,sum (amount) from record group by date