how to optimize query to avoid 'no spool space error'

Database
Enthusiast

how to optimize query to avoid 'no spool space error'

Hi ,

 

I know the error 'no spool space' is due to inefficient query, however I have no idea how to further improve my query, any help or suggestion is appreciated.

 

CREATE MULTISET TABLE db_name.SRP_VI AS (
SEL EP.QT,
EP.CHNL_ID,
GUID,
E.SESSION_START_DT,
E.SESSION_SKEY,
E.SEQ,
E.PAGE_ID,
CAST(LOWER(TRIM(E.SQR)) AS VARCHAR(100)) AS KEYWORD,
CAST(CASE WHEN E.PAGE_ID IN (2045573, 3286, 2047936) THEN 'SRP'
WHEN E.PAGE_ID IN (1881, 4340, 4079) THEN 'VI' END
AS VARCHAR(10)) PAGE_TYPE,
CASE WHEN PAGE_TYPE = 'SRP' THEN LENGTH(KEYWORD)
WHEN PAGE_TYPE = 'VI' THEN -1 END QUERY_LENGTH,
CASE WHEN PAGE_TYPE = 'SRP' AND (SOJLIB.SOJ_EXTRACT_FLAG(E.FLAGS, 070) = 1 OR SOJLIB.SOJ_EXTRACT_FLAG(E.FLAGS, 236) = 1 OR SOJLIB.SOJ_EXTRACT_FLAG(E.FLAGS, 237) = 1) THEN 'BEST MATCH'
WHEN PAGE_TYPE = 'VI' THEN 'NOT_APP' END SORT_TYPE,
CASE WHEN PAGE_TYPE = 'SRP' AND (SOJLIB.SOJ_EXTRACT_FLAG(E.FLAGS,52) = 1 OR CAST(SOJLIB.SOJ_NVL(E.SOJ, 'cpnip') AS INTEGER) = 1) THEN 1
WHEN PAGE_TYPE = 'VI' THEN 0 END PAGINITION,
CASE WHEN PAGE_TYPE = 'SRP' AND SOJLIB.SOJ_NVL(E.SOJ, 'gf') IS NULL AND SOJLIB.SOJ_NVL(E.SOJ, 'aa') IS NULL THEN 'NO_FILTER'
WHEN PAGE_TYPE = 'VI' THEN 'NOT_APP' END CONST_APPLIED,
CASE WHEN PAGE_TYPE = 'SRP' THEN CAST(SOJLIB.SOJ_DECODE_BASE36_VEC(SOJLIB.SOJ_NVL(E.SOJ, 'itm')) AS VARCHAR(1000)) END ITEM_LIST,
CAST(CHAR_LENGTH(ITEM_LIST) - CHAR_LENGTH(REGEXP_REPLACE(ITEM_LIST, '[,]', '', 1, 0, 'i')) + 1 AS BYTEINT) N_ITEM,
CAST(E.ITEM_ID AS DECIMAL(18, 0)) ITEM_ID,
ROW_NUMBER() OVER (ORDER BY QT, TRMT_VRSN_ID, E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, E.EVENT_TIMESTAMP, E.SEQ) AS RNUM
FROM  db_name.E E
INNER JOIN  db_name.S S
ON E.GUID = S.GUID
AND E.SESSION_SKEY = S.SESSION_SKEY
AND E.SESSION_START_DT = S.SESSION_START_DT
AND E.SITE_ID = S.SITE_ID
INNER JOIN  db_name.EP EP
ON EP.SESSION_START_DT = E.SESSION_START_DT
AND EP.GUID = E.GUID
AND EP.SESSION_SKEY = E.SESSION_SKEY
AND EP.SITE_ID = E.SITE_ID
WHERE E.SESSION_START_DT BETWEEN '2016-02-08' AND CAST('2016-02-08' + 7 AS DATE)
AND (E.PAGE_ID IN (2045573, 3286, 2047936, 1881, 4340, 4079)
AND E.SITE_ID = 0
AND S.VALID_PAGE_COUNT > 1 -- EXCLUDE SINGLE PAGE SESSIONS
AND EP.CHNL_ID IN (1)
AND EP.QT IN (35452,35464)
AND EP.TRMT_VRSN_ID = 3
AND (QUERY_LENGTH BETWEEN 2 AND 100 OR QUERY_LENGTH = -1)
AND SORT_TYPE IN ('BEST MATCH', 'NOT_APP')
AND PAGINITION IN (1, 0)
AND CONST_APPLIED IN ('NO_FILTER', 'NOT_APP')
) WITH DATA PRIMARY INDEX (QT, GUID, SESSION_SKEY, SESSION_START_DT);


The part I highlighted seems complex, actually I intented to put these contraints including 'case when' after where clause, but I don't know how. And I'm not sure whether this part is the cause for the inefficiency.


Thanks!


Accepted Solutions
Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

The biggest problem you have here is the ordered analytical function:

ROW_NUMBER() OVER (ORDER BY QT, TRMT_VRSN_ID, E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, E.EVENT_TIMESTAMP, E.SEQ) AS RNUM

 

This requires that all the rows in the spool be sorted and sent to a single AMP in the STAT step to assign row numbers.  This is not only slow, it means that the entire data set has to fit in the spool space of a single AMP.  That is most likely the step where you get the out-of-spool message.

If this table really requires a row number, then you might be able to calculate it separately.  Sometimes this can be done in a sub-select that selects only the columns required to compute row number and the row number itself - thus you have less data and it might fit in one AMP's spool (and it's faster!).  The sub-select would contain all the where-conditions, and then you join the rest of the data to the sub-select.  In this case though, it looks like the only space you might be saving is from KEYWORD (varchar(100)), so it might not be enough.

 

Try just minimizing the query so that it only has the data required to compute row number, and see if that works.  If not, you may have to increase the spool space.

1 ACCEPTED SOLUTION
13 REPLIES
Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

The biggest problem you have here is the ordered analytical function:

ROW_NUMBER() OVER (ORDER BY QT, TRMT_VRSN_ID, E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, E.EVENT_TIMESTAMP, E.SEQ) AS RNUM

 

This requires that all the rows in the spool be sorted and sent to a single AMP in the STAT step to assign row numbers.  This is not only slow, it means that the entire data set has to fit in the spool space of a single AMP.  That is most likely the step where you get the out-of-spool message.

If this table really requires a row number, then you might be able to calculate it separately.  Sometimes this can be done in a sub-select that selects only the columns required to compute row number and the row number itself - thus you have less data and it might fit in one AMP's spool (and it's faster!).  The sub-select would contain all the where-conditions, and then you join the rest of the data to the sub-select.  In this case though, it looks like the only space you might be saving is from KEYWORD (varchar(100)), so it might not be enough.

 

Try just minimizing the query so that it only has the data required to compute row number, and see if that works.  If not, you may have to increase the spool space.

Enthusiast

Re: how to optimize query to avoid 'no spool space error'

@GJColeman Thanks a lot for your detailed explanation! That helps a lot.

I didn't know that the row_number() function is processed in only one single AMP before. Are all the analytical functions executed in only one AMP? If so does it mean that wherever we use ordered analytical function we have to be aware of the 'out of spool space problem' and try to aplly the function on the subset of raw data?

Enthusiast

Re: how to optimize query to avoid 'no spool space error'

@GJColeman Hi, I have a follow up question, you said the ordered analytical function like row_number will process data row by row in only one AMP,  it reminds me of function 'distinct'. If I add 'distinct' before the columns I want to select, will it also process the rows one by one in one AMP after applying all the constraints in where clause?

 

Say I want to join to data set and remove duplicates

code version 1 is:

sel A.col1, A.col2, A.col3, B.col4

from tableA A

inner join table B B

on A.col1 = B.col1

and B.col2 = B.col2

group by 1, 2, 3, 4;

 

code version 2 is:

sel distinct 

A.col1, A.col2, A.col3, B.col4

from tableA A

inner join table B B

on A.col1 = B.col1

and B.col2 = B.col2

 

Which version is faster and why?

 

Appreciate it a lot for your help!

Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

Simple answer in no. DISTINCT utilizes all AMPs and is only as skewed as the source data.

 

For the long answer see: https://community.teradata.com/t5/Database/By-any-chance-is-there-any-possibility-of-getting-into-sp...

 

Check the explains to see which plan is chosen for each of your queries.

Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

No, this does not apply to all ordered analytical functions!  Just the ones that require sequential processing, like row_number() and rank().  And if those sequential functions use the partition-by clause, then the partitions are distributed across the AMPs and the problem is alleviated.  Also, if you use a rows-between phrase this is unlikely to be a problem.

Furthermore, this may all depend on the release of Teradata you are running.  More recent releases may have a more sophisticated algorithm for calculating row number that uses all the AMPs.  There are two ways to see how your query will execute: first study the EXPLAIN plan to look for oddities, then turn on DB Query Logging for your session, and after it ends look at the DBC.QryLog* views to see elapsed time, CPU and I/O for each step by AMP.  If row_number() is the problem in your query, you will see it in the STAT step in DBQL.

Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

Modern releases of the optimizer are pretty good at deciding how to process distinct and group-by.  In olden days group-by usually worked faster than distinct, but things have changed.  As Todd says, check the Explains.

Enthusiast

Re: how to optimize query to avoid 'no spool space error'

Thanks @GJColeman and @ToddAWalter for your perfect answer, that solves my puzzle!

Highlighted
Teradata Employee

Re: how to optimize query to avoid 'no spool space error'

I would like to add some clarification/detail to @GJColeman answers re the ordered analytic functions.

 

It is possible for an ordered analytic to be skewed including to be skewed to a single AMP. This will generally only happen if the data in the combined set of columns in the ORDER BY is skewed. If there are only a few values to order by or worst case if the order by is unique then the work would end up on a single AMP. However with the list of order by columns in this query, it seems unlikely that the data is badly skewed.

 

For the typical case of the OA functions like ROW_NUMBER or RANK, the data is redistributed by value to all AMPs. This is a different process than the typical redistribution by hash used for joins, aggregation, et al. This kind of redistribution evaluates the range of values in the order by set, creates a set of value range buckets with the same number of buckets as there are AMPs, then redistributes the rows to the right buckets. Then the data is sorted locally on each AMP. Then rownumbers are assigned locally on the AMP. Then the max rownumbers from each AMP is shared to the rest of the AMPs so they can have a base for the rownumbers on that AMP. Finally the data is written to a spool file with the actual rownumbers assigned.

 

If the source data is large, then this spool file will also be large. And for the period of time when the redistribution and sorting is being done, the spool required will be larger than the source data.

 

When trying to assist on a request for help with optimization, it is always good to have the explain. It would be valuable to have the explain for this query to see how many rows are participating from the various tables and joins and thus in the final result that has to have the roow_number applied.

 

The CASE expressions identified do not affect the spool size. The level of complexity of the case expressions is not a contributor to the spool. Very complex CASE expressions may have a CPU cost when run against a lot of rows but it doesn't affect the spool. If those columns are required in the resulting table, then that work needs to be done in the query.

 

The ITEM_LIST column computation may contribute significantly to the size of the spool. If a lot of the rows have PAGE_TYPE = 'SRP'  and the result of the function often fills out a large part of the varchar(1000) field, then a lot of bytes are being added to the row. Again though, if that string is required in the final table then it must be done.

 

Sometimes with a large set one simply needs more spool to sucessfully run the required work. Out of spool is not always a poorly written/inefficient query. 

Enthusiast

Re: how to optimize query to avoid 'no spool space error'

Thanks a lot for this super-detailed explanation! Now I have a much better understanding of this problemSmiley Very Happy