Database and Query statement optimization

1. Refer to the following, see the database and query statements there is no place to optimize
How to 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 possible performance differences between different implementations, which differ in large or complex databases
In the environment, such as online transaction processing OLTP or decision support system DSS, is particularly evident. The author in the work Practice
found that bad SQL often comes from improper indexing design, inadequate connectivity conditions, and an whe
The RE clause. After the proper optimization of them, the speed of their operation has been significantly improved! Below I will be from these three
Aspects are summarized separately:
----in order to illustrate the problem more intuitively, the SQL runtime in all instances has been tested for no more than 1 seconds
expressed 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, which are physically randomly stored on a data page under a non-clustered index,
Range lookup, you must perform a table scan to find all the rows in the 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 in the van
When looking around, you can find the starting point of this range, and only scan the data page in this range, avoiding the big fan
The scanning speed is improved.
----3. Combined index on Place,date,amount
Select COUNT (*) from the record where date >