varchar
无论如何都会动态分配空间,所以我的问题是,与使用varchar(255)
相比,使用varchar(5000)
效率更高还是节省更多空间。如果是,为什么?#1 楼
是的,如果所有值都适合varchar(5000)
,则varchar(255)
可能比varchar(5000)
差。原因是SQL Server将根据表中列的声明(而非实际)大小来估计数据大小,进而估计内存授予。当您具有sys.dm_exec_query_stats
时,它将假定每个值的长度为2500个字符,并根据该值保留内存。 这是我最近的GroupBy演示中的一个关于不良习惯的演示,可以轻松地为自己证明(某些
SET STATISTICS TIME ON
输出列需要SQL Server 2016,但仍然可以通过TOP (5000)
或其他工具进行证明在较早的版本上);它针对相同数据针对相同查询显示更大的内存和更长的运行时间-唯一的区别是列的声明大小:-- create three tables with different column sizes
CREATE TABLE dbo.t1(a nvarchar(32), b nvarchar(32), c nvarchar(32), d nvarchar(32));
CREATE TABLE dbo.t2(a nvarchar(4000), b nvarchar(4000), c nvarchar(4000), d nvarchar(4000));
CREATE TABLE dbo.t3(a nvarchar(max), b nvarchar(max), c nvarchar(max), d nvarchar(max));
GO -- that's important
-- Method of sample data pop : irrelevant and unimportant.
INSERT dbo.t1(a,b,c,d)
SELECT TOP (5000) LEFT(name,1), RIGHT(name,1), ABS(column_id/10), ABS(column_id%10)
FROM sys.all_columns ORDER BY object_id;
GO 100
INSERT dbo.t2(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
INSERT dbo.t3(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
GO
-- no "primed the cache in advance" tricks
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO
-- Redundancy in query doesn't matter! Just has to create need for sorts etc.
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t1 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t2 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t3 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT [table] = N'...' + SUBSTRING(t.[text], CHARINDEX(N'FROM ', t.[text]), 12) + N'...',
s.last_dop, s.last_elapsed_time, s.last_grant_kb, s.max_ideal_grant_kb
FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%' ORDER BY t.[text];
所以,是的,对-请调整您的列的大小。
此外,我使用varchar(32),varchar(255),varchar(5000),varchar(8000)和varchar(max)重新运行了测试。相似的结果(单击放大),尽管32和255之间以及5,000和8,000之间的差异可以忽略不计:
这里另一个测试带有
DISTINCT
的更改,目的是要不断重复制作更全面的测试(单击放大):所以即使使用5,000行而不是10,000行(并且sys.all_columns中有5,000+行至少追溯到SQL Server 2008 R2),也观察到相对线性的进展-即使使用相同的数据,所定义的大小也越大该列,需要更多的内存和更多的时间来满足完全相同的查询(即使它确实具有毫无意义的q4312079q)。
评论
这真是令人惊讶。 varchar(450)和varchar(255)之间的区别是否相同? (或低于4000的东西?)
– a_horse_with_no_name
17年1月24日,19:26
@a_horse_with_no_name我尚未测试运行时性能的所有排列,但是内存授予将是线性进行的-它只是rowcount *(column_size / 2)的函数。
–亚伦·伯特兰(Aaron Bertrand)
17年1月24日在19:35
@a_horse_with_no_name好吧,它必须猜测数据的宽度,以便避免溢出。还应该怎么猜?它不能扫描和读取整个表来确定所有可变宽度列的avg / max长度,以此作为生成执行计划的前提(即使这样做,它也只能在重新编译期间执行)。
–亚伦·伯特兰(Aaron Bertrand)
17年1月24日在19:50
Oracle会保留有关平均行长,每列的最小值和最大值以及直方图。 Postgres保持非常相似的统计信息(虽然不记录最小值/最大值,但记录频率)。对于它们两者,nvarchar(150),nvarchar(2000)或varchar(400)在性能上都没有任何区别。
– a_horse_with_no_name
17年1月24日在20:29
@AndreasReiff因为内存很快。孤立地,您不会注意到为单个查询使用更多的内存的区别。但是记忆是有限的。如果您有500个人在运行相同的查询,您会注意到其中的区别-有些人可能运行得一样快,而另一些人会饿死,不得不等待拨款或不得不花钱。
–亚伦·伯特兰(Aaron Bertrand)
20年5月7日在19:55
评论
您是否需要5000字符宽的列?如果是这样,为什么? varchar(MAX)列在这里适合您吗?