Millions of data query optimization in SQL SERVER database

1. When you need to query all the data in the table

Compare the following three types of query statements:

Suppose the data table is BasicMsg20170401, there are 17 columns, and the number of data bars is 2.84 million

(1) SELECT * from BasicMsg20170401

Takes more than 44 seconds

(2) SELECT column 1, column 2 ... From BasicMsg20170401

Takes 28-30 seconds up and down

(3) SELECT column 1, column 2 ... From BasicMsg20170401 with (index (SELAA_INDEX))

After forcing a nonclustered index, it takes time to 23~28 up and down

Summary: Add mandatory index, when the amount of data is less than the number of optimizations, the amount of data is large, there will be a bit of optimization. A large number of data queries, as far as possible without select *.

When the database is slow to find queries, you need to check the database initial value size and the way the data grows, or place the database filegroup in a different disk space.

2, generally do not need to query the full table data, as far as possible after the query statement to add filter statements, filter keys as far as the index column

Query 2 million data 30 seconds, if you add an index on "column 1", you can use the following query statement

SELECT column 1, column 2 ... From BasicMsg20170401 with (index (SELAA_INDEX)) where column 1<=35500000

Millions of data query optimization in SQL SERVER database