Spool treatment for DISTINCT versus GROUP BY

Database
Enthusiast

Spool treatment for DISTINCT versus GROUP BY

Surprisingly i came across an intresting observation and thoughts of sharing with forum members.

On running the following query , i got out of spool space error 2646:

SELECT DISTINCT OBJECTTABLENAME FROM HISTORY_DB.DBQLOBJTBL_HST

On changing it to GROUP BY :
SELECT OBJECTTABLENAME FROM HISTORY_DB.DBQLOBJTBL_HST GROUP BY 1

I was expecting the same error, but got the result indeed, with around 10k distinct values .Total count of the table is around 210 billion rows.

Any clues why spool behave differently for DISTINCT and GROUP BY ?

13 REPLIES
Junior Supporter

Re: Spool treatment for DISTINCT versus GROUP BY

DISTINCT and GROUP BY are different beasts (at least until v12).

You may find this interesting:

http://carlosal.wordpress.com/2009/06/02/distinct-vs-group-by-en-teradata/

HTH.

Cheers.

carlos.
WAQ
Enthusiast

Re: Spool treatment for DISTINCT versus GROUP BY

The contents of the link is not in English. Do you have an English version of it?
Junior Supporter

Re: Spool treatment for DISTINCT versus GROUP BY

Yup. It's in spanish.

http://translate.google.com is your friend...

Cheers.

Carlos.
Enthusiast

Re: Spool treatment for DISTINCT versus GROUP BY

Carlos, thanks for sharing this very good blog post.
I have one question: why do you say "at least until 12"? Have things changed in Teradata architecture to improve this performance difference?
Junior Supporter

Re: Spool treatment for DISTINCT versus GROUP BY

I seem to recall that they work the same since V12.

Cheers.

Carlos.
Enthusiast

Re: Spool treatment for DISTINCT versus GROUP BY

Carlos - Thanks for the information. But seems the distinction between DISTINCT and GROUP BY still exsit as i ran that query on TD 12.
Junior Contributor

Re: Spool treatment for DISTINCT versus GROUP BY

As i can't attach a PDF i try to copy some of it and hope i can make it readable:

Explain Distinct:
3) We do an all-AMPs RETRIEVE step from ... by way of an
all-rows scan with no residual conditions into Spool x
(group_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows.

First there's a redistribution, then duplicate rows are removed:
Efficient, if there are just a few rows per value [per AMP].
Spool size is always about the same, but may be extremely skewed → 2646: No more Spool Space

Explain Group By:
3) We do an all-AMPs SUM step to aggregate from ... by way
of an all-rows scan with no residual conditions, and the grouping
identifier in field 1025. Aggregate Intermediate Results are
computed globally, then placed in Spool x.

First each AMP removes duplicate rows locally (first aggregate) and hashes/redistributes the resulting
rows, then there's a second aggregation to remove duplicate rows:
Efficient, if there are lots of rows per value [per AMP].
Large number of rows per value Spool → small spool size
Small number of rows per value Spool → large spool size
Spool is never skewed.

Testing results for 80,000,000 rows, 80 AMPs system
(This data is approx. 5 years old, but it's still valid.)

- Runtime seconds CPU seconds
Distinct Values Ø Rows / AMP Distinct Group By Distinct Group By
- 79158864 0.01 38 83 781.33 1795.06
- 1000000 0.95 29 49 596.95 1080.50
- 100000 9.52 29 27 570.95 578.76
- 10000 95.24 28 7 559.74 151.17
- 1000 952.38 27 6 500.89 124.70
- 100 9523.81 27 5 431.62 119.04
- 10 95238.10 38 5 360.21 117.08
- 1 952380.95 221 5 386.77 115.39

Especially check the CPU time column, differences between worst and best:
DISTINCT 27 vs. 221 seconds -> 8x difference
GROUP BY: 5 vs. 83 seconds -> 16x difference

But a direct comparison will show GROUP BY is running faster in most cases.

And now look for the worst case.
Unique data: GROUP 83 seconds vs. DISTINCT 38

Only 1 value: GROUP 5 seconds vs. DISTINCT 221
Most of that long runtime is because *all* the data is processed by a single AMP and you might run out of spool because it's totally skewed.

So in worst case DISTINCT was 2.5x slower than GROUP BY, but GROUP BY was 44x faster than DISTINCT.

Efficiency increases for a Distinct using the Primary Index columns (Spool built locally on the AMPs),
because there's no redistribution, but in that case an aggregat (Intermediate Results are computed
locally) is always more efficient!

Within Teradata a subquery spool is automatically distinct (unless the optimizer knows it's unique, e.g.
because the query is accessing a unique index column), but can be overridden using Group By.

Rule of thumb:
- If the number of rows per value per AMP is close to 1 use DISTINCT else GROUP BY
- If the number of rows per value is unknown or skewed, better use Group By.

The most important point to consider is the number of rows per value per AMP:
For a value with 1000 occurrences on a 20 AMP system GROUP BY will be most efficient, but on a 2000 AMP system it will be DISTINCT.

In TD13 the optimizer automatically decides which version is more efficient, based on statistics.
Hint: as a side effect a lot of DISTINCTs are rewritten to GROUP BY and the result set is no longer ordered.
I know some people who prefer adding DISTINCT instead of ORDER BY, because it's easier to write :-)

Dieter
Enthusiast

Re: Spool treatment for DISTINCT versus GROUP BY

Thanks for the very detailed information Dieter. Can you guys shed some light on how you determine these in-depth version changes e.g. in this case, the TD13 optimizer automatically deciding on GROUP BY vs DISTINCT?

Much Thanks,
Monis
Teradata Employee

Re: Spool treatment for DISTINCT versus GROUP BY

Distinct and Group By diferences have not changed in a long time (at least up to 2.6.2). Basically, the more distinct the values are, the faster/better DISTINCT runs. When teh values are less 'unique' the GROUP BY is a better choice. If you just do an explain you can see the differences. WIth distinct, all the values in the 'list' are spooled out 'as is' and redistributed to other amps where they are then collapsed. WIthe Group by, it will extract the 'values' but collapse them BEFORE they are redistibuted. Here is a quick example:

EXPLAIN
SELECT
DISTINCT ProcID
FROM dbadmin.v_DBATraceLog;

Column1
1) First, we lock dbadmin.DBATraceLog for access.
2) Next, we do an all-AMPs RETRIEVE step from dbadmin.DBATraceLog by
way of an all-rows scan with no residual conditions into Spool 1
(group_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 1 by the sort key in spool field1
eliminating duplicate rows. The size of Spool 1 is estimated with
low confidence to be 4,592 rows. The estimated time for this step
is 0.03 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.

NOTE in step two the results are redistributed THEN duplicate rows are eliminated.

SELECT
ProcID
FROM dbadmin.v_DBATraceLog
GROUP BY 1
;

Column1
1) First, we lock dbadmin.DBATraceLog for access.
2) Next, we do an all-AMPs SUM step to aggregate from
dbadmin.DBATraceLog by way of an all-rows scan with no residual
conditions, and the grouping identifier in field 1025. Aggregate
Intermediate Results are computed globally, then placed in Spool 3.
The size of Spool 3 is estimated with no confidence to be 67 rows.
The estimated time for this step is 0.02 seconds.
3) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 67 rows. The estimated time for this step is 0.01 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.

Notice (now) there is a SUM aggregate step that takes the values and aggregates them before they are placed in the spool, etc.

I've noticed that when the values are quite unique, DISTINCDT can be a lot faster - but like the old 'luddite' that I am, I usually default to using the GROUP BY;-(