SQL query efficiency: 100w data query takes only 1 seconds

Memory:4gos:windows2003database: MS SQL Server -Purpose: Query performance test to compare performance SQL query efficiency for two queries step by step--SETP1.--Build Tables CREATE TABLE T_userinfo (useridintIdentity1,1) Primary key Nonclustered,nick varchar ( -) notNULL default "', ClassIDintNotNULL default 0, Writetime datetime notNULL defaultgetdate ()) Go--Build index create clustered index ix_userinfo_classid on t_userinfo (classid) Go--Step2. DECLARE @iintDeclare @kintdeclare @nick varchar (Ten)Set@i =1 while@i<1000000beginSet@k = @i%TenSet@nick =CONVERT (varchar,@i) insert into T_userinfo (nick,classid,writetime) VALUES (@nick, @k,getdate ())Set@i = @i +1End-Time consuming ,: -, need to wait patiently--Step3.SelectTop -Userid,nick,classid,writetime fromT_userinfowhereUserID notinch(SelectTop900000Userid fromt_userinfo ORDER BY userid ASC)-Time consuming8seconds, long enough.--Step4.SelectA.userid,b.nick,b.classid,b.writetime from(SelectTop -A.userid from (SelectTop900020Userid fromt_userinfo ORDER BY userid ASC) an ORDER BY A.userid Desc) A INNER join T_userinfo B on A.userid=B.userid ORDER BY a.userid ASC-Time consuming1seconds, too fast, no Incredibles .--Step5 whereEnquirySelectTop -Userid,nick,classid,writetime fromT_userinfowhereClassID =1and UserID notinch(SelectTop90000Userid fromT_userinfowhereClassID =1ORDER BY userid ASC)-Time consuming2seconds--Step6 whereEnquirySelectA.userid,b.nick,b.classid,b.writetime from(SelectTop -A.userid from (SelectTop900000Userid fromT_userinfowhereClassID =1ORDER BY userid ASC) an ORDER BY A.userid Desc) A INNER join T_userinfo B on A.userid=B.userid ORDER BY a.userid ASC--The Query Analyzer does not show1seconds. Query efficiency analysis: subqueries to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. In this case, you might consider replacing it with a join query. If you want to use a subquery, replace in with exists instead of not exists instead of in. Because the subquery introduced by exists only tests for the presence of rows that meet the criteria specified in the subquery, it is more efficient. In either case, not in is the least effective. Because it performs a full table traversal of a table in a subquery. Establish a reasonable index, avoid scanning redundant data, avoid table scan! Millions of data, still dozens of milliseconds to complete the query

SQL query efficiency: 100w data query takes only 1 seconds