[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

GROUP BY performance issue

Jack Vamvas

3/22/2007 5:25:00 PM

I've got a table called Lists , which has a record of each list item and
each publication.
As you can see there are 2 left joins ,in case there are records in either
of these which are required in the search results to populate fields

The results are grouped by all fields selected so that a distinct list is
produced. Is this a performance issue, is there a better way of doing this
search?
Would denormalising the data help?



Select a.sellref,a.selltext ,a.publication FROM Lists as A
LEFT JOIN Dealers as d ON a.dealerCode = d.dealercode
LEFT JOIN SellerFlags as af ON af.sellref = a.sellref
WHERE a.publication='3B'
GROUP BY a.sellref,a.selltext,a.publication

Tom
6 Answers

TenKMan

3/22/2007 6:29:00 PM

0

"Tom" <DEL_TO_REPLY@del.com> wrote in message
news:W6OdnV8529JzJp_bnZ2dnUVZ8vidnZ2d@bt.com...
> I've got a table called Lists , which has a record of each list item and
> each publication.
> As you can see there are 2 left joins ,in case there are records in
> either of these which are required in the search results to populate
> fields
>
> The results are grouped by all fields selected so that a distinct list is
> produced. Is this a performance issue, is there a better way of doing this
> search?
> Would denormalising the data help?
>
>
>
> Select a.sellref,a.selltext ,a.publication FROM Lists as A
> LEFT JOIN Dealers as d ON a.dealerCode = d.dealercode
> LEFT JOIN SellerFlags as af ON af.sellref = a.sellref
> WHERE a.publication='3B'
> GROUP BY a.sellref,a.selltext,a.publication

Can you explain how your query returns a resultset that differs from:

Select distinct A.sellref, A.selltext , A.publication FROM Lists as A

Your preserved table is Lists and, AFAIK, you use no information from the
unpreserved tables that would alter the resultset.


Aaron [SQL Server MVP]

3/22/2007 6:34:00 PM

0

>> Select a.sellref,a.selltext ,a.publication FROM Lists as A
>> LEFT JOIN Dealers as d ON a.dealerCode = d.dealercode
>> LEFT JOIN SellerFlags as af ON af.sellref = a.sellref
>> WHERE a.publication='3B'
>> GROUP BY a.sellref,a.selltext,a.publication
>
> Can you explain how your query returns a resultset that differs from:
>
> Select distinct A.sellref, A.selltext , A.publication FROM Lists as A
>
> Your preserved table is Lists and, AFAIK, you use no information from the
> unpreserved tables that would alter the resultset.

True, but he'd still need the where clause to get the same resultset. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

TenKMan

3/22/2007 7:02:00 PM

0

> True, but he'd still need the where clause to get the same resultset. :-)

And we might as well order the resultset while we're at it - since we don't
want to assume that it is ordered just because it appears to be!


Tom Cooper

3/22/2007 7:04:00 PM

0

And the query processor is smart enough to know they must return the same
result set. For example, in the pubs database. the following two queries
will generate the same execution plan:

Select Distinct a.au_lname, a.phone
From authors a
Where au_fname like 'a%'

Select a.au_lname, a.phone
From authors a
Left Join titleauthor ta On a.au_id = ta.au_id
Left Join titles t On ta.title_id = t.title_id
Where au_fname like 'a%'
Group By a.au_lname, a.phone

Tom

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uZ0HVCLbHHA.4632@TK2MSFTNGP03.phx.gbl...
>>> Select a.sellref,a.selltext ,a.publication FROM Lists as A
>>> LEFT JOIN Dealers as d ON a.dealerCode = d.dealercode
>>> LEFT JOIN SellerFlags as af ON af.sellref = a.sellref
>>> WHERE a.publication='3B'
>>> GROUP BY a.sellref,a.selltext,a.publication
>>
>> Can you explain how your query returns a resultset that differs from:
>>
>> Select distinct A.sellref, A.selltext , A.publication FROM Lists as A
>>
>> Your preserved table is Lists and, AFAIK, you use no information from the
>> unpreserved tables that would alter the resultset.
>
> True, but he'd still need the where clause to get the same resultset. :-)
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sq...
> http://www.aspfa...
>
>


Jack Vamvas

3/22/2007 7:29:00 PM

0

Thanks. Physically what is the difference. When you use SELECT DISTINCT ,
isn't that DISTINCT just on that first column?
If you do a GROUP BY col1,col2 aren't you saying -- give every unique
combination between those 2 columns?




"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:uscHaTLbHHA.2188@TK2MSFTNGP04.phx.gbl...
> And the query processor is smart enough to know they must return the same
> result set. For example, in the pubs database. the following two queries
> will generate the same execution plan:
>
> Select Distinct a.au_lname, a.phone
> From authors a
> Where au_fname like 'a%'
>
> Select a.au_lname, a.phone
> From authors a
> Left Join titleauthor ta On a.au_id = ta.au_id
> Left Join titles t On ta.title_id = t.title_id
> Where au_fname like 'a%'
> Group By a.au_lname, a.phone
>
> Tom
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:uZ0HVCLbHHA.4632@TK2MSFTNGP03.phx.gbl...
>>>> Select a.sellref,a.selltext ,a.publication FROM Lists as A
>>>> LEFT JOIN Dealers as d ON a.dealerCode = d.dealercode
>>>> LEFT JOIN SellerFlags as af ON af.sellref = a.sellref
>>>> WHERE a.publication='3B'
>>>> GROUP BY a.sellref,a.selltext,a.publication
>>>
>>> Can you explain how your query returns a resultset that differs from:
>>>
>>> Select distinct A.sellref, A.selltext , A.publication FROM Lists as A
>>>
>>> Your preserved table is Lists and, AFAIK, you use no information from
>>> the unpreserved tables that would alter the resultset.
>>
>> True, but he'd still need the where clause to get the same resultset.
>> :-)
>>
>> --
>> Aaron Bertrand
>> SQL Server MVP
>> http://www.sq...
>> http://www.aspfa...
>>
>>
>
>


Aaron [SQL Server MVP]

3/22/2007 7:38:00 PM

0

> When you use SELECT DISTINCT , isn't that DISTINCT just on that first
> column?

No, DISTINCT applies to the entire column list. Have you tried saying
SELECT DISTINCT col1, DISTINCT col2, col3?

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...