Секционированное представление считывает все таблицы, когда строк больше или запросы становятся более сложными? Ошибка 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:

https://docs.google.com/file/d/0B6OXmuJYfpRcTE9Pd0xpSEhEQy04eWZqa2lKejM5YkdPRHFr/edit?usp=docslist_api

Неверный план выполнения в xml: https://docs.google.com/file/d/0B6OXmuJYfpRcU2ZUVFdtLUcxQk83TVFSNUFoZEYtbVdaWU4w/edit?usp=docslist_api

1 ответ

Вы ожидаете, что T1 не будет сканироваться, потому что представление разделено. Однако табличная переменная вызывает сканирование. Оператор для выбора из табличной переменной анализируется отдельно от оператора для создания и вставки значений в табличную переменную - поэтому оптимизатор не знает о значениях.

Если вы используете литеральные значения, а не (SELECT * FROM @a), он не будет ссылаться на T1 и будет просто сканировать T4. Хотя, как ни странно, это дороже и хуже.

Изучите создание индекса DateY с включенными столбцами для оптимизации производительности.

Другие вопросы по тегам