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

Откуда: Москва
Сообщений: 606
Microsoft SQL Server 2008 (SP3) - 10.0.5520.0 (X64) 
	Jul 11 2014 16:11:50 
	Copyright (c) 1988-2008 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Есть две таблицы, первая: остатки на складе на каждый день по штрихкоду изделия. Дневной остаток порядка 300 тыщ штрихкодов. поле склад - это айд склада где лежит данная позиция, дата - остаток на этот день. ну и сам штрихкод.

CREATE TABLE #OST

code identifierdateinsklad
1AAA101/01/20153
2FFFS01/01/20152
3SSSD01/01/20152
4LLLSD01/01/20151
.................
120AAA102/01/20153
121SSSD02/01/20153
................
1021AAA108/06/20163


Есть вторая таблица: справочник каждого штрихкода- расшифровка что это, сколько весит, и т.д.

CREATE TABLE #IZDELIA


codeidentifiertypvesArtikul
1AAA1Браслет3.66 01Б
2FFFSКольцо4.5201К
3SSSDБраслет3.8801Б
4LLLSDБраслет2.5201Б
.....................



В первой порядка 300 млн записей, во второй 11 млн. в первой уникальность это identifier+datein (т.е. в один на складе может лежать только один штрихкод), во второй уникальность это identifier.
На первой таблице есть два индекса, кластерный по полю Code, счетчик и не кластерный по полю datein. Во второй таблице уникальный кластерный индекс по полю identifier.

Делаю соединение, мне нужно знать, сколько дней лежал на складах Артикул за заданный период времени.


SELECT a.Artikul,sum(a.ves) as ves,sum(a.sht) as sht,count(*) as dney
FROM
(
SELECT o.datein,i.Artikul,sum(i.ves) as ves,count(*) as sht
FROM #OST as o
INNER JOIN #IZDELIA as i on i.identifier=o.identifier
where o.datein>=@FirstDate and o.datein<@Lastdate
GROUP BY o.datein,i.Artikul
) as a
GROUP BY a.artikul


Если выбираю период полгода, запрос не выполняется. Может надо индексы перестроить?
9 июн 16, 12:19    [19275227]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
План выполнения покажите в формате sqlplan.
9 июн 16, 12:31    [19275305]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
а так не тоже?

SELECT i.Artikul,cntDay = COUNT(DISTINCT o.datein) ,sum(i.ves) as ves,count(*) as sht
FROM #OST as o
INNER JOIN #IZDELIA as i on i.identifier=o.identifier
where o.datein>=@FirstDate and o.datein<@Lastdate
GROUP BY i.Artikul
9 июн 16, 12:33    [19275329]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
sti
Member

Откуда:
Сообщений: 769
Если бы Вы структуру не словами описывали, а скрипты привели и план, было бы намного проще.
Как то так по идее:

CREATE UNIQUE NONCLUSTERED INDEX [IX1] ON #OST (datein, identifier)
GO
CREATE NONCLUSTERED INDEX [IX2] ON #IZDELIA (identifier, Artikul) INCLUDE(ves)
GO


Разумеется зависит от того, сколько у вас за эти полгода данных.
9 июн 16, 12:53    [19275503]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
AlanDenton,

SELECT a.FirmName,a.FirmID,a.innerArtikul,a.proba,sum(a.sht) as sht,sum(a.ves) as ves,count(*) as kdn
FROM
(
select f.FirmName,f.FirmID,o.datein,a.innerArtikul,a.proba,count(*) as sht,sum(i.ves) as ves
from #OST as o
INNER JOIN #IZDELIA as i on o.identifier=i.identifier
INNER JOIN #ARTIKULS as a on a.artikul=i.artikul and a.proba=i.proba
left join Attrib.dbo.firmName as f with(nolock) on f.FirmID=i.supplierID
GROUP BY f.FirmName,f.FirmID,a.innerArtikul,a.proba,o.datein
) as a
GROUP BY a.FirmName,a.FirmID,a.innerArtikul,a.proba
  |--Parallelism(Gather Streams)
       |--Compute Scalar(DEFINE:([Expr1013]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1014]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END, [Expr1015]=CONVERT_IMPLICIT(int,[Expr1039],0)))
            |--Stream Aggregate(GROUP BY:([f].[FirmName], [f].[FirmID], [a].[innerArtikul], [i].[proba]) DEFINE:([Expr1039]=Count(*), [Expr1035]=COUNT_BIG([Expr1011]), [Expr1036]=SUM([Expr1011]), [Expr1037]=COUNT_BIG([Expr1012]), [Expr1038]=SUM([Expr1012])))
                 |--Sort(ORDER BY:([f].[FirmName] ASC, [f].[FirmID] ASC, [a].[innerArtikul] ASC, [i].[proba] ASC))
                      |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[globalagg1018],0), [Expr1012]=CASE WHEN [globalagg1020]=(0) THEN NULL ELSE [globalagg1022] END))
                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmName], [f].[FirmID], [a].[innerArtikul], [i].[proba]))
                                |--Stream Aggregate(GROUP BY:([f].[FirmName], [a].[innerArtikul], [i].[proba], [o].[datein]) DEFINE:([globalagg1018]=SUM([partialagg1017]), [globalagg1020]=SUM([partialagg1019]), [globalagg1022]=SUM([partialagg1021]), [f].[FirmID]=ANY([Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID])))
                                     |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmName], [a].[innerArtikul], [i].[proba], [o].[datein]), ORDER BY:([f].[FirmName] ASC, [a].[innerArtikul] ASC, [i].[proba] ASC, [o].[datein] ASC))
                                          |--Stream Aggregate(GROUP BY:([f].[FirmName], [a].[innerArtikul], [i].[proba], [o].[datein]) DEFINE:([partialagg1017]=Count(*), [partialagg1019]=COUNT_BIG([tempdb].[dbo].[#IZDELIA].[ves] as [i].[ves]), [partialagg1021]=SUM([tempdb].[dbo].[#IZDELIA].[ves] as [i].[ves]), [f].[FirmID]=ANY([Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID])))
                                               |--Sort(ORDER BY:([f].[FirmName] ASC, [a].[innerArtikul] ASC, [i].[proba] ASC, [o].[datein] ASC))
                                                    |--Hash Match(Right Outer Join, HASH:([f].[FirmID])=([i].[supplierID]), RESIDUAL:([Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID]=[tempdb].[dbo].[#IZDELIA].[supplierID] as [i].[supplierID]))
                                                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmID]))
                                                         |    |--Index Scan(OBJECT:([Attrib].[dbo].[firmName].[IX_firmName_1] AS [f]))
                                                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[supplierID]))
                                                              |--Hash Match(Inner Join, HASH:([a].[artikul], [a].[proba])=([i].[artikul], [i].[proba]), RESIDUAL:([tempdb].[dbo].[#ARTIKULS].[artikul] as [a].[artikul]=[tempdb].[dbo].[#IZDELIA].[artikul] as [i].[artikul] AND [tempdb].[dbo].[#ARTIKULS].[proba] as [a].[proba]=[tempdb].[dbo].[#IZDELIA].[proba] as [i].[proba]))
                                                                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([a].[artikul], [a].[proba]))
                                                                   |    |--Table Scan(OBJECT:([tempdb].[dbo].[#ARTIKULS] AS [a]))
                                                                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[artikul], [i].[proba]))
                                                                        |--Hash Match(Inner Join, HASH:([o].[identifier])=([i].[identifier]), RESIDUAL:([tempdb].[dbo].[#OST].[identifier] as [o].[identifier]=[tempdb].[dbo].[#IZDELIA].[identifier] as [i].[identifier]))
                                                                             |--Bitmap(HASH:([o].[identifier]), DEFINE:([Opt_Bitmap1016]))
                                                                             |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier]))
                                                                             |         |--Table Scan(OBJECT:([tempdb].[dbo].[#OST] AS [o]))
                                                                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[identifier]))
                                                                                  |--Table Scan(OBJECT:([tempdb].[dbo].[#IZDELIA] AS [i]), WHERE:(PROBE([Opt_Bitmap1016],[tempdb].[dbo].[#IZDELIA].[identifier] as [i].[identifier])))
9 июн 16, 14:44    [19276268]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
Я использую три временные таблицы, чтобы не писать много подзапросов. Потому что каждую таблицу нужно обработать, отфильтровать. а потом уже все соединить. Обработка и запись во временные таблицы занимает порядка 1 минуты, т.к. там миллионы записей. Но в итоговую сводную соединять не хочет, уже и индексы на эти временные таблицы кидал не помогает. Этот план получен для периода равному одному дню.
9 июн 16, 14:47    [19276307]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 606
Запустил без временных таблиц
SELECT a.FirmName,a.FirmID,a.innerArtikul,a.proba,sum(a.sht) as sht,sum(a.ves) as ves,count(*) as kdn
FROM
(
select f.FirmName,f.FirmID,o.datein,a.innerArtikul,a.proba,count(*) as sht,sum(i.ves) as ves
from Ostatki.dbo.Ostatki as o
INNER JOIN main..izdelia as i with(nolock) on o.identifier=i.identifier
INNER JOIN Attrib.dbo.artikuls as a With(nolock) on a.artikul=i.artikul and a.proba=i.proba
left join Attrib.dbo.firmName as f with(nolock) on f.FirmID=i.supplierID
where o.datein>='2016/5/1' and o.datein<='2016/5/31' and a.metall='ЗОЛОТО'
GROUP BY f.FirmName,f.FirmID,a.innerArtikul,a.proba,o.datein
) as a
GROUP BY a.FirmName,a.FirmID,a.innerArtikul,a.proba
  |--Parallelism(Gather Streams)
       |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [Expr1034]=(0) THEN NULL ELSE [Expr1035] END, [Expr1011]=CASE WHEN [Expr1036]=(0) THEN NULL ELSE [Expr1037] END, [Expr1012]=CONVERT_IMPLICIT(int,[Expr1038],0)))
            |--Hash Match(Aggregate, HASH:([f].[FirmName], [f].[FirmID], [a].[innerArtikul], [a].[proba]), RESIDUAL:([Attrib].[dbo].[firmName].[FirmName] as [f].[FirmName] = [Attrib].[dbo].[firmName].[FirmName] as [f].[FirmName] AND [Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID] = [Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID] AND [Attrib].[dbo].[artikuls].[innerArtikul] as [a].[innerArtikul] = [Attrib].[dbo].[artikuls].[innerArtikul] as [a].[innerArtikul] AND [Attrib].[dbo].[artikuls].[proba] as [a].[proba] = [Attrib].[dbo].[artikuls].[proba] as [a].[proba]) DEFINE:([Expr1034]=COUNT_BIG([Expr1008]), [Expr1035]=SUM([Expr1008]), [Expr1036]=COUNT_BIG([Expr1009]), [Expr1037]=SUM([Expr1009]), [Expr1038]=COUNT(*)))
                 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmName], [f].[FirmID], [a].[innerArtikul], [a].[proba]))
                      |--Compute Scalar(DEFINE:([Expr1008]=CONVERT_IMPLICIT(int,[Expr1031],0), [Expr1009]=CASE WHEN [Expr1032]=(0) THEN NULL ELSE [Expr1033] END))
                           |--Hash Match(Aggregate, HASH:([f].[FirmName], [a].[innerArtikul], [a].[proba], [o].[datein]), RESIDUAL:([Attrib].[dbo].[firmName].[FirmName] as [f].[FirmName] = [Attrib].[dbo].[firmName].[FirmName] as [f].[FirmName] AND [Attrib].[dbo].[artikuls].[innerArtikul] as [a].[innerArtikul] = [Attrib].[dbo].[artikuls].[innerArtikul] as [a].[innerArtikul] AND [Attrib].[dbo].[artikuls].[proba] as [a].[proba] = [Attrib].[dbo].[artikuls].[proba] as [a].[proba] AND [Ostatki].[dbo].[Ostatki].[datein] as [o].[datein] = [Ostatki].[dbo].[Ostatki].[datein] as [o].[datein]) DEFINE:([Expr1031]=COUNT(*), [Expr1032]=COUNT_BIG([Main].[dbo].[izdelia].[ves] as [i].[ves]), [Expr1033]=SUM([Main].[dbo].[izdelia].[ves] as [i].[ves]), [f].[FirmID]=ANY([Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID])))
                                |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmName], [a].[innerArtikul], [a].[proba], [o].[datein]))
                                     |--Hash Match(Right Outer Join, HASH:([f].[FirmID])=([i].[supplierID]), RESIDUAL:([Attrib].[dbo].[firmName].[FirmID] as [f].[FirmID]=[Main].[dbo].[izdelia].[supplierID] as [i].[supplierID]))
                                          |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([f].[FirmID]))
                                          |    |--Index Scan(OBJECT:([Attrib].[dbo].[firmName].[IX_firmName_1] AS [f]))
                                          |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[supplierID]))
                                               |--Hash Match(Inner Join, HASH:([a].[artikul], [a].[proba])=([i].[artikul], [i].[proba]), RESIDUAL:([Attrib].[dbo].[artikuls].[artikul] as [a].[artikul]=[Main].[dbo].[izdelia].[artikul] as [i].[artikul] AND [Attrib].[dbo].[artikuls].[proba] as [a].[proba]=[Main].[dbo].[izdelia].[proba] as [i].[proba]))
                                                    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([a].[artikul], [a].[proba]))
                                                    |    |--Index Seek(OBJECT:([Attrib].[dbo].[artikuls].[IX_forStonesGroup] AS [a]), SEEK:([a].[metall]=N'ЗОЛОТО') ORDERED FORWARD)
                                                    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[artikul], [i].[proba]))
                                                         |--Hash Match(Inner Join, HASH:([o].[identifier])=([i].[identifier]), RESIDUAL:([Ostatki].[dbo].[Ostatki].[identifier] as [o].[identifier]=[Main].[dbo].[izdelia].[identifier] as [i].[identifier]))
                                                              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([o].[identifier]))
                                                              |    |--Index Seek(OBJECT:([Ostatki].[dbo].[Ostatki].[IX_datein] AS [o]), SEEK:([o].[datein] >= '2016-05-01 00:00:00.000' AND [o].[datein] <= '2016-05-31 00:00:00.000') ORDERED FORWARD)
                                                              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([i].[identifier]))
                                                                   |--Index Scan(OBJECT:([Main].[dbo].[izdelia].[NonClusteredIndex-20160608-161427] AS [i]))
9 июн 16, 17:01    [19277283]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
+
CREATE NONCLUSTERED INDEX ix
    ON Attrib.dbo.artikuls (metall, artikul, proba)
    INCLUDE (innerArtikul)
GO

CREATE NONCLUSTERED INDEX ix
    ON Ostatki.dbo.Ostatki (datein, identifier)
GO

SELECT a.FirmName,
       a.FirmID,
       t.*
FROM (
    SELECT a.supplierID,
           a.innerArtikul,
           a.proba,
           SUM(a.sht) AS sht,
           SUM(a.ves) AS ves,
           COUNT(*) AS kdn
    FROM (
        SELECT i.supplierID,
               o.datein,
               a.innerArtikul,
               a.proba,
               COUNT(*) AS sht,
               SUM(i.ves) AS ves
        FROM Ostatki.dbo.Ostatki o
        JOIN main.dbo.izdelia i ON o.identifier = i.identifier
        JOIN Attrib.dbo.artikuls a ON a.artikul = i.artikul AND a.proba = i.proba
        WHERE o.datein BETWEEN '20160501' AND '20160531'
            AND a.metall = N'ЗОЛОТО'
        GROUP BY i.supplierID,
                 a.innerArtikul,
                 a.proba,
                 o.datein
    ) a
    GROUP BY a.supplierID,
             a.innerArtikul,
             a.proba
) t
LEFT JOIN Attrib.dbo.firmName f ON f.FirmID = t.supplierID
9 июн 16, 17:20    [19277351]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с индексами, какие лучше проставить?  [new]
aleks2
Guest
Вынеси, страдалец, соединение из под группировки.
9 июн 16, 19:45    [19277791]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить