由于varchar无论如何都会动态分配空间,所以我的问题是,与使用varchar(255)相比,使用varchar(5000)效率更高还是节省更多空间。如果是,为什么?

评论

您是否需要5000字符宽的列?如果是这样,为什么? varchar(MAX)列在这里适合您吗?

#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