how can i optimize a group by ?

how can i optimize a group by ?

Post by Juan Ignacio D'Amat » Fri, 04 Jul 1997 04:00:00

I need to group a very big table (several million records) and sql server
doesn't use any index.

Can a group by benefit from an index ? or the only thing i can do is reduce
the number of records before the group by ?

Thanks in advance


1. Optimize a query : Replacing the Group By Clause ?

Hi Everyone.
I have a (newbie ?) question about this example query :

Two tables :
PEOPLE (FamilyName, BirthDate)
Name1 | 01/01/2000
Name2 | 02/02/2000

ITEM (FamilyName, ItemNumber, Option, CreationDate)
Name1 | #1 | <empty>| 10/10/1999
Name2 | #1 | <empty> | 11/11/1999
Name2 | #2 | Option_for_ Name2 | 12/12/1999

This means : Name2 has an Option, and Name1 don't.
The option can be on #1  OR #2 item number => this means I cannot use
ItemNumber to select the line from ITEM

This is the result I want :
Name1 | 01/01/2000 | <Empty> | 10/10/1999
Name2 | 02/02/2000 | Option for Name2 | 12/12/1999

This is the Query I am using for now :

SELECT People.FamilyName, BirthDate, MAX(Option), MAX(CreationDate)
WHERE People.FamilyName = Item.FamilyName
GROUP BY People.FamilyName, BirthDate

It's working, but is very slow because of the "group by" clause. In
addition, this is not very "clean" especially for the
Is there another way to get the same result ?
Thanks for reading this long post !...


2. fm 3 with fm 4?

3. Optimizing a GROUP BY Query

4. Reverse Engineering in Designer6.0

5. MDX : Canned Report or OLAP

6. Paradox Help!!!!

7. Anyone know of some canned (cheap or free) DB performance testing software

8. 同感

9. canned code to get db on web quickly via perl or

10. Cans access2.0 engine access btrieve files?

11. bcp canned app

12. if you will promise Allahdad's swamp against cans, it will angrily depart the unit

13. Switching from inhouse to canned package.