473,320 Members | 1,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

tuning the sort step of execution plan

hi
i got a query that takes about 14 mins
here it is
select BDProduct.ProductCode,BDProduct.ProductName,SALTer ritory.TerritoryID
,SALTerritory.TerritoryName,SALAccount.AccountID,S ALAccount.AccountName
,sum(SalesNetFact.Qty2) as Quantity
,sum(SalesNetFact.bonus) as Bonus
from SalesNetFact
inner join BDProduct
on BDProduct.ProductID=SalesNetFact.ProductID
inner join SALAccount
on SALAccount.AccountID=SalesNetFact.AccountID
and SALAccount.BranchID=SalesNetFact.branchid
inner join SALTerritory
on dbo.SALAccount.TerritoryID = dbo.SALTerritory.TerritoryID
and dbo.SALAccount.BranchID = dbo.SALTerritory.BranchID
group by BDProduct.ProductCode,BDProduct.ProductName
,SALTerritory.TerritoryID,SALTerritory.TerritoryNa me,SALAccount.AccountID
,SALAccount.AccountName
the SalesNetFact table has BranchID,TransactionLineID as primary key
the BDProduct table has ProductID as primary key
the SALAccount table has AccountID,BranchID as primary key
the SALTerritory table has TerritoryID,BranchID 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
Jul 20 '05 #1
1 8457
[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.TerritoryName,
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.TerritoryName, 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.TerritoryName,
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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: hrishy | last post by:
Hi All I have the following query to be tuned.. Select distinct PA.PersonAddress_IDX, AT.Name AddressType, A.Line1 Address1, A.Line2 Address2, A.City, A.State, A.County, A.Country,...
1
by: Daniel Roy | last post by:
Hi gurus, I just started to look at a very slow-running SQL statement generated by an application (Siebel). I spooled the SQL from the application, replaced the bind variables by their values, and...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
2
by: Ina Schmitz | last post by:
Hi NG, does IBM Universal Database 8.2 make any difference between actual and estimated execution plans like in SQL Server ("set showplan_all on" for estimated execution plan and "set statistics...
3
by: mleal | last post by:
Does anyone have some more detailed information about how Oracle and MS implement / allow Tuning on Oracle 10g and SQL Server 2005 and the differences between them? Which of them, In a deep...
0
by: Medhatithi | last post by:
Hi, I have been in several ways benefiited from this site. I would like to share some sql tuning techniques(simple, but effective) with you all. SQL Tuning Tips Oracle Tips Session #6 ...
1
by: codefragment | last post by:
Hi I've heard 2 things recently, can I confirm if their true/false? (1) If you have a stored procedure and you want to optimise it you can call exec proc1, you could also use define/set for...
1
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of...
1
by: deepu03 | last post by:
Hi All I have the following query to be tuned.. SELECT distinct a.EMPLID ,a.PER_ORG ,a.HIRE_DT ,a.grade
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.