tuning the sort step of execution plan

[web developer]

hi
i got a query that takes about 14 mins
here it is
select BDProduct.Produ ctCode,BDProduc t.ProductName,S ALTerritory.Ter ritoryID
,SALTerritory.T erritoryName,SA LAccount.Accoun tID,SALAccount. AccountName
,sum(SalesNetFa ct.Qty2) as Quantity
,sum(SalesNetFa ct.bonus) as Bonus
from SalesNetFact
inner join BDProduct
on BDProduct.Produ ctID=SalesNetFa ct.ProductID
inner join SALAccount
on SALAccount.Acco untID=SalesNetF act.AccountID
and SALAccount.Bran chID=SalesNetFa ct.branchid
inner join SALTerritory
on dbo.SALAccount. TerritoryID = dbo.SALTerritor y.TerritoryID
and dbo.SALAccount. BranchID = dbo.SALTerritor y.BranchID
group by BDProduct.Produ ctCode,BDProduc t.ProductName
,SALTerritory.T erritoryID,SALT erritory.Territ oryName,SALAcco unt.AccountID
,SALAccount.Acc ountName
the SalesNetFact table has BranchID,Transa ctionLineID as primary key
the BDProduct table has ProductID as primary key
the SALAccount table has AccountID,Branc hID as primary key
the SALTerritory table has TerritoryID,Bra nchID as primary key
i have no other indices in any of these tables
the execution plan shows that the sort step takes 96% cost,that is the
most expensive step,it is done after all the joining steps and before
the group by step
for the sort step:the estimated row count is 1552242,the arguments
are:ORDER BY [BDProduct].[ProductCode]
asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName]
asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)
any ideas about how to improve this sort step

[Erland Sommarskog]

[posted and mailed, please reply in news]

web developer (o_*****@yahoo. com) writes:

i got a query that takes about 14 mins
here it is
...
the execution plan shows that the sort step takes 96% cost,that is the
most expensive step,it is done after all the joining steps and before
the group by step
for the sort step:the estimated row count is 1552242,the arguments
are:ORDER BY [BDProduct].[ProductCode]
asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName]
asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)
any ideas about how to improve this sort step

First of all, I would point out information about the estimates of the
various steps are just that: estimates. They may be right, but personally,
I pay little attention to them.

Next, I like to point out that it may be difficult enough even you know
the schema and indexes and the data distribution. In this case, I only
know the primary keys, but I don't know they relate.

Nevertheless, here is a suggestion that *might* perform better in the case
the ORDER BY for the grouping indeed is the problem. But since know how
data maps, I don't know whether this query gives the same result. So please
accept my suggestion as a shot in the dark.

As reference: here is you original query reformated according to my
preferences:

SELECT bd.ProductCode, bd.ProductName, st.TerritoryID, st.TerritoryNam e,
sa.AccountID, sa.AccountName,
sum(snf.Qty2) as Quantity, sum(snf.bonus) as Bonus
FROM SalesNetFact snf
JOIN BDProduct bd ON bd.ProductID = snf.ProductID
JOIN SALAccount sa ON sa.AccountID = snf.AccountID
AND sa.BranchID = snf.branchid
JOIN SALTerritory ON sa.TerritoryID = st.TerritoryID
AND sa.BranchID = st.BranchID
GROUP BY bd.ProductCode, bd.ProductName, st.TerritoryID,
st.TerritoryNam e, sa.AccountID, sa.AccountName

By the use of aliases, there is a considerable noice reduction in my
opinion. And here is a rewrite:

SELECT bd.ProductCode, bd.ProductName, st.TerritoryID, st.TerritoryNam e,
sa.AccountID, sa.AccountName, snf.Quantity, snf.Bonus
FROM (SELECT ProductId, AccountID, BranchID,
SUM(snf.Qty2) AS Quantity, SUM(snf.bonus) AS Bonus
FROM SalesNetFact
GROUP BY ProductId, AccountID, BranchID) AS snf
JOIN BDProduct bd ON bd.ProductID = snf.ProductID
JOIN SALAccount sa ON sa.AccountID = snf.AccountID
AND sa.BranchID = snf.BranchID
JOIN SALTerritory ON sa.TerritoryID = st.TerritoryID
AND sa.BranchID = st.BranchID

What I've done is that I have used derived table, which is a sort of
temp table in the middle of the query. Logically, that is. It is never
materialized, and SQL Server may change the computation order, as long
as the result is the same. The idea is that the grouping is now performed
only on the SalesNetFact table, and first after this the other tables
are added. But if different snf.ProductId:s can map to the same tuple
of (bd.ProductCode , bd.ProductName) , you will not get the same result
as the first query.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp