If the answer is 'No', I'm OK with that...
I'm looking to see if this can be optimized any...it's part of a much larger stored proc.
CGCode is varchar(50),
select max(id) from Table where 1=1 and cgcode = 123 and datefromparts(cast(year as char(4)),cast(month as char(2)),'01') < getdate() and totalcount > 0 group by cgcode, year, month, fein
Logical reads from actual execution plan: 1,566,473
Source table raw data 32 million+ records
Estimated rows: 640K, actual 55K, before the Group By kicks in
Implicit conversion warnings on
Year/Month/CGCode (comes in as
Execution time roughly 7.5 seconds, doing a Non-Clustered Index Seek:
End result set is 114 rows (for this
CGCode we test with...others vary)
Performance is roughly the same in Prod on significantly better hardware than the Dev box. This is only going to get worse over time, as its pulling everything older than the current month, to populate a history chart in a UI.
What other info can I provide?
The index being used currently:
CREATE NONCLUSTERED INDEX [COIX_Table_TotalCount] ON [dbo].[Table] ( [TotalCount] ASC ) INCLUDE ( [ID], [CGCode], [Year], [Month], [FEIN]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
CGCode limits the data most...281 values.
year is only 3-4 years worth of data,
month only 12 options there of course.
TotalCount is a
bigint, 17K distinct values across 32 million records. No idea what the purpose of the
TotalCount column is.