9, the field extraction should be "SELECT *" in accordance with the principle of "how much to be much, how much"
Let's do a test:
Select Top 10000 GID, Fariqi, Reader, Title from Tgongwen Order by Gid DESC
Time: 4673 ms
Select Top 10000 GID, Fariqi, Title from Tgongwen Order by GID DESC
Time: 1376 ms
Select Top 10000 GID, Fariqi from Tgongwen Order by GID DESC
Time: 80 milliseconds
From this point of view, each of us extracts a field, and the extraction speed of the data will have a corresponding improvement. The upgrade speed also depends on the size of the field you discard.
10, count (*) is not slower than count (field)
Certain information says: Use * will count all columns, obviously more efficient than one world column name. This statement is actually unfounded. Let's see:
Select count (*) from tgongwen
Time: 1500 ms
Select Count (GID) from TGongwen
Time: 1483 ms
Select Count (Fariqi) from Tgongwen
Time: 3140 ms
Select Count (Title) from Tgongwen
Time: 52050 ms
As can be seen from the above, if you use count (*) and use the speed of COUNT (primary key), count (*) is faster than any other field other than the primary key, and the longer the field, summarizes The slower speed. I think, if you use count (*), SQL Server may automatically find the minimum field to summarize. Of course, if you write a COUNT (primary key), it will come more directly.
11, Order By is the highest in ordering the collected index
Let's see: (GID is the primary key, Fariqi is a polymerization column)
SELECT TOP 1000 GID, Fariqi, Reader, Title from TGongwen
Time: 196 milliseconds. Scan count 1, logic read 289 times, physically read 1 time, read reading 1527 times.
Select Top 10000 GID, Fariqi, Reader, Title from TGongwen Order by GID ASC
Time: 4720 milliseconds. Scan count 1, logic read 41956 times, physically read 0 times, pre-read 1287 times.
Select Top 10000 GID, Fariqi, Reader, Title from Tgongwen Order by Gid DESC
Time: 4736 ms. Scan count 1, logic read 55,350 times, physically read 10 times, read 775 times.
Select Top 10000 GID, Fariqi, Reader, Title from TGongwen Order by Fariqi ASC
Time: 173 ms. Scan count 1, logic read 290 times, physically read 0 times, read reading 0 times.
Select Top 10000 GID, Fariqi, Reader, Title from Tgongwen Order by Fariqi DESC
Time: 156 milliseconds. Scan count 1, logic read 289 times, physically read 0 times, read reading 0 times.
From the above we can see that the speed of no sorting and the number of logical readings are quite, but these are much more queried than "ORDER BY non-aggregated index". of. At the same time, when sorting in a field, whether it is a normal or reverse, the speed is basically quite.
12, efficient TOP
In fact, when querying and extracting a large-capacity data set, the maximum factor affecting the database response time is not a data lookup, but a physical I / 0 operation. Such as:
SELECT TOP 10 * FROM
SELECT TOP 1000 GID, Fariqi, Title from Tgongwen
Where neibuyonghu = 'office'
Order by gid desc) AS A
Order by GID ASC
This statement, in theory, the execution time of the whole statement should be longer than the child's execution time, but the fact is the opposite. Because the clause is executed, the 10000 records are returned, and the whole statement returns only 10 statements, so the factor affecting the database response time is a physical I / O operation. One of the most effective ways to limit physical I / O operations here is to use TOP keywords. Top Keywords are the words used in SQL Server to extract the first few or the first few percentage data. The application of the script in practice found that TOP is really easy, and the efficiency is also high. But this word is not in another large database Oracle, this can't be said to be a regret, although in Oracle, other methods (such as rownumber) can be used in Oracle. In the discussion of the "Paging Display Storage Process of Ten Thousands of Data), we will use TOP.
To this end, we discussed how to quickly query the data methods you need from a large-capacity database. Of course, these methods we introduce are "soft" methods. In practice, we have to consider all kinds of "hard" factors, such as network performance, server performance, performance system performance, even network cards, switches, etc.
