Group By\Distinct Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group By\Distinct Performance

Author

Topic

budski
Starting Member

7 Posts

Posted - 2008-09-15 : 11:54:46

I have some issues with the performance of a sql statement.
The statement below runs in about two seconds however I must include a column a don't need


    SELECT DISTINCT L.LOOKUP, L.LOOKUPIDX, U.UNMATCHEDIDX
    FROM LOOKUPS L
    INNER JOIN UNMATCHED U ON L.LOOKUPIDX=U.LOOKUPIDX
    INNER JOIN UNMATCHEDITEM UITM ON U.UNMATCHEDIDX=UITM.UNMATCHEDIDX
    INNER JOIN UNMATCHEDITEMS UITMS ON          UITM.UNMATCHEDITEMIDX=UITMS.UNMATCHEDITEMIDX

I don't really need unmatchedidx. What I really need is a distinct list of lookup and lookupidx. However if i exclude unmatchedidx from the distinct it takes 46 seconds


    SELECT DISTINCT L.LOOKUP, L.LOOKUPIDX--, U.UNMATCHEDIDX
    FROM LOOKUPS L
    INNER JOIN UNMATCHED U ON L.LOOKUPIDX=U.LOOKUPIDX
    INNER JOIN UNMATCHEDITEM UITM ON U.UNMATCHEDIDX=UITM.UNMATCHEDIDX
    INNER JOIN UNMATCHEDITEMS UITMS ON          UITM.UNMATCHEDITEMIDX=UITMS.UNMATCHEDITEMIDX

In order to get what I need with the performance I need the best I can come up with is below


  SELECT LKU.LOOKUP, LKU.LOOKUPIDX 
  FROM 
  LOOKUPS LKU
  INNER JOIN		
       	(
	   SELECT DISTINCT L.LOOKUP, L.LOOKUPIDX, U.UNMATCHEDIDX
       	   FROM LOOKUPS L
	   INNER JOIN UNMATCHED U ON L.LOOKUPIDX=U.LOOKUPIDX
	   INNER JOIN UNMATCHEDITEM UITM ON U.UNMATCHEDIDX=UITM.UNMATCHEDIDX
		   INNER JOIN UNMATCHEDITEMS UITMS ON UITM.UNMATCHEDITEMIDX=UITMS.UNMATCHEDITEMIDX
        	) AS A 
        ON LKU.LOOKUPIDX = A.LOOKUPIDX

The above works but it seem way more obtuse than it needs to be. I can imagine someone looking at this a year from now and being totally perplexed as to why something so simple is written in such a way

General info
Lookup - (PK: LookupIDX) (Count: 19)
Unmatched - (PK: UnmatchedIDX) (Count: 7)
UnmatchedItem - (PK: UnmatchedItemIDX) (Count: 15,277)
UnmatchedItems - (PK: UnmtachedItemsIDX) (Count: 257,455)

Any suggestions will be appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 12:06:35

[code]SELECT DISTINCT l.Lookup,
l.LookupIdx
FROM (
SELECT [Lookup],
LookupIdx
FROM Lookups
GROUP BY [Lookup],
LookupIdx
) AS l
INNER JOIN (
SELECT LookupIdx,
UnmatchedIdx
FROM Unmatched
GROUP BY LookupIdx,
UnmatchedIdx
) AS u ON u.LookupIdx = l.LookupIdx
INNER JOIN (
SELECT UnmatchedIdx,
UnmatchedItemIdx
FROM UnmatchedItem
GROUP BY UnmatchedIdx,
UnmatchedItemIdx
) AS UItm ON UItm.UnmatchedIdx = u.UnmatchedIdx
WHERE EXISTS (SELECT * FROM UnmatchedItems AS UItms WHERE UItms.UnmatchedItemIdx = UItm.UnmatchedItemIdx[/code]

E 12°55'05.63"
N 56°04'39.26"

budski
Starting Member

7 Posts

Posted - 2008-09-15 : 12:25:04

Thanks for the quick reply. That got it to less than a second.

Still not exactly sure what's going on because max and min work like a champ which to me would indicate the sort is working ok. Oh well I'll figure that out another day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources