We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing
COUNT statements on the Items table while processing most financial documents (orders, invoices, etc.).
SELECT COUNT('A') FROM [dbo].[Items] T0
I'm sure that would normally be fine, but there's over 6 million records, and it takes ~400ms to count them all. This can constitute a substantial portion of the overall processing time.
The table already has an extremely narrow NonClustered Index (tinyint, plus Clustered Key) on it which is what SQL is using when it does the Table Scan, so I don't think we can do any better in that regard.
There's a few solutions I'm aware of, which we'd like to avoid if possible:
- Indexed View using
- Use faster Processors - we're using Cloud Servers, and there's very limited options in this regard.
- Deleting Records - not really an option in this case.
Do we have any other options to speed this up?
Here's a Gist showing the setup: https://gist.github.com/elvishfiend/5094f120b14f8ecfb325623edcb5f3eb