Immediately exit a query if a condition is verified

Database
Fan

Immediately exit a query if a condition is verified

Hi to everybody,

I'd like to to this: I have a query which usually takes some minutes to run; this query runs every night; I only need to run the query on Monday, but I can't change the automatic night job of every day. So I'd like to put in the query a trick such that if the day is not Monday it returns 0 rows in just some seconds, while if the day is Monday it executes the query as usual.

I can't use BTEQ.

Is there a way to do that?

Thank you very much in advance.

10 REPLIES
Senior Apprentice

Re: Immediately exit a query if a condition is verified

Add a condition based on current_date:

WHERE (your current conditions)
AND (CURRENT_DATE - DATE '0001-01-01') MOD 7 = 0

When you check Explains you'll still get some RETRIEVE/JOIN steps, but they should have an "unsatisfiable condition"

Dieter

Fan

Re: Immediately exit a query if a condition is verified

Thank you for your answer,

it works correctly indeed! But, since the whole query needs about 30minutes to run, even though the condition you posted is not satisfied it keeps executing the entire query (so, 30min), and after that it correctly outputs 0 rows.

But, I'd like that, when the condition you posted is false, it gives 0 rows in less than 1 minute (instead of 30mins). So such that if the condition is false it does not need to compute the whole query and it just exits.

So my current query is like this:

WHERE (my current conditions)

and it runs in about 30mins.

If I try to do this:

WHERE 1 = 0

AND (my current conditions)

it runs in 1 second and does not evaluate all other conditions!

But if I try

WHERE (CURRENT_DATE - DATE '0001-01-01') MOD 7 = 0

AND (my current conditions)

it runs in about 25 minutes, which is less than 30mins, but far away from what I'd like to have (<1minute).

I hopeI made myself clear! Is that possible to archieve?

Thank you very much again

Senior Apprentice

Re: Immediately exit a query if a condition is verified

What's your TD release?

"(CURRENT_DATE - DATE '0001-01-01') MOD 7 = 0" should be the same as "1=0"

Could you post output of explain?

Dieter 

Fan

Re: Immediately exit a query if a condition is verified

I'm using TD v6.01 V2R.

In fact, logically, I agree with you!

This is the EXPLAIN of the whole query when I put 1=0 (I hide real object names):

  1) First, we do a single-AMP RETRIEVE step from
DB.MYTABLE by way of the unique primary
index with unsatisfiable conditions derived by SAT. The estimated
time for this step is 0.01 seconds.
-> The row is sent directly back to the user as the result of
statement 1. The total estimated time is 0.01 seconds.

Which is pretty immediate and it explains why it runs in less than one sec.

This is an extract of the very long EXPLAIN when I put (CURRENT_DATE DATE '0001-01-01') MOD 7 = 0:

  7) We execute the following steps in parallel. 
1) We do an all-AMPs RETRIEVE step from
DB.MYTABLE by way of an all-rows
scan with a condition of ("((((DATE )- (DATE '0001-01-01'))
MOD 7 )= 0) AND
((DB.MYTABLE.C1 = (((DATE
)- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C2
= (((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C3 =
(((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C4
= (((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C5 =
(((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C6 =
(((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C7 =
(((DATE )- (DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C8 = (((DATE )-
(DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C9= (((DATE )-
(DATE '0001-01-01')) MOD 7 )) AND
((DB.MYTABLE.C10 >= ((((DATE )-
(DATE '0001-01-01')) MOD 7 )+ 18 )) AND
((DB.MYTABLE.C10 <= ((((DATE )-
(DATE '0001-01-01')) MOD 7 )+ 64 )) AND
((DB.MYTABLE.C11 = ((((DATE
)- (DATE '0001-01-01')) MOD 7 )+ 1 )) AND
((DB.MYTABLE.C12 = ((((DATE
)- (DATE '0001-01-01')) MOD 7 )+ 1 )) AND
((DB.MYTABLE.C13 = ((((DATE
)- (DATE '0001-01-01')) MOD 7 )+ 1 )) AND
((DB.MYTABLE.C1 = 0) AND
((DB.MYTABLE.C2
= 0) AND
((DB.MYTABLE.C3 = 0)
AND
((DB.MYTABLE.C4
= 0) AND
((DB.MYTABLE.C5 = 0)
AND ((DB.MYTABLE.C6 = 0)
AND ((DB.MYTABLE.C7 = 0)
AND ((DB.MYTABLE.C8 = 0) AND
((DB.MYTABLE.C9 = 0) AND
((DB.MYTABLE.C10 <= 64) AND
((DB.MYTABLE.C10 >= 18) AND
((DB.MYTABLE.C11 = 1) AND
((DB.MYTABLE.C12= 1) AND
((DB.MYTABLE.C13 = 1) AND
(DB.MYTABLE.C14 =
'INDIVIDUALS')))))))))))))))))))))))))))))") into Spool 5
(all_amps), which is redistributed by hash code to all AMPs.
Then we do a SORT to order Spool 5 by row hash. The input
table will not be cached in memory, but it is eligible for
synchronized scanning. The size of Spool 5 is estimated with
no confidence to be 380 rows. The estimated time for this
step is 52.38 seconds.
2) We do an all-AMPs RETRIEVE step from
DB.MYTABLE2 by way of an all-rows
scan with a condition of (
<some other irrelevant conditions)
into Spool 6 (all_amps), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 6 by row hash and the sort key in spool field1
eliminating duplicate rows. The input table will not be
cached in memory, but it is eligible for synchronized
scanning. The size of Spool 6 is estimated with no
confidence to be 3,310,704 rows. The estimated time for this
step is 34.45 seconds.
8) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to Spool 6 (Last Use) by way of an
all-rows scan. Spool 5 and Spool 6 are joined using an exclusion
merge join, with a join condition of (<a join condition>).
The result goes into Spool 7 (all_amps), which is built locally on
the AMPs. The size of Spool 7 is estimated with no confidence to
be 380 rows. The estimated time for this step is 0.03 seconds.

I've replaced object names with fake names but I've preserved the structure.

Why is it computing the whole query?

Thanks again

Senior Apprentice

Re: Immediately exit a query if a condition is verified

Oooops, V2R6.1 is quite old, the optimizer started to resolve CURRENT_DATE in a later version.

You say you can't use BTEQ, but how is the query scheduled then?

Dieter

Fan

Re: Immediately exit a query if a condition is verified

I mean that I can't use BTEQ (it is not allowed), but some other people in the IT staff can, so the query is scheduled by them :-)

Anyway that's explain everything! Thank you very much!

Will version 12 be sufficient to optimize this issue? (I expect a migration to v12 soon).

Senior Apprentice

Re: Immediately exit a query if a condition is verified

Of course is TD12 ok, but you'll probably not migrate to TD12 as this is still a very old version, there's TD13, TD13.10, TD14 and TD14.10 already :-)

You could tell the IT staff to add some lines to the existing script:

SELECT 'monday' WHERE (CURRENT_DATE - DATE '0001-01-01') MOD 7 = 0;

.IF ACTIVITYCOUNT = 0 THEN GOTO SkipMyQuery;

your SELECT....

.LABEL SkipMyQuery;
Enthusiast

Re: Immediately exit a query if a condition is verified

 I think the value in where clause should equate to 1, instead of 0 for Mondays. 0 should be for Sundays.

(CURRENT_DATE - DATE '0001-01-01') MOD 7=1

Senior Apprentice

Re: Immediately exit a query if a condition is verified

The numbering is based on whatever you want and 0001-01-01 was a monday.

SELECT CURRENT_DATE, (CURRENT_DATE - DATE '0001-01-01') MOD 7;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

Current Date ((Current Date-0001-01-01) MOD 7)
------------ ---------------------------------
2013-07-25 3

Dieter