The following questions please help analyze what could be the cause, or have had to help answer:
Table in SQL SERVER, a month in the table has a record of 6 million or so, starting from January 1, 2019,
Recently feeling query speed is slow, so do the following tests:
Using SQL: select top 1000 * from table name, quickly;
Using SQL: select top 1000 * from table name where the text fields like "% B", quickly;
Using SQL: select top 1000 * from table name where the date & gt; '2019-1-1', quickly;
The date condition has been to '2019-3-1' is soon, and then start from '2019-4-1', has slowly:
'2019-4-1', takes 34 seconds;
'2019-5-1', take 46 seconds;
'2019-6-1', takes 45 seconds;
'2019-7-1', takes 45 seconds;
'2019-8-1', takes 45 seconds;
The most magical is, in the '2020-8-1', actually reached 5 minutes!!!!!
Date fields have a nonclustered index, I think what's wrong with the index, so deleted index reconstruction, the result still,
Scan the date field index fragments, didn't find any problem:
Executed the LEAF level of scanning,
- scan pages... : 198918
- scan area number... : 24865
- area switching times... : 24864
- the average number of pages of each block... : 8.0
- scan density/best count: the actual count... : 100.00% (24865-24865)
- logic scanning pieces... 0.00% :
- area scanning pieces... 0.14% :
- the average number of bytes available each page... : 11.0
- the average density (full)... : 99.86%

CodePudding user response:

 select top 1000 * 
From the table name 
Where the date & gt; '2019-4-1' 
The ORDER BY date of ASC 

And sorting try again?
If it is slow, look at the execution plan, the implementation plan of the corresponding,

CodePudding user response:

reference 1/f, gypsy song response:

 select top 1000 * 
From the table name 
Where the date & gt; '2019-4-1' 
The ORDER BY date of ASC 

And sorting try again?
If it is slow, look at the execution plan, the implementation plan of the corresponding,

sort, but I changed idea, set date field index as the aggregate type, speed is fast,, thank you!

CodePudding user response:

TOP the data distribution has always been the problem, you change the row_number writing is solved

CodePudding user response:

Look at the execution plan

CodePudding user response:

reference sxq129601 reply: 3/f

TOP the data distribution has always been the problem, you change the row_number method solves the

Could you tell me what you said "change the row_number" writing is solved, what meaning be?

CodePudding user response:

With analysis function row_number rewrite the top, you can try to see see