July 16, 2012 at 8:16 am
it is quite slow as you will see from the execution plan it should be returning just over 1.5 million rows but as you can see in the Stream Aggregate its returning 18 billion.
t-sql:
SELECT
d.Company,
d.TRXDATE,
l.ACTINDX,
SUM (l.CRDTAMNT) AS CRDTAMNT,
SUM (l.DEBITAMT) AS DEBITAMT,
MAX (l.RowIn) AS LastModified
FROM
(
SELECT
DISTINCT
Company,
TRXDATE
FROM dbo.tblGeneralLedger
WHERE (RowOut IS NULL)
)
AS d
INNER JOIN
dbo.tblGeneralLedger AS l
ON
l.Company = d.Company AND
l.TRXDATE <= d.TRXDATE AND
l.RowOut IS NULL
INNER JOIN
dbo.tblGeneralLedgerAccounts AS a
ON
a.Company = d.Company AND
a.ACTINDX = l.ACTINDX AND
a.RowOut IS NULL
INNER JOIN
dbo.tblGeneralLedgerAccountCategories AS c
ON
c.Company = d.Company AND
c.ACCATNUM = a.ACCATNUM AND
c.RowOut IS NULL
WHERE
(c.IsBalanceSheet = 1) AND
(c.IsIncluded = 1)
GROUP BY
d.Company,
d.TRXDATE,
l.ACTINDX
DDL info:
USE [ODS]
GO
CREATE TABLE [dbo].[tblGeneralLedger](
[Company] [varchar](5) NOT NULL,
[TRXDATE] [datetime] NOT NULL,
[ACTINDX] [int] NOT NULL,
[CRDTAMNT] [numeric](19, 5) NOT NULL,
[DEBITAMT] [numeric](19, 5) NOT NULL,
[RowIn] [datetime] NOT NULL,
[RowOut] [datetime] NULL,
CONSTRAINT [PK_tblGeneralLedger] PRIMARY KEY NONCLUSTERED
(
[GeneralLedgerRowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblGeneralLedgerAccounts](
[AccountRowID] [int] IDENTITY(1,1) NOT NULL,
[RowIn] [datetime] NOT NULL,
[RowOut] [datetime] NULL,
[Company] [varchar](5) NOT NULL,
[ACTINDX] [int] NOT NULL,
CONSTRAINT [PK_TBLGENERALLEDGERACCOUNTS] PRIMARY KEY NONCLUSTERED
(
[AccountRowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblGeneralLedgerAccountCategories](
[CategoryRowID] [int] IDENTITY(1,1) NOT NULL,
[RowIn] [datetime] NOT NULL,
[RowOut] [datetime] NULL,
[Company] [varchar](5) NOT NULL,
[IsBalanceSheet] [bit] NOT NULL,
[IsIncluded] [bit] NOT NULL,
CONSTRAINT [PK_TBLGENERALLEDGERACCOUNTCATE] PRIMARY KEY NONCLUSTERED
(
[CategoryRowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
and lastely here is my execution plan:
<?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.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="18429500000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="72100.4" StatementText="SELECT d.Company, d.TRXDATE, l.ACTINDX, SUM (l.CRDTAMNT) AS CRDTAMNT, SUM (l.DEBITAMT) AS DEBITAMT, MAX (l.RowIn) AS LastModified FROM ( SELECT DISTINCT Company, TRXDATE FROM dbo.tblGeneralLedger WHERE (RowOut IS NULL) ) AS d INNER JOIN dbo.tblGeneralLedger AS l ON l.Company = d.Company AND l.TRXDATE <= d.TRXDATE AND l.RowOut IS NULL INNER JOIN dbo.tblGeneralLedgerAccounts AS a ON a.Company = d.Company AND a.ACTINDX = l.ACTINDX AND a.RowOut IS NULL INNER JOIN dbo.tblGeneralLedgerAccountCategories AS c ON c.Company = d.Company AND c.ACCATNUM = a.ACCATNUM AND c.RowOut IS NULL WHERE (c.IsBalanceSheet = 1) AND (c.IsIncluded = 1) GROUP BY d.Company, d.TRXDATE, l.ACTINDX" StatementType="SELECT" ParameterizedText="(@0 int,@1 int)select d . Company , d . TRXDATE , l . ACTINDX , SUM ( l . CRDTAMNT ) as CRDTAMNT , SUM ( l . DEBITAMT ) as DEBITAMT , MAX ( l . RowIn ) as LastModified from ( select distinct Company , TRXDATE from dbo . tblGeneralLedger where ( RowOut is null ) ) as d inner join dbo . tblGeneralLedger as l on l . Company = d . Company and l . TRXDATE < = d . TRXDATE and l . RowOut is null inner join dbo . tblGeneralLedgerAccounts as a on a . Company = d . Company and a . ACTINDX = l . ACTINDX and a . RowOut is null inner join dbo . tblGeneralLedgerAccountCategories as c on c . Company = d . Company and c . ACCATNUM = a . ACCATNUM and c . RowOut is null where ( c . IsBalanceSheet = @0 ) and ( c . IsIncluded = @1 ) group by d . Company , d . TRXDATE , l . ACTINDX" QueryHash="0xC695FCF273B1F1D3" QueryPlanHash="0x91EB90978DE7E173">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan CachedPlanSize="64" CompileTime="82" CompileCPU="82" CompileMemory="2824">
<RelOp AvgRowSize="69" EstimateCPU="20272.4" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="18429500000" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="72100.4">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
<ColumnReference Column="Expr1015" />
</OutputList>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="SUM([ODS].[dbo].[tblGeneralLedger].[CRDTAMNT] as [l].[CRDTAMNT])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="CRDTAMNT" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="SUM([ODS].[dbo].[tblGeneralLedger].[DEBITAMT] as [l].[DEBITAMT])">
<Aggregate AggType="SUM" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="DEBITAMT" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1015" />
<ScalarOperator ScalarString="MAX([ODS].[dbo].[tblGeneralLedger].[RowIn] as [l].[RowIn])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowIn" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
</GroupBy>
<RelOp AvgRowSize="53" EstimateCPU="1247.09" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="18429500000" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="51828">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowIn" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="CRDTAMNT" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="DEBITAMT" />
</OutputList>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</OuterReferences>
<RelOp AvgRowSize="33" EstimateCPU="6351.35" EstimateIO="10639" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="128485000" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="17133.2">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</OutputList>
<MemoryFractions Input="0.99981" Output="1" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="33" EstimateCPU="20.3606" EstimateIO="0.259414" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="128485000" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="142.812">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</OutputList>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="ACCATNUM" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedgerAccounts].[Company] as [a].[Company]=[ODS].[dbo].[tblGeneralLedgerAccountCategories].[Company] as [c].[Company] AND [ODS].[dbo].[tblGeneralLedgerAccountCategories].[ACCATNUM] as [c].[ACCATNUM]=[ODS].[dbo].[tblGeneralLedgerAccounts].[ACCATNUM] as [a].[ACCATNUM]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="ACCATNUM" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="33" EstimateCPU="98.6496" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1575550" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="120.859">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="ACCATNUM" />
</OutputList>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedgerAccountCategories].[Company] as [c].[Company]=[ODS].[dbo].[tblGeneralLedger].[Company]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="28" EstimateCPU="0.0069429" EstimateIO="0.0483102" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="477.489" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0552531" TableCardinality="6169">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="ACCATNUM" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="ACCATNUM" />
</DefinedValue>
</DefinedValues>
<Object Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Index="[IX_tblGeneralLedgerAccountCategories]" Alias="[c]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedgerAccountCategories].[IsBalanceSheet] as [c].[IsBalanceSheet]=[@0] AND [ODS].[dbo].[tblGeneralLedgerAccountCategories].[IsIncluded] as [c].[IsIncluded]=[@1] AND [ODS].[dbo].[tblGeneralLedgerAccountCategories].[RowOut] as [c].[RowOut] IS NULL">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="IsBalanceSheet" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@0" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="IsIncluded" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@1" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="RowOut" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="23" EstimateCPU="0.00899688" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="476.489" EstimateRows="49426" LogicalOp="Lazy Spool" NodeId="6" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="15.0668">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</OutputList>
<Spool>
<RelOp AvgRowSize="23" EstimateCPU="0.579638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="49426" LogicalOp="Aggregate" NodeId="7" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="10.761">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</OutputList>
<StreamAggregate>
<DefinedValues />
<GroupBy>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</GroupBy>
<RelOp AvgRowSize="23" EstimateCPU="1.22099" EstimateIO="8.96034" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1109850" LogicalOp="Index Seek" NodeId="8" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="10.1813" TableCardinality="7773110">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="Company" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</DefinedValue>
</DefinedValues>
<Object Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Index="[ix_Perf1]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="RowOut" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="NULL">
<Const ConstValue="NULL" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="23" EstimateCPU="1.13015" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="17470" LogicalOp="Sort" NodeId="11" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.33242">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</OutputList>
<MemoryFractions Input="0.000189609" Output="0.000189609" />
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="23" EstimateCPU="0.019374" EstimateIO="0.171636" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="17470" LogicalOp="Index Seek" NodeId="12" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.19101" TableCardinality="29666">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="Company" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACCATNUM" />
</DefinedValue>
</DefinedValues>
<Object Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Index="[ix_Perf3]" Alias="[a]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="RowOut" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="NULL">
<Const ConstValue="NULL" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="37" EstimateCPU="1.11011" EstimateIO="56.8172" EstimateRebinds="128485000" EstimateRewinds="0" EstimateRows="2.32202" LogicalOp="Eager Spool" NodeId="14" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="33447.7">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowIn" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="CRDTAMNT" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="DEBITAMT" />
</OutputList>
<Spool>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedgerAccountCategories].[Company] as [c].[Company]">
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccountCategories]" Alias="[c]" Column="Company" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedgerAccounts].[ACTINDX] as [a].[ACTINDX]">
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedgerAccounts]" Alias="[a]" Column="ACTINDX" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<EndRange ScanType="LE">
<RangeColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="TRXDATE" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[ODS].[dbo].[tblGeneralLedger].[TRXDATE]">
<Identifier>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Column="TRXDATE" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
<RelOp AvgRowSize="53" EstimateCPU="1.22099" EstimateIO="13.4166" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1109850" LogicalOp="Index Seek" NodeId="15" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="14.6376" TableCardinality="7773110">
<OutputList>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowIn" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="Company" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="TRXDATE" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="CRDTAMNT" />
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="DEBITAMT" />
</OutputList>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowIn" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="Company" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="TRXDATE" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="ACTINDX" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="CRDTAMNT" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="DEBITAMT" />
</DefinedValue>
</DefinedValues>
<Object Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Index="[ix_Perf3]" Alias="[l]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[ODS]" Schema="[dbo]" Table="[tblGeneralLedger]" Alias="[l]" Column="RowOut" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="NULL">
<Const ConstValue="NULL" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="(1)" />
<ColumnReference Column="@0" ParameterCompiledValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
July 16, 2012 at 8:41 am
It looks like you're trying to do a running total. Is that correct?
If so, there are definitely more efficient ways to do one. I prefer using CLR for that. You can find a ton of articles on the subject by searching for "sql server running total clr" in either Google or Bing (I'm eccentric and use Bing).
What you've got here is what's called a "triangular join", and is the slowest way to get a running total on any substantial amount of data. Search for "triangular join" in your search-engine of choice, you'll find a bunch of good articles on why it's slow.
Another method, which I don't recommend but some people prefer, is to use a "quirky update" running total. Again, you can research these by searching "running total quirky update". Jeff Moden has a good article on this site about that subject. You can find it by going to the "Authors" list from the menu on the left of this site, and finding Jeff on that list. He's near the top. An older article on the subject appears on http://www.simple-talk.com, by "Phil Factor" and Robyn Page, under the heading "Working Tables", if I remember correctly. This one is also known as a "pseudo cursor", and you can find data by searching that too.
Yet another is to use a cursor to step through the data. It's faster than a triangular join, and more reliable than a quirky update, but it's got all the usual cursor problems.
CLR is the fastest, is fully reliable (if done right, which is easy), but does have the security consideration that you have to turn on CLR in your database, and some companies won't allow that. If you can't do the CLR version, or aren't comfortable with it, then you'll need to research which of the other methods (cursor or quirky) is best for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2012 at 8:47 am
essentially yes I am trying to run totals, and I only want to display unique data based on that select distinct join.
July 16, 2012 at 8:49 am
Makes sense.
Definitely dig into the various running total methods I mentioned. Any of them will speed this up considerably.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2012 at 9:12 am
thanks for pointing me in the right direction!
July 16, 2012 at 11:33 am
CELKO (7/16/2012)
Look at the [ROW | RANGE] option in the OVER() clause we now have in SQL Server 2012.
If this were the SQL 2012 forum, that would make sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2012 at 11:42 am
well I don't have CLR and would prefer to not do anything with cursors, so what I will do is load a history table with all data prior to this table and run a union on the two queries. I'm still puzzled how to fix the triangular join though.
July 16, 2012 at 12:01 pm
jfriedl (7/16/2012)
well I don't have CLR and would prefer to not do anything with cursors, so what I will do is load a history table with all data prior to this table and run a union on the two queries. I'm still puzzled how to fix the triangular join though.
You either have to use a procedural solution, or a triangular join. Procedural is cursor/pseudo-cursor/CLR. If you can't/won't use any of those, then a triangular join and slow code is all you have for options. Or upgrade to SQL 2012 and use the built-in procedural method, which is the Over() clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2012 at 10:57 am
jfriedl (7/16/2012)
well I don't have CLR and would prefer to not do anything with cursors, so what I will do is load a history table with all data prior to this table and run a union on the two queries. I'm still puzzled how to fix the triangular join though.
I encourage you to try a cursor solution to this. It can be WAY (as in orders of magnitude) more efficient than what you have for running totals. The "quirky update" mechanism is the fastest option by far given you aren't on SQL 2012, but it carries a lot of burden on you to do things EXACTLY RIGHT or you can/will get bad data out.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply