Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/***************************************************************************
* SETUP
***************************************************************************/
SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
-- Jun 9 2015 12:06:16
-- Copyright (c) Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO
-- Misc hardware info
-- Laptop with Quad core i7 2.7GHz, 64 bit
-- SQL on dedicated disk, but it's a slow spinning drive
-- Set server memory to ~16GB; don't run in production!
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'16000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-- Create a table of 10MM rows of string values with the proper VARCHAR lengths
CREATE TABLE dbo.testVarchar (
s1 VARCHAR(18) NOT NULL,
s2 VARCHAR(75) NOT NULL,
s3 VARCHAR(9) NOT NULL,
s4 VARCHAR(15) NOT NULL,
s5 VARCHAR(123) NOT NULL,
s6 VARCHAR(5) NOT NULL
)
GO
INSERT INTO dbo.testVarchar WITH (TABLOCK) (s1, s2, s3, s4, s5, s6)
SELECT LEFT(s,18), LEFT(s, 75), LEFT(s, 9), LEFT(s, 15), LEFT(s, 123), LEFT(s, 5)
FROM (
SELECT TOP 10000000 CONVERT(VARCHAR(MAX), v1.name) +
CONVERT(VARCHAR(MAX), v2.name) +
CONVERT(VARCHAR(MAX), v3.name) AS s
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
CROSS JOIN master..spt_values v3
WHERE v1.name IS NOT NULL
AND v2.name IS NOT NULL
AND v3.name IS NOT NULL
) x
GO
-- Create another table with the same data, but all VARCHAR(256)
CREATE TABLE dbo.testVarchar256 (
s1 VARCHAR(256) NOT NULL,
s2 VARCHAR(256) NOT NULL,
s3 VARCHAR(256) NOT NULL,
s4 VARCHAR(256) NOT NULL,
s5 VARCHAR(256) NOT NULL,
s6 VARCHAR(256) NOT NULL
)
GO
INSERT INTO dbo.testVarchar256 WITH (TABLOCK) (s1, s2, s3, s4, s5, s6)
SELECT *
FROM dbo.testVarchar
GO
-- Check the sizes of the two tables
SELECT t.name, ps.ROW_COUNT, (ps.reserved_page_count*8.0) / (1024.0) AS sizeMb
FROM sys.TABLES t WITH (NOLOCK)
JOIN sys.dm_db_partition_stats ps WITH (NOLOCK)
ON ps.object_id = t.object_id
WHERE t.name IN ('testVarchar','testVarchar256','testVarcharChangeTo256')
--name row_count sizeMb
--testVarchar 10000000 1492.7656250
--testVarchar256 10000000 1492.7578125
GO
/***************************************************************************
* TEST CASE
* On my machine, the VARCHAR(CorrectLength) example completes
* almost 10x faster than the VARCHAR(256) example
***************************************************************************/
SET STATISTICS TIME, IO ON
GO
-- CPU time = 37674 ms, elapsed time = 19206 ms.
CREATE CLUSTERED INDEX IX_testVarchar
ON dbo.testVarchar (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO
-- CPU time = 29827 ms, elapsed time = 180581 ms.
CREATE CLUSTERED INDEX IX_testVarchar256
ON dbo.testVarchar256 (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO
/***************************************************************************
* DROP INDEXES (if you want to try again without rebuilding full data set)
***************************************************************************/
DROP INDEX testVarchar.IX_testVarchar
GO
DROP INDEX testVarchar256.IX_testVarchar256
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.