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

Откуда:
Сообщений: 612
Добрый день,
наблюдаю странную картину при казалось бы простом SELECT.
Есть таблица заявок по странам с id клиента, id страны и датой и временем поступления.
+ DDL
/*dim_applications*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[dim_applications](
		[id] [bigint] NOT NULL,
		[country_id] [int] NOT NULL,
		[customer_id] [bigint] NOT NULL,
		[first_status_day_date] [date] NULL,
		[first_status_time_of_day] [time](0) NULL,
		[campaign_id] [int] NULL,
		[campaign_response_time][int] NULL
	) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND name = N'PK_dim_applications')
ALTER TABLE [dbo].[dim_applications] ADD CONSTRAINT [PK_dim_applications] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[dim_applications]') AND name = N'IX_dim_applications')
CREATE NONCLUSTERED INDEX [IX_dim_applications] ON [dbo].[dim_applications]
(
	[country_id] ASC
)
INCLUDE ([customer_id], [first_status_day_date], [first_status_time_of_day]) 
GO

сейчас там 6 млн. записей.

Также есть лог рекламных рассылок клиентам от CRMщиков - когда и какая рекламная кампания была отправлена какому клиенту.
+ DDL
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'fact_customers_campaigns' AND TABLE_CATALOG = 'dwh')
BEGIN
	CREATE TABLE [dbo].[fact_customers_campaigns](
		[campaign_id] [int] NOT NULL,
		[customer_id] [bigint] NULL,
		[day_date] [date] NOT NULL,
		[time_of_day] [time](0) NOT NULL
		) ON [PRIMARY]
END 
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[fact_customers_campaigns]') AND name = N'IX_fact_customers_campaigns_3')
CREATE NONCLUSTERED INDEX [IX_fact_customers_campaigns_3] ON [dbo].[fact_customers_campaigns]
(
	[customer_id] ASC,
	[day_date] DESC,
	[time_of_day] DESC
)
INCLUDE([campaign_id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


там около 1.6 млн. записей

требуется для каждой заявки для заданной страны в dim_applications выбрать campaign_id ближайшей по времени кампании из fact_customers_campaigns, да так, чтобы разница между датой кампании и датой заявки была не больше 14 дней. "точка соприкосновения" - customer_id.

сам запрос для первой в списке страны
SELECT 
	a.id
	,(SELECT TOP 1 
			c.campaign_id 
		FROM [dwh].[dbo].[fact_customers_campaigns] c 
		WHERE 1 = 1
			AND c.[customer_id] = a.[customer_id] 
			AND DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14
			ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)
FROM [dwh].[dbo].[dim_applications] a
WHERE 1 = 1
	AND a.[country_id] = 1

отрабатывает 1.5 млн. записей за 7 секунд.
Но тот же запрос для последней в списке страны ([country_id] = 14, 1.2 млн. записей в dim_applications) отрабатывает очень медленно - 2 минуты и более. При этом данные как бы отдаются "рывками" или порциями. Для всех остальных стран между этими двумя все работает как надо, список id выводится быстро и "гладко".
Во всех случаях используется Index Seek по обеим таблицам (Actual Execution Plan).
почему так? причем одинаковое поведение на
Microsoft SQL Server 2012 (SP1) - 11.0.3339.0 (X64) 
	Jan 14 2013 19:02:10 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

и
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 
	Dec  9 2011 11:27:20 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Как победить? Все статистики обновлены, индексы регулярно ребилдятся...
28 июн 13, 16:21    [14498279]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
Диклевич Александр,
А где же самое интересное? Где планы?
28 июн 13, 22:31    [14499468]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 612
вот, SQL Server 2008 R2, машина слабее, поэтому время немного больше.
+ country_id = 1, 18 сек

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2796.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1547490" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="320.002" StatementText="SELECT &#xD;&#xA;    a.id&#xD;&#xA;    ,(SELECT TOP 1 &#xD;&#xA;            c.campaign_id &#xD;&#xA;        FROM [dwh].[dbo].[fact_customers_campaigns] c &#xD;&#xA;        WHERE 1 = 1&#xD;&#xA;            AND c.[customer_id] = a.[customer_id] &#xD;&#xA;            AND DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14&#xD;&#xA;            ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)&#xD;&#xA;FROM [dwh].[dbo].[dim_applications] a&#xD;&#xA;WHERE 1 = 1&#xD;&#xA;    AND a.[country_id] = 1" StatementType="SELECT" QueryHash="0x2A240F5ADDD3C80D" QueryPlanHash="0x0D728CFCA7D5C264">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="2" MemoryGrant="0" CachedPlanSize="40" CompileTime="4" CompileCPU="4" CompileMemory="432">
            <RelOp AvgRowSize="19" EstimateCPU="0.154749" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1547490" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="320.002">
              <OutputList>
                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                <ColumnReference Column="Expr1006" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[dwh].[dbo].[fact_customers_campaigns].[campaign_id] as [c].[campaign_id]">
                      <Identifier>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </Identifier>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="19" EstimateCPU="2.27894" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1547490" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="319.847">
                  <OutputList>
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1547491" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <RelOp AvgRowSize="19" EstimateCPU="3.23426" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1547490" LogicalOp="Left Outer Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="317.568">
                      <OutputList>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="2" ActualRows="771330" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="1" ActualRows="776161" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          <ColumnReference Column="Expr1008" />
                        </OuterReferences>
                        <RelOp AvgRowSize="26" EstimateCPU="0.851199" EstimateIO="5.11986" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1547490" LogicalOp="Index Seek" NodeId="4" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="5.97106" TableCardinality="6023900">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="2" ActualRows="771330" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="776161" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Index="[IX_dim_applications]" Alias="[a]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="country_id" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="(1)">
                                        <Const ConstValue="(1)" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="1547490" EstimateRewinds="2.11356" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="true" PhysicalOp="Top" EstimatedTotalSubtreeCost="308.363">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="2" ActualRows="3423" ActualEndOfScans="767907" ActualExecutions="771330" />
                            <RunTimeCountersPerThread Thread="1" ActualRows="2819" ActualEndOfScans="773342" ActualExecutions="776161" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <Top RowCount="false" IsPercent="false" WithTies="false">
                            <TopExpression>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </TopExpression>
                            <RelOp AvgRowSize="25" EstimateCPU="0.000167715" EstimateIO="0.003125" EstimateRebinds="1547490" EstimateRewinds="2.11356" EstimateRows="1" LogicalOp="Index Seek" NodeId="7" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="279.713" TableCardinality="1633240">
                              <OutputList>
                                <ColumnReference Column="Bmk1002" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="2" ActualRows="3423" ActualEndOfScans="767907" ActualExecutions="771330" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="2819" ActualEndOfScans="773342" ActualExecutions="776161" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Bmk1002" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Index="[IX_fact_customers_campaigns_3]" Alias="[c]" IndexKind="NonClustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="customer_id" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[dwh].[dbo].[dim_applications].[customer_id] as [a].[customer_id]">
                                            <Identifier>
                                              <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                                <Predicate>
                                  <ScalarOperator ScalarString="datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&gt;=(0) AND datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&lt;=(14)">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="GE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(0)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="LE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(14)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </Top>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                  </Parallelism>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>



+ country_id = 14, 7 мин 35 сек

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2796.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1255640" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="263.462" StatementText="SELECT &#xD;&#xA;    a.id&#xD;&#xA;    ,(SELECT TOP 1 &#xD;&#xA;            c.campaign_id &#xD;&#xA;        FROM [dwh].[dbo].[fact_customers_campaigns] c &#xD;&#xA;        WHERE 1 = 1&#xD;&#xA;            AND c.[customer_id] = a.[customer_id] &#xD;&#xA;            AND DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14&#xD;&#xA;            ORDER BY c.[day_date] DESC, c.[time_of_day] DESC)&#xD;&#xA;FROM [dwh].[dbo].[dim_applications] a&#xD;&#xA;WHERE 1 = 1&#xD;&#xA;    AND a.[country_id] = 14" StatementType="SELECT" QueryHash="0x2A240F5ADDD3C80D" QueryPlanHash="0x0D728CFCA7D5C264">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="2" MemoryGrant="0" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="432">
            <RelOp AvgRowSize="19" EstimateCPU="0.125564" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1255640" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="263.462">
              <OutputList>
                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                <ColumnReference Column="Expr1006" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[dwh].[dbo].[fact_customers_campaigns].[campaign_id] as [c].[campaign_id]">
                      <Identifier>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </Identifier>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="19" EstimateCPU="1.85452" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1255640" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="263.336">
                  <OutputList>
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="1255641" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Parallelism>
                    <RelOp AvgRowSize="19" EstimateCPU="2.62429" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1255640" LogicalOp="Left Outer Join" NodeId="2" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="261.482">
                      <OutputList>
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                        <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="1" ActualRows="691880" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="2" ActualRows="563761" ActualEndOfScans="1" ActualExecutions="1" />
                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                      </RunTimeInformation>
                      <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
                        <OuterReferences>
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          <ColumnReference Column="Expr1008" />
                        </OuterReferences>
                        <RelOp AvgRowSize="26" EstimateCPU="0.690681" EstimateIO="4.15472" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1255640" LogicalOp="Index Seek" NodeId="4" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="4.84541" TableCardinality="6023900">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="1" ActualRows="691880" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="563761" ActualEndOfScans="1" ActualExecutions="1" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Index="[IX_dim_applications]" Alias="[a]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="country_id" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="(14)">
                                        <Const ConstValue="(14)" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="1255640" EstimateRewinds="1.63462" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="true" PhysicalOp="Top" EstimatedTotalSubtreeCost="254.012">
                          <OutputList>
                            <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="1" ActualRows="67116" ActualEndOfScans="624764" ActualExecutions="691880" />
                            <RunTimeCountersPerThread Thread="2" ActualRows="72774" ActualEndOfScans="490987" ActualExecutions="563761" />
                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                          </RunTimeInformation>
                          <Top RowCount="false" IsPercent="false" WithTies="false">
                            <TopExpression>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </TopExpression>
                            <RelOp AvgRowSize="25" EstimateCPU="0.000167715" EstimateIO="0.003125" EstimateRebinds="1255640" EstimateRewinds="1.63462" EstimateRows="1" LogicalOp="Index Seek" NodeId="7" Parallel="true" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="230.765" TableCardinality="1633240">
                              <OutputList>
                                <ColumnReference Column="Bmk1002" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="1" ActualRows="67116" ActualEndOfScans="624764" ActualExecutions="691880" />
                                <RunTimeCountersPerThread Thread="2" ActualRows="72774" ActualEndOfScans="490987" ActualExecutions="563761" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Bmk1002" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="campaign_id" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="time_of_day" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Index="[IX_fact_customers_campaigns_3]" Alias="[c]" IndexKind="NonClustered" />
                                <SeekPredicates>
                                  <SeekPredicateNew>
                                    <SeekKeys>
                                      <Prefix ScanType="EQ">
                                        <RangeColumns>
                                          <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="customer_id" />
                                        </RangeColumns>
                                        <RangeExpressions>
                                          <ScalarOperator ScalarString="[dwh].[dbo].[dim_applications].[customer_id] as [a].[customer_id]">
                                            <Identifier>
                                              <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="customer_id" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </RangeExpressions>
                                      </Prefix>
                                    </SeekKeys>
                                  </SeekPredicateNew>
                                </SeekPredicates>
                                <Predicate>
                                  <ScalarOperator ScalarString="datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&gt;=(0) AND datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[fact_customers_campaigns].[day_date] as [c].[day_date],0),CONVERT_IMPLICIT(datetimeoffset(7),[dwh].[dbo].[dim_applications].[first_status_day_date] as [a].[first_status_day_date],0))&lt;=(14)">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="GE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(0)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="LE">
                                          <ScalarOperator>
                                            <Intrinsic FunctionName="datediff">
                                              <ScalarOperator>
                                                <Const ConstValue="(4)" />
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[fact_customers_campaigns]" Alias="[c]" Column="day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Convert DataType="datetimeoffset" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[dwh]" Schema="[dbo]" Table="[dim_applications]" Alias="[a]" Column="first_status_day_date" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                            </Intrinsic>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="(14)" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </Top>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                  </Parallelism>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

29 июн 13, 00:57    [14499940]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
Так легче не станет?

SELECT 
	a.id
	,(SELECT TOP 1 
			c.campaign_id 
		FROM [dbo].[fact_customers_campaigns] c 
		WHERE c.[customer_id] = a.[customer_id] 
			and c.[day_date] >= dateadd(day,-14,a.[first_status_day_date])
			and c.[day_date] <= a.[first_status_day_date]
			ORDER BY c.[customer_id],c.[day_date] DESC, c.[time_of_day] DESC)
FROM [dbo].[dim_applications] a
WHERE a.[country_id] = 1
29 июн 13, 09:42    [14500161]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
ertyey
Guest
почему-то в планево внутреннем цикле nested loops после top(1) большое число строк в первом запросе их 6200 строк, во втором ~ 139000.
29 июн 13, 10:45    [14500184]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
Диклевич Александр
Member

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

хм, помогло, спасибо!
а в чем секрет?
29 июн 13, 16:56    [14500583]     Ответить | Цитировать Сообщить модератору
 Re: Странное поведение выборки  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
Диклевич Александр,

из-за того, что в подзапросе явное условие только по customer_id - поиск в индексе производится только по 1 полю, выбирается большое число записей, для каждой из которых проверяется условие
DATEDIFF(DD, c.[day_date], a.[first_status_day_date]) BETWEEN 0 AND 14

Во втором случае поиск идет по 2 полям индекса - выбирается меньшее число записей и без всякой обработки выбирается первая запись.
30 июн 13, 07:31    [14501548]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить