All Forums Database
stefann 4 posts Joined 11/13
01 Nov 2013
How to simplify query that runs out of spool space?

 hello,
i have a query that runs out of spool space as it performs a count on several joined tables (there are multiple 1 - Many relationships and one Many - Many relationship, which is the only way it can be joined based on avaialble tables and columns)
how can you go about resolving this issue? how would you rewrite it using a dervied table?
thank you all for your help!
 
stefan
 
general syntax of the query is below:
=========================================
select count(distinct CustomerID) as NoOfCustomers

from table1

join table2
    on table2.columnname = table1.columnname
join table3
    on table2.columnname = table3.columnname
join table4
    on table4.columnname = table1.columnname
join table5
    on table4.columnname = table5.columnname
    
where condition1
and condition2
and condition3
and condition4
and condition5

order by 1

M.Saeed Khurram 544 posts Joined 09/12
01 Nov 2013

These joins are quite simpler for Teradata. The things you can modify about these join is to make sure that the data types of joining columns are are same. Collecting statistcis will most probably avoid the out of spool issue. If still the issue persists, you can use derived or volatile tables.
Or you can write:

select count(distinct CustomerID) as NoOfCustomers

from 
(
SELECT CustomerID
FROM table1
join table2
    on table2.columnname = table1.columnname
join table3
    on table2.columnname = table3.columnname
join table4
    on table4.columnname = table1.columnname
join table5
    on table4.columnname = table5.columnname
where condition1
and condition2
and condition3
and condition4
and condition5

)DRV

order by 1

 

Khurram

stefann 4 posts Joined 11/13
01 Nov 2013

hi Khurram,  all the data types of the joining columns are the same...i tried rewriting the query like the example above, but the result is the same Out of Spool space issue

M.Saeed Khurram 544 posts Joined 09/12
01 Nov 2013

Have you collected the required stats?

Khurram

M.Saeed Khurram 544 posts Joined 09/12
01 Nov 2013

Place the following before your query:

DIAGNOSTIC HELPSTATS ON FOR SESSION;
--Please your query here.

Now press F6, at the end of explain plan, you will get the required stats to be collected. 
From the explain plan you can also deduce why the query is going out of spool.
 

Khurram

dnoeth 4628 posts Joined 11/04
01 Nov 2013

A m:n-join might result in a huge intermediate spool causing the no more spool error.
Without knowledge about your data model it's impossible to tell exactly how to rewrite your query, but many rows per x might be reduced to one using a DISTINCT x in a Derived Table or a subquery instead of a join.
 
Dieter
 

Dieter

Raja_KT 1246 posts Joined 07/09
01 Nov 2013

Hi,
Why can't you post your explain here so that we can analyze , the demography, steps etc.
Cheers,
Raja
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

stefann 4 posts Joined 11/13
01 Nov 2013

thank you all for your replies, unfortunately I can't post details of the code due to confidentiality...but your input gave me some good ideas...thanks....i will come back if I have further questions

stefann 4 posts Joined 11/13
04 Nov 2013

hello everyone, just want to post a quick update...my issue has been resolved by redesigning the queries using subqeuries and derived tables...thank you for your input!

vamsi.srivastav 1 post Joined 11/13
15 Nov 2013

Hi All, Can some one point me to a document which talks about the TD 14 version new additional SQL standards that improve the performance of the query. Either in terms of indexes, Partitions, or general SQL statements.

M.Saeed Khurram 544 posts Joined 09/12
18 Nov 2013

Hi Vamsi,
You can fin some of the new features introduced in TD 14 at the following:
http://pk.teradata.com/News-Releases/2011/Teradata-Database--the-Smartest-Database-for-the-Smartest-Analytics/?LangType=1056
 

Khurram

You must sign in to leave a comment.