Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как получить одинаковые планы с view и без view  [new]
Glamorama
Member

Откуда:
Сообщений: 152
Доброе время суток, господа.

Есть сервер Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1. Есть БД на этом сервере. Есть приложение, которое работает с этой БД. Приложение имеет доступ только к нескольким view и работает с ними через свой ORM - т.е. ручками запросы написать нельзя, только в теле view поковыряться можно.

Есть желание ускорить одну view.
Базовые таблицы.
-- абоненты
create table Accounts( AccountID int not null primary key, AccountNumber varchar(20), AccountParams varchar(1024) not null)
-- услуги
create table AccountServices( AccountID int not null, ServiceID int not null, ChangeDate datetime, StateID int not null, primary key( AccountID, ServiceID ) )
--справочник "особых" услуг
create table Ref( ServiceID int not null primary key)

View
create view AccountServicesInfo
as
select a.AccountID
      , s.ServiceId
      , s.StateId
      , case when s.StateID=0 and r.ServiceId is not null then min(ChangeDate) over( PARTITION by a.AccountID ) else s.ChangeDate end as ChangeDate
from Accounts a
join AccountServices s
    on s.AccountId = a.AccountID
left join Ref r
    on r.ServiceId = s.ServiceId

Суть view - показать все услуги абонентов. Для выключенных (StateID = 0 ) "особых" услуг (из Ref) показать не дату последнего изменения, а дату изменения самой ранней особой услуги у этого абонента.

Собственно проблема.
Запрос вида
declare @id int = 1231354
select a.*
from AccountServicesInfo a
where a.AccountID = @id

имеет план
  |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Data].[dbo].[AccountServices].[StateId] as [s].[StateId]=(0) AND [Data].[dbo].[Ref].[ServiceId] as [r].[ServiceId] IS NOT NULL THEN [Expr1008] ELSE [Data].[dbo].[AccountServices].[ChangeDate] as [s].[Change
       |--Filter(WHERE:([Data].[dbo].[Accounts].[AccountID] as [a].[AccountID]=[@id]))
            |--Nested Loops(Inner Join)
                 |--Table Spool
                 |    |--Segment
                 |         |--Sort(ORDER BY:([a].[AccountID] ASC))
                 |              |--Hash Match(Right Outer Join, HASH:([r].[ServiceId])=([s].[ServiceId]))
                 |                   |--Clustered Index Scan(OBJECT:([Data].[dbo].[Ref].[ix] AS [r]))
                 |                   |--Merge Join(Inner Join, MERGE:([a].[AccountID])=([s].[AccountId]), RESIDUAL:([Data].[dbo].[Accounts].[AccountID] as [a].[AccountID]=[Data].[dbo].[AccountServices].[AccountId] as [s].[AccountId]))
                 |                        |--Clustered Index Scan(OBJECT:([Data].[dbo].[Accounts].[PK__Accounts__349DA5861ED998B2] AS [a]), ORDERED FORWARD)
                 |                        |--Clustered Index Scan(OBJECT:([Data].[dbo].[AccountServices].[ix] AS [s]), ORDERED FORWARD)
                 |--Nested Loops(Inner Join, WHERE:((1)))
                      |--Stream Aggregate(DEFINE:([Expr1008]=MIN([Data].[dbo].[AccountServices].[ChangeDate] as [s].[ChangeDate])))
                      |    |--Table Spool
                      |--Table Spool

и работает неприлично долго.

Такой же запрос без view
declare @id int = 1231354

select a.AccountID
      , s.ServiceId
      , s.StateId
      , case when s.StateID=0 and r.ServiceId is not null then MIN(ChangeDate) over( PARTITION by a.AccountID ) else s.ChangeDate end as ChangeDate
from Accounts a
join AccountServices s
    on s.AccountId = a.AccountID
left join Ref r
    on r.ServiceId = s.ServiceId
where a.AccountID = @id
имеет план
  |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Data].[dbo].[AccountServices].[StateId] as [s].[StateId]=(0) AND [Data].[dbo].[Ref].[ServiceId] as [r].[ServiceId] IS NOT NULL THEN [Expr1008] ELSE [Data].[dbo].[AccountServices].[ChangeDate] as [s].[Change
       |--Nested Loops(Inner Join)
            |--Table Spool
            |    |--Segment
            |         |--Nested Loops(Left Outer Join, WHERE:([Data].[dbo].[Ref].[ServiceId] as [r].[ServiceId]=[Data].[dbo].[AccountServices].[ServiceId] as [s].[ServiceId]))
            |              |--Nested Loops(Inner Join)
            |              |    |--Clustered Index Seek(OBJECT:([Data].[dbo].[Accounts].[PK__Accounts__349DA5861ED998B2] AS [a]), SEEK:([a].[AccountID]=[@id]) ORDERED FORWARD)
            |              |    |--Clustered Index Seek(OBJECT:([Data].[dbo].[AccountServices].[ix] AS [s]), SEEK:([s].[AccountId]=[@id]) ORDERED FORWARD)
            |              |--Clustered Index Scan(OBJECT:([Data].[dbo].[Ref].[ix] AS [r]))
            |--Nested Loops(Inner Join, WHERE:((1)))
                 |--Stream Aggregate(DEFINE:([Expr1008]=MIN([Data].[dbo].[AccountServices].[ChangeDate] as [s].[ChangeDate])))
                 |    |--Table Spool
                 |--Table Spool

и время работы в разы меньше.

Кол-во записей в оригинальных таблицах измеряется миллионами (кроме Ref - там всего 2-3 записи).
Как добиться адекватного результата от запроса с view?
26 ноя 13, 23:33    [15195097]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
Glamorama
Member

Откуда:
Сообщений: 152
Для тех, кто захочет у себя повторить.
Заполняем таблицы
--Accounts
; with Cte as (
select top 1000 a.id
     , a.name
from sysobjects a
cross join sysobjects b
)
insert into Accounts( AccountId, AccountNumber, AccountParams )
select a.AccountID
     , right( '00000000000000000' + CAST( AccountId as varchar ), 18 )
     , CAST( AccountId as varchar ) + '_' + a.name
from (
        select ROW_NUMBER() over( order by a.id) as AccountID
	         , a.name 
        from Cte a
        cross join Cte b
    ) a

-- AccountServices
declare @id int, @rand int
set @id = 1

while @id<=10000
begin
    set @rand = 3+RAND()*17

    insert into AccountServices
    select top (@rand)
        @id
        , id
        , GETDATE()
        , case when @id % 3 = 1 and @rand % 5 = 1 then 0 else 1 end
    from #nums

    set @id = @id +1 
end
--Ref
insert into Ref values( 1), (2), (3 )
26 ноя 13, 23:37    [15195112]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
step_ks
Member

Откуда:
Сообщений: 936
Glamorama
Для тех, кто захочет у себя повторить.
Заполняем таблицы

Invalid object name '#nums'.
26 ноя 13, 23:50    [15195157]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
create view dbo.AccountServicesInfo
as
select a.AccountID
      , s.ServiceId
      , s.StateId
      , case when s.StateID=0 and r.ServiceId is not null then t.MinChangeDate else s.ChangeDate end as ChangeDate
from Accounts a
join AccountServices s
    on s.AccountId = a.AccountID
left join Ref r
    on r.ServiceId = s.ServiceId
cross apply
    (select min(ChangeDate) as MinChangeDate from AccountServices where AccountID = a.AccountID) t
27 ноя 13, 00:36    [15195324]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
invm,

+100500.

Оконные функции (ОФ) в public вью - это кабздец.

Запросы пока суть технически не идентичные.
Я бы сказал даже формально запросы разные.
ОФ имеет область применимости и оно его "захватывает", определяет.
А с другой быть прозрачным по группе (она в одном экземпляре).

Т.е. ОФ должны были бы быть "чистыми", но MS забило болт (как и на другие проблемы и недоделки).

Окошки ломают язык. Но с другой стороны должны были решить маркетологовые проблемы и задачи.
Ломают в том смысле, что вы решаете частную задачу - группинг данных. И формально надо было писать основу группинга внизу запроса (не путать с GROUP BY), а функции можно было и те же оставить (без OVER) - просто режим другой.
А с другой вы можете написать по разным группам и тогда ваше частное решение становится очевидно невыполнимым и поведение скуля понятным и логичным.

Т.е. один тип PARTITION BY (или не один) - ни к чему не должен обязывать.
И в этом изъян языка - двусмысленность.
Хотя такого много, те же кореллированные подзапросы (в SELECT), что также не люблю и не применяю.

И кстати, invm, почему CROSS APPLY?
27 ноя 13, 06:05    [15195941]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
Glamorama
Member

Откуда:
Сообщений: 152
step_ks
Glamorama
Для тех, кто захочет у себя повторить.
Заполняем таблицы

Invalid object name '#nums'.
if ( select object_id('tempdb..#nums') )  is not null
    drop table #nums
select top 20 row_number() over( order by a.id ) as id
into #nums
from sysobjects a


И с текстом view ошибочка вышла. Должно быть так
create view AccountServicesInfo
as
select a.AccountID
      , s.ServiceId
      , s.StateId
      , case 
            when s.StateID = 0 then min( case when r.ServiceID is null then null else ChangeDate end ) over( PARTITION by a.AccountID ) 
            else s.ChangeDate 
        end as ChangeDate
from Accounts a
join AccountServices s
    on s.AccountId = a.AccountID
left join Ref r
    on r.ServiceId = s.ServiceId


И вариант invm будет выглядеть так
create view dbo.AccountServicesInfo
as
select a.AccountID
      , s.ServiceId
      , s.StateId
      , case when s.StateID=0  then t.ChangeDate else s.ChangeDate end as ChangeDate
from Accounts a
join AccountServices s
    on s.AccountId = a.AccountID
outer apply ( 
                select min(x.ChangeDate) as ChangeDate
                from AccountServices x
                join Ref r
                    on r.ServiceId = x.ServiceID
                where x.AccountID = a.AccountID
                  and x.StateId = 0
           ) t

Все хорошо в этом варианте, но печалит двойное обращение к AccountServices.
Есть ещё варианты?
27 ноя 13, 08:07    [15196048]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
invm
Member

Откуда: Москва
Сообщений: 9724
Mnior
И кстати, invm, почему CROSS APPLY?
Для запроса ТС'а практически все равно полный агрегирующий или apply - по io разницы не будет. Поэтому был выбран apply, как более короткий в написании, ибо уже хотелось спать Конечно, если по уму, то apply там быть не должно.
Glamorama
но печалит двойное обращение к AccountServices.
Сравнивать надо по io и cpu, а не количеству обращений к таблице.
27 ноя 13, 23:12    [15202482]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glamorama
Есть ещё варианты?
А этот ваш ORM параметризованные представления потреблять может?
Скорее нет.
28 ноя 13, 00:50    [15202820]     Ответить | Цитировать Сообщить модератору
 Re: Как получить одинаковые планы с view и без view  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glamorama, это полный песец. Вы запутали всех и не умете и не понимаете как писать правильно.
SELECT	S.AccountID
,	S.ServiceID
,	S.StateID
,	CASE	WHEN S.StateID = 0
		THEN M.ChangeDate
		ELSE S.ChangeDate
		END AS ChangeDate
FROM	dbo.AccountServices	S
OUTER APPLY (
	SELECT	Min(M.ChangeDate)
	FROM	dbo.AccountServices	M
	WHERE	M.StateID	= 0
	AND	M.AccountID	= S.AccountID
	AND	Exists(SELECT * FROM dbo.Ref R ON R.ServiceID = M.ServiceID)
)				M (ChangeDate)
1. Стало понятнее (не опускайте алиасы, и кстати схему тоже).
2. А накуя тут dbo.Accounts вообще в этом представлении? Добавьте FK на таблу.
3. А накуя тут dbo.Ref? Это вместо Bit поля?
4. Min можно на Top(1) заменить. Имхо такой подход чаще лучше, удобнее и понятнее. Агрегатки чаще именно для отчётной части.
5. "Лишнее" обращение думаю будет выгоднее сегментирования.
6. Этот CASE выворачивает мозг. Вы путаете скуль и читателя по чёрному. Так делать глупо.
Правильнее надо написать так OVER(PARTITION BY S.AccountID, S.StateID) ибо это свойство не dbo.Accounts, а dbo.AccountServices в рамках состояния.
Вот как должно было выглядеть изначально:
ALTER VIEW [dbo].[AccountServicesInfo] AS
SELECT	S.AccountID
,	S.ServiceID
,	S.StateID
,	CASE	WHEN S.StateID = 0
		 AND Exists(SELECT * FROM dbo.Ref R ON R.ServiceID = S.ServiceID)
		THEN Min(S.ChangeDate)OVER(PARTITION BY S.AccountID, S.StateID)
		ELSE S.ChangeDate
		END AS ChangeDate
FROM	dbo.AccountServices	S
GO
Уверен что план станет нужным.
28 ноя 13, 01:40    [15202955]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить