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

Откуда: Moscow
Сообщений: 1172
Доброго времени суток.

Что то лыжи в пятницу не едут:
Имеем:
+ запрос
IF OBJECT_ID('tempdb..#tmp_Accounts') IS NOT NULL DROP TABLE #tmp_Accounts
CREATE TABLE #tmp_Accounts (
      [ResourceID] NUMERIC(15, 0)
)

IF OBJECT_ID('tempdb..#tmp_AccDetails') IS NOT NULL DROP TABLE #tmp_AccDetails
CREATE TABLE #tmp_AccDetails (
      [ResourceID] NUMERIC(15, 0),
      [Date] DATETIME,
      [TurnsDebt] MONEY,
      [TurnCred] MONEY
)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--delete from tDocMark where SPID = @@SPID
--insert into tDocMark (SPID, type,ID) values (@@SPID, 41, 2000) 
DECLARE
       @ThresholdQty MONEY = 600000.00,
       @ObjClassifierID DSIDENTIFIER = 10000028564,
       @DateStart DATETIME = '20170512',
       @DateEnd DATETIME = '20170512'

INSERT INTO #tmp_Accounts ([ResourceID])
      SELECT r.[ResourceID]
      FROM [dbo].[tResource] r
          JOIN [dbo].[tDocMark] dm on dm.[SPID] = @@SPID AND dm.[Type] = 41 AND dm.[ID] = r.[InstitutionID]
      WHERE r.[ResourceType] = 1
        AND r.[BalanceID] IN (2140, 55015845)
        AND r.[AccOrder] > ''
        AND (r.[DateEnd] = '19000101' OR r.[DateEnd] >= '20170512')
      GROUP BY r.[ResourceID]

DELETE FROM #tmp_Accounts
 WHERE NOT EXISTS (SELECT 1 FROM [dbo].[tResourceBind] rb
                                 JOIN [dbo].[tObjClsRelation] ocr ON ocr.[ObjectID] = rb.[ParentID] AND ocr.[ObjType] = 3
                                     JOIN [dbo].[tObjClassifier] oc on oc.[ObjClassifierID] = ocr.[ObjClassifierID] AND oc.[ParentID] = @ObjClassifierID
                             WHERE rb.[ChildID] = #tmp_Accounts.[ResourceID])
OPTION (OPTIMIZE FOR (@ObjClassifierID = 10000028564))

IF OBJECT_ID('tempdb..#tmp_DATELIST') IS NOT NULL DROP TABLE #tmp_DATELIST
CREATE TABLE #tmp_DATELIST ([Date] DATETIME)

INSERT INTO #tmp_DATELIST
  SELECT TRY_CONVERT(DATETIME, CONVERT(VARCHAR, [DateInt]))
  FROM [dbo].[tCalendarContent]
  WHERE [CalendarID] = 1
    AND [DateInt] >= CONVERT(INT, CONVERT(VARCHAR, @DateStart, 112))
    AND [DateInt] <= CONVERT(INT, CONVERT(VARCHAR, @DateEnd, 112))

INSERT INTO #tmp_AccDetails ([ResourceID], [Date])--, [TurnsDebt], [TurnsCred])--, [Flags])
  SELECT
        a.[ResourceID],
        op.[OperDate]
--SUM(op.[QtyBs] * IIF(op.[CharType] = 1, 1, 0)),
--SUM(op.[QtyBs] * IIF(op.[CharType] = -1, 1, 0))--,
--0 +
--1 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty AND op.[CharType] = 1, 1, NULL))) +
--2 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty AND op.[CharType] = -1, 1, NULL))) +
--4 * IIF(SUM(op.[QtyBs] * IIF(op.[CharType] = 1, 1, 0)) >= @ThresholdQty, 1, 0) +
--8 * IIF(SUM(op.[QtyBs] * IIF(op.[CharType] = -1, 1, 0)) >= @ThresholdQty, 1, 0) +
--16 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty, 1, NULL)))
FROM [dbo].[tOperPart] op
    JOIN #tmp_Accounts a ON a.[ResourceID] = op.[ResourceID]
    JOIN #tmp_DATELIST d ON d.[Date] = op.[OperDate]
WHERE op.[Confirmed] = 1
  --AND op.[OperDate] = '20170512'
GROUP BY a.[ResourceID], op.[OperDate]


И вот тут я сталкиваюсь с sort warning но не пойму почему, в запросе не используется явное предложение ORDER BY.
причем если изменить предикат по OperDate вместо джоина таблицы дат то сортировка пропадает и используется не Stream Aggregate а Hash Aggregate.

Кто может пояснить почему при аргерации без указания ORDER BY результат перед агрегацией сортируется и где об этом почитать?

К сообщению приложен файл (sw.7z - 8Kb) cкачать
12 май 17, 18:30    [20477384]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
да, табличка tOperPart
+

  <OBJECT_INDEXES>
    <INDEX ID="2" Name="XAK1tOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="0" IsUnique="0" FillFactor="100" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="ResourceID" Type="DSIDENTIFIER" />
          <COLUMN ID="2" Name="OperDate" Type="DSOPERDAY" />
          <COLUMN ID="3" Name="Confirmed" Type="DSTINYINT" />
          <COLUMN ID="4" Name="CharType" Type="smallint" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
    <INDEX ID="3" Name="XIE2tOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="0" IsUnique="0" FillFactor="100" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="ParentID" Type="DSIDENTIFIER" />
          <COLUMN ID="2" Name="OperTemplateID" Type="DSIDENTIFIER" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
    <INDEX ID="4" Name="XIE3tOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="0" IsUnique="0" FillFactor="100" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="DealID" Type="DSIDENTIFIER" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
    <INDEX ID="5" Name="XIE4tOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="0" IsUnique="0" FillFactor="100" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="DealTransactID" Type="DSIDENTIFIER" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
    <INDEX ID="7" Name="XIE8tOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="0" IsUnique="0" FillFactor="100" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="BalanceID" Type="DSIDENTIFIER" />
          <COLUMN ID="2" Name="InstitutionID" Type="DSIDENTIFIER" />
          <COLUMN ID="3" Name="OperDate" Type="DSOPERDAY" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
    <INDEX ID="8" Name="XPKtOperPart" Type="NONCLUSTERED" Location="workdb_IDX" Ignore_Dup_Key="0" IsPrimaryKey="1" IsUnique="0" FillFactor="80" IsPadded="0" IsDisabled="0" AllowRowLocks="1" AllowPageLocks="0" IsFulltextKey="0" IsColumnstore="0">
      <COLUMNS>
        <KEY_COLUMNS>
          <COLUMN ID="1" Name="OperationID" Type="DSIDENTIFIER" />
          <COLUMN ID="2" Name="CharType" Type="smallint" />
          <COLUMN ID="3" Name="ResourceID" Type="DSIDENTIFIER" />
          <COLUMN ID="4" Name="AccountingType" Type="DSINT_KEY" />
        </KEY_COLUMNS>
      </COLUMNS>
    </INDEX>
  </OBJECT_INDEXES>

IF OBJECT_ID('[dbo].[tOperPart]', 'U') IS NOT NULL DROP TABLE [dbo].[tOperPart]
GO
CREATE TABLE [dbo].[tOperPart] (
      [OperationID] [DSIDENTIFIER] NOT NULL,
      [CharType] [SMALLINT] NOT NULL,
      [ResourceID] [DSIDENTIFIER] NOT NULL,
      [OperDate] [DSOPERDAY] NOT NULL,
      [Confirmed] [DSTINYINT] NOT NULL,
      [Qty] [DSBIGMONEY] NOT NULL,
      [QtyBs] [DSBIGMONEY] NOT NULL,
      [QtyPos] [DSBIGMONEY] NOT NULL,
      [FundID] [DSIDENTIFIER] NOT NULL,
      [OperSetID] [DSIDENTIFIER] NOT NULL,
      [BatchID] [DSIDENTIFIER] NOT NULL,
      [OperTypeID] [DSIDENTIFIER] NOT NULL,
      [BalanceID] [DSIDENTIFIER] NOT NULL,
      [ParentID] [DSIDENTIFIER] NOT NULL,
      [DealID] [DSIDENTIFIER] NOT NULL,
      [DealTransactID] [DSIDENTIFIER] NOT NULL,
      [InstrumentID] [DSIDENTIFIER] NOT NULL,
      [InstitutionID] [DSIDENTIFIER] NOT NULL,
      [InputDocID] [DSIDENTIFIER] NOT NULL,
      [SecurityID] [DSIDENTIFIER] NOT NULL,
      [ValueDate] [DSOPERDAY] NOT NULL,
      [Course] [DSFLOAT] NOT NULL,
      [TransactType] [DSTINYINT] NOT NULL,
      [OperSetNum] [DSTINYINT] NOT NULL,
      [OpCode] [DSTINYINT] NOT NULL,
      [UserID] [DSIDENTIFIER] NOT NULL,
      [InDateTime] [DATETIME] NOT NULL,
      [Number] [VARCHAR](20) COLLATE Cyrillic_General_CI_AS NOT NULL,
      [Comment] [DSCOMMENT] COLLATE Cyrillic_General_CI_AS NOT NULL,
      [DealDate] [DSOPERDAY] NOT NULL,
      [ExtrDate] [DSOPERDAY] NOT NULL,
      [Account] [DSVARFULLNAME] COLLATE Cyrillic_General_CI_AS NOT NULL,
      [TypeMask] [DSINT_KEY] NOT NULL,
      [Comment2] [DSCOMMENT] COLLATE Cyrillic_General_CI_AS NOT NULL,
      [ExternalID] [DSIDENTIFIER] NOT NULL,
      [OperTemplateID] [DSIDENTIFIER] NOT NULL,
      [AccountingType] [DSINT_KEY] NOT NULL
)
GO

CREATE NONCLUSTERED INDEX [XAK1tOperPart] ON [dbo].[tOperPart](
     [ResourceID] ASC,
     [OperDate] ASC,
     [Confirmed] ASC,
     [CharType] ASC
)
GO
CREATE NONCLUSTERED INDEX [XIE2tOperPart] ON [dbo].[tOperPart](
     [ParentID] ASC,
     [OperTemplateID] ASC
)
GO
CREATE NONCLUSTERED INDEX [XIE3tOperPart] ON [dbo].[tOperPart](
     [DealID] ASC
)
GO
CREATE NONCLUSTERED INDEX [XIE4tOperPart] ON [dbo].[tOperPart](
     [DealTransactID] ASC
)
GO
CREATE NONCLUSTERED INDEX [XIE8tOperPart] ON [dbo].[tOperPart](
     [BalanceID] ASC,
     [InstitutionID] ASC,
     [OperDate] ASC
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [XPKtOperPart] ON [dbo].[tOperPart](
     [OperationID] ASC,
     [CharType] ASC,
     [ResourceID] ASC,
     [AccountingType] ASC
)
GO


12 май 17, 18:33    [20477391]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
И главное, как в таком случае бороться со спиллами?

Оно нормально так ошибается в оценке O_o
EstimateRows="358.337"
ActualRows="53468"
12 май 17, 18:41    [20477409]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
aleks2
Guest
felix_ff

Кто может пояснить почему при аргерации без указания ORDER BY результат перед агрегацией сортируется и где об этом почитать?


Патаму, что объединение/агрегация упорядоченных последовательностей - очень эффективный алгоритм.
12 май 17, 19:06    [20477480]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
Sort warning уходит при вот таком шаманстве:

INSERT INTO #tmp_AccDetails ([ResourceID], [Date], [TurnsDebt], [TurnsCred])--, [Flags])
  SELECT
        a.[ResourceID],
        op.[OperDate],
        SUM(op.[QtyBs] * IIF(op.[CharType] = 1, 1, 0)),
        SUM(op.[QtyBs] * IIF(op.[CharType] = -1, 1, 0))--,
--0 +
--1 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty AND op.[CharType] = 1, 1, NULL))) +
--2 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty AND op.[CharType] = -1, 1, NULL))) +
--4 * IIF(SUM(op.[QtyBs] * IIF(op.[CharType] = 1, 1, 0)) >= @ThresholdQty, 1, 0) +
--8 * IIF(SUM(op.[QtyBs] * IIF(op.[CharType] = -1, 1, 0)) >= @ThresholdQty, 1, 0) +
--16 * SIGN(COUNT(IIF(op.[QtyBs] >= @ThresholdQty, 1, NULL)))
FROM #tmp_DATELIST d 
    CROSS JOIN #tmp_Accounts a
         JOIN [dbo].[tOperPart] op ON op.[ResourceID] = a.[ResourceID] AND op.[OperDate] = d.[Date]
WHERE op.[Confirmed] = 1
  --AND op.[OperDate] = '20170512'
GROUP BY a.[ResourceID], op.[OperDate]
OPTION (FORCE ORDER)



во еще интереснее увидел: начинает шмалять sorw warnings на этой инструкции:

INSERT INTO #tmp_Accounts ([ResourceID])
      SELECT r.[ResourceID]
      FROM [dbo].[tResource] r
          JOIN [dbo].[tDocMark] dm on dm.[SPID] = @@SPID AND dm.[Type] = 41 AND dm.[ID] = r.[InstitutionID]
      WHERE r.[ResourceType] = 1
        AND r.[BalanceID] IN (2140, 55015845)
        AND r.[AccOrder] > ''
        AND (r.[DateEnd] = '19000101' OR r.[DateEnd] >= '20170512')
      GROUP BY r.[ResourceID]

при том sw возникает только когда оптимизатор жестко промахивается в плане кардинальности строк для сортировки, а промахивается он покуда используется @@SPID если указать константу, сортировка для ключа индекса есть, но вот уже самого sort warnings не наблюдается
12 май 17, 19:34    [20477550]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
вот план с варнингом

К сообщению приложен файл (sw_err_@@SPID_12052017.sqlplan - 43Kb) cкачать
12 май 17, 19:38    [20477558]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
и вот без

К сообщению приложен файл (nosw_err_155_12052017.sqlplan - 37Kb) cкачать
12 май 17, 19:39    [20477560]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
invm
Member

Откуда: Москва
Сообщений: 9125
felix_ff
Кто может пояснить почему при аргерации без указания ORDER BY результат перед агрегацией сортируется
Потому что из-за малого оценочного числа строк оптимизатор счел более выгодным order by + stream aggregate, чем hash aggregate.
Рулить можно option(order group) и option(hash group) соответственно. Hash aggregate тоже может дать spill.
Можете сравнить итоговую стоимость запроса и с той и с другой опцией.

Вы своим "шаманством" спровоцировали другую оценку и оптимизатор выбрал hash aggregate.
12 май 17, 23:13    [20477976]     Ответить | Цитировать Сообщить модератору
 Re: Sort Warnings  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Option recompile?
15 май 17, 08:41    [20480997]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить