主页 / dba / 问题 / 162798
Accepted
Jmaurier
Asked: 2017-02-01 13:42:14 +0800 CST

数据类型会影响视图的性能吗？

  • 772

我刚刚注意到，在我们的数据库中，我们有一个将BIT列转换为INT. 基础表有一个计算BIT列，其定义如下：

IncludeInJobTotals as (CONVERT(bit, 
case 
when FL.[Status]=(5) 
  or FL.[Status]=(3)
  or FL.[Status]=(2) 
then (1) 
else (0) 
end,(0)))

在使用的视图中，IncludeInJobTotals我们将其转换为INT. 这会导致任何性能问题吗？可能还有哪些问题？有什么理由使用INToverBIT吗？

sql-server

1 个回答

  1. Best Answer

    首先回答一般性问题，是的，视图中的数据类型绝对会影响性能。让我们模拟一些数据来说明一个简单的例子：

    DROP TABLE IF EXISTS X_162798;

CREATE TABLE X_162798 (
NUM_VARCHAR VARCHAR(10), 
NUM_INT INT,
[Status] INT,
IncludeInJobTotals as (CONVERT(bit, 
case 
when [Status]=(5) 
  or [Status]=(3)
  or [Status]=(2) 
then (1) 
else (0) 
end,(0)))
);

-- insert 1 M rows with 999990 of them having a value of 1 for IncludeInJobTotals
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_162798 WITH (TABLOCK)
SELECT 
CAST(n AS VARCHAR(10))
, n
, CASE WHEN n <= 10 THEN 0 ELSE 5 END
FROM Nums 
WHERE n <= 1000000;

UPDATE STATISTICS X_162798 WITH FULLSCAN;

-- index all the columns
CREATE INDEX IX_162798_NUM_VARCHAR ON X_162798 (NUM_VARCHAR);
CREATE INDEX IX_162798_NUM_INT ON X_162798 (NUM_INT);
CREATE INDEX IX_162798_STATUS ON X_162798 ([Status]);
CREATE INDEX IX_162798_JOB ON X_162798 (IncludeInJobTotals);

DROP VIEW IF EXISTS V_162798;

GO

CREATE VIEW V_162798
AS
SELECT
CAST(NUM_VARCHAR AS INT) NUM_INT_CASTED
, NUM_VARCHAR 
, IncludeInJobTotals
, CONVERT(INT, IncludeInJobTotals) IncludeInJobTotals_INT
FROM X_162798;

GO

    以下两个查询返回相同的结果，但性能却大不相同：

    -- this query does a table scan and does an estimate based on density
SELECT *
FROM V_162798
WHERE NUM_INT_CASTED = '1';

-- this query does an index seek and does an estimate based on the histogram 
SELECT *
FROM V_162798
WHERE NUM_VARCHAR = '1';

    执行计划

    如果我进行了错误的数据类型转换，过滤器将不再可优化搜索。它不能在列上使用索引，并且基数估计会因为不能使用直方图而受到影响。

    BIT在您将列投射到 an 的情况下是否重要INTEGER？我们可以过滤视图中的IncludeInJobTotals和IncludeInJobTotals_INT列以运行一些测试。基于数据类型优先级，我们应该期望在 a和 an之间的任何比较中 aBIT将转换为an 。SQL Server应该能够在它们之间进行隐式转换，所以我认为很难找到性能问题。INTEGERBITINTEGER

    由于我们处理的是位列，因此简单过滤器的可能测试用例并不多。如果我在视图的任一列上过滤以查找具有0值的行，我总是会得到索引查找和正确的基数估计：

    -- all queries do an index seek and estimate 10 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 0;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(0 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(0 AS BIT);

    如果我过滤视图的任一列以查找具有1值的行，我总是会得到表扫描和正确的基数估计：

    -- all queries do a table scan and estimate 999990 rows will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 1;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = CAST(1 AS BIT);

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = CAST(1 AS BIT);

    寻找超出范围的值也按预期工作：

    -- these queries do an index seek and estimate 1 row will be returned
SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals = 2;

SELECT NUM_VARCHAR
FROM V_162798
WHERE IncludeInJobTotals_INT = 2;

    我不会担心这种数据类型更改的性能。很难想象您会遇到性能问题的场景。但是，请务必注意，使用视图的查询可能会根据您使用列的方式返回不同的结果。BIT数据类型的行为不同于INT：

    转换为位会将任何非零值提升为 1。

    回到数据类型优先级列表，一个VARCHAR值将被转换为一个BIT值。这意味着获得不同结果的一种方法是过滤或加入超出范围的VARCHAR值：

    CREATE TABLE TABLE_WITH_THE_NUMBER_2 (NUM VARCHAR(1));
INSERT INTO TABLE_WITH_THE_NUMBER_2 VALUES ('2');

-- this query returns 999990
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals = t.NUM;

-- this query returns 0
SELECT COUNT(*)
FROM V_162798 v
INNER JOIN TABLE_WITH_THE_NUMBER_2 t ON v.IncludeInJobTotals_INT = t.NUM;

    也许您的应用程序永远不会这样做，但您希望在那种情况下发生什么？我会在这里关注正确性而不是性能。

    • 3

相关问题