Секционированное представление считывает все таблицы, когда строк больше или запросы становятся более сложными? Ошибка SQL Server 2008?
Ниже приведены сценарии настройки и тестирования. Тестовый скрипт предполагает сканирование только таблицы T4. Однако он начинает сканировать обе таблицы T1 и T4, когда в них более 10000 строк.
create table T1 (A varchar(5) check ((A='S4' or A='S3' or A='S2' or A='S1' or A='FS' or A='FM' or A='FBL' or A='ES' or A='EBL' or A='BL'))
,DateX date
,id char(6)
,DateY date
,primary key clustered (A, DateX, id))
create table T4 (A varchar(5) check ((A='S1780' OR A='C1780' OR A='B1780'))
,DateX date
,id char(6)
,DateY date
,primary key clustered (A, DateX, id));
-- Insert some values
go
create view dbo.tall
as
select * from dbo.T1
union all
select * from dbo.T4
Тестовый код:
declare @A table (A varchar(5) primary key (A));
insert @A
values ('S1780'), ('C1780'), ('B1780');
with a as (select *
from tall
where A in (select *
from @A)
),
sd
as (select A, max(DateY) DateY
from a
group by A
),
filter24m
-- Un-comment the lines in this CTE will make the scanning T1 occur with even less row count
as (select id, a.A --, sd.DateY
from a
join sd on a.A = sd.A
--where DateX between dateadd(mm, 1, sd.DateY) and dateadd(mm, 24 + 1, sd.DateY) --
--group by id, a.A, sd.DateY
--having count(*) = 24
)
--
select *
from filter24m
Неправильное выполнение (тест, когда T1 имеет 100 строк, а T4 имеет 10000 строк):
Стол "Т4". Сканирование 2, логическое чтение 80, физическое чтение 0, чтение с опережением 0, чтение логического объекта 0, физическое чтение 1, чтение с опережением 0. Таблица "#1B3A42B1". Сканирование 1, логическое чтение 6, физическое чтение 0, чтение с опережением 0, логическое чтение с 0, физическое чтение с 0, чтение с опережением 0. Стол "Рабочий стол". Сканирование счетчик 0, логическое чтение 0, физическое чтение 0, чтение с опережением 0, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0. Стол "Рабочий стол". Сканирование счетчик 0, логическое чтение 0, физическое чтение 0, чтение с опережением 0, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0. Стол "Т1". Сканирование 3, логическое чтение 6, физическое чтение 0, чтение с опережением 0, логическое чтение с 0, физическое чтение с 0, чтение с опережением 0.
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCES:([workdb].[dbo].[T1].[A]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([A]))
| | |--Clustered Index Seek(OBJECT:(@A), SEEK:([A] >= 'B1780' AND [A] <= 'S4') ORDERED FORWARD)
| | |--Stream Aggregate(DEFINE:([workdb].[dbo].[T1].[A]=ANY([workdb].[dbo].[T1].[A])))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([workdb].[dbo].[T1].[A]))
| | |--Stream Aggregate(DEFINE:([workdb].[dbo].[T1].[A]=ANY([workdb].[dbo].[T1].[A])))
| | | |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T1].[PK__T1__EE1DD21123AC6823]), SEEK:([workdb].[dbo].[T1].[A]=[A]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:(@A), SEEK:([A]=[workdb].[dbo].[T1].[A]), WHERE:([A]>='B1780' AND [A]<='S4') ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T1].[PK__T1__EE1DD21123AC6823]), SEEK:([workdb].[dbo].[T1].[A]=[workdb].[dbo].[T1].[A]) ORDERED FORWARD)
|--Merge Join(Inner Join, MERGE:([workdb].[dbo].[T4].[A])=([workdb].[dbo].[T4].[A]), RESIDUAL:([workdb].[dbo].[T4].[A]=[workdb].[dbo].[T4].[A]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([workdb].[dbo].[T4].[A]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([workdb].[dbo].[T4].[A]))
| | |--Stream Aggregate(GROUP BY:([workdb].[dbo].[T4].[A]))
| | | |--Clustered Index Scan(OBJECT:([workdb].[dbo].[T4].[PK__T4__EE1DD21128711D40]), ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT:(@A), SEEK:([A]=[workdb].[dbo].[T4].[A]), WHERE:([A]>='B1780' AND [A]<='S4') ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:(@A), SEEK:([A]=[workdb].[dbo].[T4].[A]), WHERE:([A]>='B1780' AND [A]<='S4') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([workdb].[dbo].[T4].[PK__T4__EE1DD21128711D40]), ORDERED FORWARD)
Хорошо (тест, когда в обеих таблицах всего 100 строк):
Таблица "#1DE1A532". Сканирование 101, логическое чтение 202, физическое чтение 0, чтение с опережением 0, логическое чтение с 0, физическое чтение с 0, чтение с опережением 0. Стол "Рабочий стол". Сканирование счетчик 0, логическое чтение 0, физическое чтение 0, чтение с опережением 0, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0. Стол "Т4". Сканирование 103, логическое чтение 206, физическое чтение 0, чтение с опережением 0, логическое чтение 1, физическое чтение 1, чтение с опережением 0.
|--Nested Loops(Inner Join, OUTER REFERENCES:([Union1006]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([A]))
| |--Clustered Index Seek(OBJECT:(@A), SEEK:([A] >= 'B1780' AND [A] <= 'S4') ORDERED FORWARD)
| |--Concatenation
| |--Filter(WHERE:(STARTUP EXPR([A]='BL' OR [A]='EBL' OR [A]='ES' OR [A]='FBL' OR [A]='FM' OR [A]='FS' OR [A]='S1' OR [A]='S2' OR [A]='S3' OR [A]='S4')))
| | |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T1].[PK__T1__EE1DD21123AC6823]), SEEK:([workdb].[dbo].[T1].[A]=[A]) ORDERED FORWARD)
| |--Filter(WHERE:(STARTUP EXPR([A]='B1780' OR [A]='C1780' OR [A]='S1780')))
| |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T4].[PK__T4__EE1DD21128711D40]), SEEK:([workdb].[dbo].[T4].[A]=[A]) ORDERED FORWARD)
|--Top(TOP EXPRESSION:((1)))
|--Nested Loops(Inner Join, WHERE:([Union1019]=[A]))
|--Concatenation
| |--Filter(WHERE:(STARTUP EXPR([Union1006]='B1780' OR [Union1006]='C1780' OR [Union1006]='S1780')))
| | |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T4].[PK__T4__EE1DD21128711D40]), SEEK:([workdb].[dbo].[T4].[A]=[Union1006]) ORDERED FORWARD)
| |--Filter(WHERE:(STARTUP EXPR([Union1006]='BL' OR [Union1006]='EBL' OR [Union1006]='ES' OR [Union1006]='FBL' OR [Union1006]='FM' OR [Union1006]='FS' OR [Union1006]='S1' OR [Union1006]='S2' OR [Union1006]='S3' OR [Union1006]='S4')))
| |--Clustered Index Seek(OBJECT:([workdb].[dbo].[T1].[PK__T1__EE1DD21123AC6823]), SEEK:([workdb].[dbo].[T1].[A]=[Union1006]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:(@A), SEEK:([A] >= 'B1780' AND [A] <= 'S4') ORDERED FORWARD)
Хороший план выполнения в xml:
Неверный план выполнения в xml: https://docs.google.com/file/d/0B6OXmuJYfpRcU2ZUVFdtLUcxQk83TVFSNUFoZEYtbVdaWU4w/edit?usp=docslist_api
1 ответ
Вы ожидаете, что T1 не будет сканироваться, потому что представление разделено. Однако табличная переменная вызывает сканирование. Оператор для выбора из табличной переменной анализируется отдельно от оператора для создания и вставки значений в табличную переменную - поэтому оптимизатор не знает о значениях.
Если вы используете литеральные значения, а не (SELECT * FROM @a), он не будет ссылаться на T1 и будет просто сканировать T4. Хотя, как ни странно, это дороже и хуже.
Изучите создание индекса DateY с включенными столбцами для оптимизации производительности.