Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to Make SELECT COUNT(*) Queries Crazy Fast.sql
USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO
/* Turn on actual execution plans and: */
SET STATISTICS IO, TIME ON;
GO
/* 1: Plain ol' SELECT with only a clustered rowstore index, compatibility level 2017 & prior */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO
/* 2: Compatibility level 2019 */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO
/* 3: Add nonclustered rowstore indexes, but use 2017 & prior's row mode */
CREATE INDEX IX_PostId ON dbo.Votes(PostId);
GO
CREATE INDEX IX_UserId ON dbo.Votes(UserId);
GO
CREATE INDEX IX_BountyAmount ON dbo.Votes(BountyAmount);
GO
CREATE INDEX IX_VoteTypeId ON dbo.Votes(VoteTypeId);
GO
CREATE INDEX IX_CreationDate ON dbo.Votes(CreationDate);
GO
/* What are the sizes of each index?
Turn OFF actual plans to run this: */
sp_BlitzIndex @TableName = 'Votes';
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO
/* 4: 2019's batch mode with nonclustered rowstore indexes */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO
/* 5: Add nonclustered columnstore indexes */
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_BountyAmount ON dbo.Votes(BountyAmount);
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.