Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 список клиентов, присутствующих в фактах  [new]
нуб987
Guest
подскажите, есть ли быстрый способ получить список клиентов, имеющихся в фактах (отгрузки только определенной продукции)?
В справочнике клиентов всего около 30тыс.записей. В фактах около 10млн.записей.
Т.е. я делаю так:
select c.id, c.Name
from Clients c
where exists(select 1 from Facts f
      where f.ClientId = c.id
         and f.ProductType = 1 -- продукция Тип1
      )

Запрос работает примерно минуту и выдает 3тыс.клиентов (есть индексы и на f.ClientId, и на f.ProductType). В идеале хотелось бы сделать вьюху, но с минутным запросом куб будет процесситься слишком долго...
Т.е. вопрос заключается в том, правильный ли запрос я написал (есть ли варианты проще/быстрей)?
13 янв 14, 12:45    [15407343]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
ну для начала план выполнения бы выложили
13 янв 14, 12:47    [15407369]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
нуб987
Guest
Konst_One, значит придется полный запрос привести... Полная задача такая: нужно получить список клиентов, на которых осуществлялись отгрузки продукции Типа1 (из вьюхи) с 2009г.
Вот полный запрос:
select PayerIdName, PayerNameId, RcvrIdName, RepartidorName, OwnerKindId, Id, PayerId, ReceiverId, Caption, Doc_Source, SkladId, price_grp, ManagerId, ABC, ClientKindId, DeliveryKindId, DeliveryKindId_Changed, geo_AreaId, geo_RayonId, geo_TownId, geo_CityAdmAreaId, geo_CitySubAreaId, POSKindId, NetId, RepartidorId, TimeDelivery, FreshExpressId, AddressId, ChannelSitnikId, CategoryId, ChannelOfSellingId
from t_Clients c
where exists(
   select 1 from FactsAll f -- FactsAll - это вьюха вида "select ... from Facts_<YEAR> union all"
     where exists(select 1 from Product_Cond mc where mc.ProdId = f.ProdId) -- здесь отбираем факты по уже отобранной продукции (вьюха)
         and f.ModifiedDate >= '20090101' -- дата отгрузки с 2009г
         and f.ClientId = c.id -- привязка к клиентам
   ) -- exists


вот план:
+
  |--Merge Join(Inner Join, MERGE:([Union1723])=([c].[Id]), RESIDUAL:([Union1723]=[c].[Id]))
       |--Sort(ORDER BY:([Union1723] ASC))
       |    |--Hash Match(Aggregate, HASH:([Union1723]), RESIDUAL:([Union1723]=[Union1723]))
       |         |--Hash Match(Right Semi Join, HASH:([Product].[ProdId])=([Union1713]))
       |              |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Product].[PK_Product]), WHERE:((([Product].[ProdTypeId]=2 OR [Product].[KShau1]<>1) OR [Product].[KShau2]<>1) OR [Product].[KShau3]<>1))
       |              |--Concatenation
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1001]), OBJECT:([DBName].[dbo].[Facts_2006]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_2006].[fwm_mdate]), SEEK:([Facts_2006].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1003]), OBJECT:([DBName].[dbo].[Facts_2007]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_2007].[fwm_mdate]), SEEK:([Facts_2007].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1038]), OBJECT:([DBName].[dbo].[Facts_2008]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_2008].[fwm_mdate]), SEEK:([Facts_2008].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2009].[PK_Facts_2009]), WHERE:([Facts_2009].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2010].[PK_Facts_2010]), WHERE:([Facts_2010].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2011].[PK_Facts_2011]), WHERE:([Facts_2011].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2012].[PK_Facts_2012]), WHERE:([Facts_2012].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2013].[PK_Facts_2013]), WHERE:([Facts_2013].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_2014].[PK_Facts_2014]), WHERE:([Facts_2014].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1283]), OBJECT:([DBName].[dbo].[Facts_vozvr_2007]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_vozvr_2007].[fwm_mdate]), SEEK:([Facts_vozvr_2007].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1318]), OBJECT:([DBName].[dbo].[Facts_vozvr_2008]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_vozvr_2008].[fwm_mdate]), SEEK:([Facts_vozvr_2008].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_vozvr_2009].[PK_Facts_vozvr_2009]), WHERE:([Facts_vozvr_2009].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_vozvr_2010].[PK_Facts_vozvr_2010]), WHERE:([Facts_vozvr_2010].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_vozvr_2011].[PK_Facts_vozvr_2011]), WHERE:([Facts_vozvr_2011].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_vozvr_2012].[PK_Facts_vozvr_2012]), WHERE:([Facts_vozvr_2012].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_Vozvr_2013].[PK_Facts_Vozvr_2013]), WHERE:([Facts_Vozvr_2013].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_Vozvr_2014].[PK_Facts_Vozvr_2014]), WHERE:([Facts_Vozvr_2014].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1563]), OBJECT:([DBName].[dbo].[Facts_pekar_2006]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_pekar_2006].[fwm_mdate]), SEEK:([Facts_pekar_2006].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1598]), OBJECT:([DBName].[dbo].[Facts_pekar_2007]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_pekar_2007].[fwm_mdate]), SEEK:([Facts_pekar_2007].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Bookmark Lookup(BOOKMARK:([Bmk1633]), OBJECT:([DBName].[dbo].[Facts_pekar_2008]))
       |                   |    |--Index Seek(OBJECT:([DBName].[dbo].[Facts_pekar_2008].[fwm_mdate]), SEEK:([Facts_pekar_2008].[ModifiedDate] >= 'Jan  1 2009 12:00AM') ORDERED FORWARD)
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_pekar_2009].[PK_Facts_pekar_2009]), WHERE:([Facts_pekar_2009].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |                   |--Clustered Index Scan(OBJECT:([DBName].[dbo].[Facts_prostor].[PK_Facts_prostor]), WHERE:([Facts_prostor].[ModifiedDate]>='Jan  1 2009 12:00AM'))
       |--Clustered Index Scan(OBJECT:([DBName].[dbo].[t_Clients].[PK_t_Clients] AS [c]), ORDERED FORWARD)



ПС. Microsoft SQL Server 2000 - 8.00.2249 (Intel X86) May 1 2007 18:39:59 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
13 янв 14, 13:33    [15407806]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
нуб987
Guest
так есть ли более оптимальный способ получить список клиентов, на которых были отгрузки, кроме как проверкой exists?
15 янв 14, 08:56    [15416656]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31440
нуб987
так есть ли более оптимальный способ получить список клиентов, на которых были отгрузки, кроме как проверкой exists?
C exists - самый быстрый вариант.

Если бы условия не менялись, можно было бы как то хранить факт отгрузки по клиентам, но в вашем случае это нереально.

Нужно просто оптимизировать запросы и базы, у вас конечно жуть - массовые сканы больших таблиц :-(
15 янв 14, 09:25    [15416736]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
Анонимус2
Guest
select c.id, c.Name
from Clients c
inner join Facts f on c.id = f.ClientId and f.ProductType = 1
15 янв 14, 14:32    [15418792]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
нуб987
Guest
alexeyvg
Нужно просто оптимизировать запросы и базы, у вас конечно жуть - массовые сканы больших таблиц :-(

посмотрел план еще раз после вашего сообщения (не научился еще с планами работать). Действительно, не пойму откуда там сканирование кластерного индекса (поле id в т.фактов), когда проверка идет по дате (обычный индекс)?
16 янв 14, 19:04    [15426391]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
нуб987,

Судя по плану, индексы на поле ModifiedDate есть далеко не везде. Ну или не используются, если есть.

И, конечно, вот это:
Clustered Index Scan(OBJECT:([DBName].[dbo].[Product].[PK_Product]), WHERE:((([Product].[ProdTypeId]=2 OR [Product].[KShau1]<>1) OR [Product].[KShau2]<>1) OR [Product].[KShau3]<>1))
- застрелиться и не жить. Использовать OR - плохая практика, union all работает гораздо лучше. Не уверен, правда, что в вашем случае это получится - запрос может получиться такого размера, что 2000 может отказаться его выполнять...
17 янв 14, 04:26    [15427885]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31440
нуб987
посмотрел план еще раз после вашего сообщения (не научился еще с планами работать). Действительно, не пойму откуда там сканирование кластерного индекса (поле id в т.фактов), когда проверка идет по дате (обычный индекс)?

Могу предоложить, что условие [ModifiedDate]>='Jan 1 2009 12:00AM' удовлетворяет всем записям таблицы [dbo].[Facts_2012].[PK_Facts_2012]...
А вот в таблице [DBName].[dbo].[Facts_2006] наоборот.
Что и видно по плану - сервер хорошо учитывает статистику.

Нужно как то поэксперементировать с индексом для фактов: может, сделать покрывающий индекс на ModifiedDate, ProdId, ClientId...
17 янв 14, 09:35    [15428283]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
aleks2
Guest
нуб987
подскажите, есть ли быстрый способ получить список клиентов, имеющихся в фактах (отгрузки только определенной продукции)?
(есть ли варианты проще/быстрей)?


Я уж устал советовать: Indexed View.

create view Client_ProductType
with shemabinding
as
select ClientId, ProductType, count_big(*) as cnt
from Facts 
group by ClientId, ProductType;

go

create unique clustered index IDX_Client_ProductType on Client_ProductType (ClientId, ProductType);

go
select * from Client_ProductType WITH(NOEXPAND) where ProductType = 1;
17 янв 14, 10:48    [15428500]     Ответить | Цитировать Сообщить модератору
 Re: список клиентов, присутствующих в фактах  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31440
aleks2
Я уж устал советовать: Indexed View.
Дык у ТС меняющийся параметр "ModifiedDate"

Если бы не это, то конечно посоветовали бы Indexed View или хотя бы просто хранение признака в таблице клиента.
17 янв 14, 11:14    [15428650]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить