I have a big table with 315M rows. with these columns & Index :
[key_] (int & IDENTITY(1,1)), [time_] (datetime), [value_] (float), [quality_] (int)
PK & Clustered-Index: key_ , Non-Clustered-Index on: time_ + quality_
Actually the main select that we use here is as bellow. This select gives good result, in 2Secs, but when I change the range & make it wider (like 36 hours), it responds in 6Minutes!
SELECT TOP (1000)
,[time_]
,[value_]
,[quality_]
FROM [DataHubRev2].[dbo].[tb_HPGR_120MI01ME01_Power]
WHERE quality_=192 AND time_ >' 2020-03-27 19:00:01'AND time_ <' 2020-03-28 07:00:00'
--- 12 hours: Good Result
--- WHERE quality_=192 AND time_ >' 2020-03-27 07:00:01'AND time_ <' 2020-03-28 19:00:00'
--- 36 hours: Bad Result
It is clear that by using this select & where, SQL-Engine should go for NonClusteredIndex (at least I think so). When I look at the 'Actual Execution Plan' the first one goes directly to the NonClustIndex Seek, but the second one goes for ClusteredIndex Scan & the readed rows tells 315M (the entire table)!!
Interesting part is that when I remove ',[value_]
' from the select or add the Hint to force NonClustIndex use (With (INDEX = [IX_TimeQuality_Index])
) response time & exec-plan is solved. But I need a more deep solution & actually why this is happening.
Also tell me that: making my table as Heap & not having any Clustered Index on ID which I don't use on my selects is a good practice or not?
Edit:
These are the Actual Execution Plans, you could see that the second one scans all the table (Number of rows read).
- Good Response Time : https://ibb.co/JFjXXwJ
- Bad Response Time: https://ibb.co/WGgrDh2
Best Answer
Wild Guess only: Use quality_ + time_ instead time_ + quality_ and include (value_) ?