Multiple queries vs single query in sql assistant

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Multiple queries vs single query in sql assistant

Hi Experts,

 

If I have 10 queries to run in sql assistant. From spool utilization perspective, which approach will be more efficient , is it running all 10 queries in one go or 1 query at a time ?

 

Thanks,

Minn

3 REPLIES
Junior Contributor

Re: Multiple queries vs single query in sql assistant

Define "one go".

 

When submitted sequentially (=F5) each query's spool will be released after finishing it.

 

When they're submitted as a single Multi Statement Request (= F9) it might be more efficient overall, but will probably need (way) more spool (all answer sets are kept in spool until all queries finished).

 

 

Enthusiast

Re: Multiple queries vs single query in sql assistant

Thanks Dieter !!

When you say it "might be more efficient", in what scenario you feel that this will be efficient ?

Teradata Employee

Re: Multiple queries vs single query in sql assistant

There are cases when the multiple statements will operate on the same tables or subqueries in the same way. In that case, the multi-statement request will allow the optimizer to recognize and re-use the spool file rather than computing it again. This can be observed in the Explain by looking for spools that are used but not marked last use, then are used again later in the plan. If this is the case and if these spool files are large while the result set is small, then it is possible it will be more efficient in spool space usage as well.