July 22, 2009 at 7:08 pm
TOP 1000 (select count(*) from LessOrder where cancelnumber = cr.cancelnumber)
deselect,
cr.cancelnumber,
cr.SellerAcctNbr,
cp.FirstName,
cp.LastName,
cr.canceltatus,
cr.memo,
cr.PaymtStatus,
cr.canceltime AS OrderDate,
cr.latestcanceltime,
cr.cancelamount AS Amount,
pm.pricing AS CardType,
cp.mail AS EMail,
cr.roadmap,
cg.roadmapdesc,
cr.couponcd
FROM
cancel cr, Recipient cp, Payment cm,
cancelmethod pm,cancelprogram cg
WHERE
cr.cancelnumber = cp.cancelnumber AND
cp.SellerAcctNbr = cr.SellerAcctNbr AND
cm.PurchaserID = cr.PurchaserID AND
cm.SellerAcctNbr = cr.SellerAcctNbr AND
cm.PaymtMethodCd = pm.PaymtMethodCd AND
cg.roadmap = cr.roadmap AND
cp.FirstName = 'm' AND
cp.LastName = 'kat'
ORDER BY cr.canceltime Desc
This query is taking longer than expected. Just checked the query plan, there is a clustered index scan on lessorder column 'cancelnumber' taking 73% of cost. Are there any inputs?
M&M
July 22, 2009 at 7:52 pm
i rewrote your query to use the standard INNER JOIN syntax, but I'm confused a bit about the subquery to get the count from LessOrder
take a look at this and see if it was what you were trying to do.
SELECT
TOP 1000
MYALIAS.THECOUNT as deselect,
cr.cancelnumber,
cr.SellerAcctNbr,
cp.FirstName,
cp.LastName,
cr.canceltatus,
cr.memo,
cr.PaymtStatus,
cr.canceltime AS OrderDate,
cr.latestcanceltime,
cr.cancelamount AS Amount,
pm.pricing AS CardType,
cp.mail AS EMail,
cr.roadmap,
cg.roadmapdesc,
cr.couponcd
FROM
cancel cr
INNER JOIN Recipient cp ON cr.cancelnumber = cp.cancelnumber
AND cr.SellerAcctNbr = cp.SellerAcctNbr
INNER JOIN Payment cm ON cr.PurchaserID = cm.PurchaserID
AND cr.SellerAcctNbr = cm.SellerAcctNbr
INNER JOIN cancelmethod pm ON cm.PaymtMethodCd = pm.PaymtMethodCd
INNER JOIN cancelprogram cg ON cr.roadmap = cg.roadmap
INNER JOIN (select cancelnumber,count(*) AS THECOUNT from LessOrder GROUP BY cancelnumber) MYALIAS
ON cr.cancelnumber = MYALIAS.cancelnumber
WHERE cp.FirstName = 'm'
AND cp.LastName = 'kat'
ORDER BY cr.canceltime Desc
Lowell
July 23, 2009 at 7:15 am
Could you post the actual execution plan?
I suspect you're missing an index,but it's also possible that, without any thing to filter that table, you're getting a scan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2009 at 7:23 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2009 at 2:01 am
First lets locate possible bottlenecks (not neccessarily to do it in this order, but it’s neccessary to review it):
1)Are there unnecessary ugly joins that can be avoided by breaking huge join into smaller one
2)Are there appropriate indexes deployed (and check whether they are not fragmented)
3)See for TOP 1000 clause conected with ORDER BY clause, whether it can be replaced with something cheaper
4)See for nested joins, especially grouping, counting and „supporting“
5)See for lookup queries to avoid, if possible
If required indexes exists and fragmentation is okay, one idea is to isolate „annoying counting“ – do not count rows before you isolate records. Try this:
select
(select count(*) from LessOrder where cancelnumber = IsolatedRecords.cancelnumber) as deselect,
cancelnumber, SellerAcctNbr, FirstName, LastName, canceltatus, memo, PaymtStatus, OrderDate, latestcanceltime, Amount, CardType, EMail, roadmap, roadmapdesc, couponcd
FROM (
select TOP 1000
/* avoid COUNTING until you isolate needed records
--(select count(*) from LessOrder where cancelnumber = cr.cancelnumber) as deselect,
*/
cr.cancelnumber,
cr.SellerAcctNbr,
cp.FirstName,
cp.LastName,
cr.canceltatus,
cr.memo,
cr.PaymtStatus,
cr.canceltime AS OrderDate,
cr.latestcanceltime,
cr.cancelamount AS Amount,
pm.pricing AS CardType,
cp.mail AS EMail,
cr.roadmap,
cg.roadmapdesc,
cr.couponcd
FROM
cancel cr, Recipient cp, Payment cm,
cancelmethod pm, cancelprogram cg
WHERE
cr.cancelnumber = cp.cancelnumber AND
cp.SellerAcctNbr = cr.SellerAcctNbr AND
cm.PurchaserID = cr.PurchaserID AND
cm.SellerAcctNbr = cr.SellerAcctNbr AND
cm.PaymtMethodCd = pm.PaymtMethodCd AND
cg.roadmap = cr.roadmap AND
cp.FirstName = 'm' AND
cp.LastName = 'kat'
ORDER BY cr.canceltime Desc
) as IsolatedRecords
ORDER BY canceltime Desc
I know that it looks more complicated but I simulate it (on few million records in each table and proper indexes) and it showed improvements
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply