SQL index Usage (RPM)

The data tested below is 3,852,916 records.
The test environment is os:windows XP SP2, Memory: 1G,CPU: Dual-core 2.66 GHZ.

(1) Itemtransaction table has nothing, no primary key, no foreign key, no index.
DECLARE @d datetime
Set @d=getdate ()

SELECT * FROM Itemtransaction
where logdate> ' 2008-9-1 ' and logdate< ' 2009-7-1 '

Select ' Yongshi: ' =datediff (Ss,@d,getdate ())

Duration: 278 seconds (i.e.: 4 minutes 39 seconds)

(2) Nonclustered indexes are only created on logdate in the Itemtransaction table.

DECLARE @d datetime
Set @d=getdate ()

SELECT * FROM Itemtransaction
where logdate> ' 2008-9-1 ' and logdate< ' 2009-7-1 '

Select ' Yongshi: ' =datediff (Ss,@d,getdate ())

Duration: 182 seconds (i.e.: 3 minutes 04 seconds)

Fast 1 minutes and 30 seconds, the speed is not as fast as expected, it seems to build a clustered index on the longdate. Requirements that do not conform to the clustered index are "neither the vast majority of the same nor the very few identical." Analyze this watch.
The total number of data is 3852916, but logdate data is only 360 a year (because Logdate is time, only one time a day,
There are only 360 articles a year. Compared with 3 million of the data, it can only be very small. General 1:200 is more appropriate.

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
(3) Create a logdate clustered index in the Itemtransaction table.

DECLARE @d datetime
Set @d=getdate ()

SELECT * FROM Itemtransaction
where logdate= ' 2009-4-1 ' and employeeno=804562132
Select ' Yongshi: ' =datediff (Ss,@d,getdate ())

Duration: 45 seconds

(4) Creates a Logdate,employeeno composite clustered index in the Itemtransaction table.

DECLARE @d datetime
Set @d=getdate ()

SELECT * FROM Itemtransaction
where logdate= ' 2009-4-1 ' and employeeno=804562132
Select ' Yongshi: ' =datediff (Ss,@d,getdate ())

Duration: 40 seconds

(5) Creates a Logdate,employeeno composite clustered index in the Itemtransaction table.

DECLARE @d datetime
Set @d=getdate ()

SELECT * FROM Itemtransaction
where employeeno=804562132
Select ' Yongshi: ' =datediff (Ss,@d,getdate ())

Duration: 55 seconds

By comparison of these three statements: (3) There is only one logdate index, and (4) is the conforming index of Logdate and Employeeno
The Where condition is the same. The speed of the query is similar. However, the query data is slow from (5). In comparison with (4), the order of the conditions in where is changed, and the change in the sequence of the formal is caused. This is because the logdate in a composite clustered index is different from the order of Employeeno. My order is logdate is the first, Employeeno is the second place. This index slows down when you are not using the index's starting column as the query criteria.
Doesn't even work.

-----------------------------
The so-called index is to save some key-value pairs in other places, the key is the index column, the value is the address pointer. and are arranged in order.

(5) Comparison of union and or in Employeetransaction table

With OR:
SELECT * FROM Employeetransaction
where logdate> ' 2009-1-1 ' or prodgroup= ' d1g '

Time: 03 minutes: 05 Seconds

With Union.
SELECT * FROM Employeetransaction
where logdate> ' 2009-1-1 '
Union
SELECT * FROM Employeetransaction
where prodgroup= ' d1g '

Time: 03 minutes: 41 seconds

There is no index on the logdate,prodgroup. It seems that the speed of the two is not much different.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/wobuwei/archive/2009/08/26/4486105.aspx

SQL index Usage (RPM)