SQL Server Query Efficiency optimization

Query Efficiency analysis:

subquery to ensure that duplicate values are eliminated, nested queries must be processed for each result of the external query. In this case, you might consider replacing the join query.

If you want to use a subquery, use exists instead of in, with not exists instead of in. Because the subquery introduced by exists only tests the existence 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 on the table in the subquery.

Establish a reasonable index, avoid scanning redundant data, avoid table scan!

Millions of data, so dozens of milliseconds to complete the query.

Machine condition

p4:2.4

Memory: 1 G

Os:windows 2003

Database: MS SQL Server 2000

Objective: To query performance test and compare the performance of two kinds of queries

SQL query efficiency step by step

--SETP 1.

--Build the table

create table t_userinfo
(
userid int identity(1,1) primary key nonclustered,
nick varchar(50) not null default '',
classid int not null default 0,
writetime datetime not null default getdate()
)

Go