Efficiency of QUALIFY

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

Efficiency of QUALIFY

Hi all,

 

I have a 300 to 500 million row transactional table where I am trying to further filter records using a QUALIFY ROW_NUMBER() OVER(PARTITION BY <columns> ORDER BY <column>) over the entire set of data. Now the table has a Composite Primary Key consisting of 8 columns that in combination make each row unique.

 

Would it be more efficient to do the QUALIFY clause over 1 single column that concatenated together all 8 columns as a VARCHAR(100) column? I would be doing the concatenation in the SELECT portion of the query and just refer to the alias in the QUALIFY part.

Or would it be better to do the QUALIFY over each of the 8 columns individually ie: list all 8 columns in the PARITION BY clause? 

 

Thanks

 


Accepted Solutions
Junior Apprentice

Re: Efficiency of QUALIFY

Hi,

Based purely on the explain plan, I think you can expect the PARTITION BY individual columns (not concat) to run faster. If the PARTITION BY columns include concatenation (either directly or by alias) then the data will be redistributed in order to put the rows into the correct partitions.

 

What may also make a difference is whether your PARTITION BY columns include the PI columns or not. If the PARTITION BY columns include the PI columns then the rows for any partition are already on  the same AMP as each other - no redistribution

 

The following come from a TD16.10 system, you may need to check the plan on your system:

 

When the PARTITION BY column(s) match or include all of the PI column(s) then the 'stat function' step shows the following:

  3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from WARD.a by way
     of an all-rows scan with no residual conditions into Spool 5 (Last
     Use), which is built locally on the AMPs in TD_Map1.  The result
     rows are put into Spool 1 (group_amps), which is built locally on
     the AMPs.  The size is estimated with high confidence to be
     1,051,564 rows (8,954,067,460 bytes).  The estimated time for this
     step is 6.02 seconds.

Note that 'spool 5' is "built locally".

 

When the PARTITION BY columns do not include all of the PI column(s) then the 'stat function' step shows the following:

 3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from WARD.a by way
     of an all-rows scan with no residual conditions into Spool 5 (Last
     Use), which is redistributed by hash code to all AMPs in TD_Map1.
     The result rows are put into Spool 1 (group_amps), which is built
     locally on the AMPs.  The size is estimated with high confidence
     to be 1,051,564 rows (8,638,598,260 bytes).  The estimated time
     for this step is 6.02 seconds.

Note that 'spool 5' is "hash redistributed" - based on the 'partition by' columns. This is normally more work for the dbms to do and hence a slower  running query.

 

Some empirical testing with 3M rows (which I know is a lot less than your table) shows that when the PARTITION BY column(s) match the PI column(s): cpu dropped by @ 10-15% compared to not having the PI columns in there.

 

If PARTITION BY column(s) include the PI column(s) then the cpu drop compared to not having the PI columns in there was only @ 1-2%.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
9 REPLIES
Enthusiast

Re: Efficiency of QUALIFY

hi tduser1, 

1) In some cases, the concatenation and single column in partition by might go for re-distribution in all amps whereas all columns as is operation is local to all amps (or)

2) it could be same operation from parsing engine in both scenarios depends on the collect stats on the columns that you choose.

So best thing is, look at the explain plan.. it should give idea which one to follow with least expensive plan...or paste explain plans of both scenarios here

 

Junior Apprentice

Re: Efficiency of QUALIFY

Hi,

 

I'm probably misunderstanding your requirements here, but if you use "ROW_NUMBER() OVER(PARTITION BY <columns> ORDER BY <column>)" and the PARTITION BY column list is unique you're always going to get an answer of 1 for each row in the table.

 

Is this what you want?

Do you simply want a new column in your SELECT list with a value of 1 in it? - there are easier ways of doing that

 

What have I missed here?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Efficiency of QUALIFY

Hi Dave,

the question is on which is better performing query out of below 2

 

SELECT

C1||C2||C3||C4||..||C8 as C_PK,

C10, C11.... FROM TAB1

QUALIFY ROW_NUMBER() OVER(PARTITION BY C_PK ORDER BY C_PK)"

(OR)

SELECT C1, C2, C3, .. C10, C11

FROM TAB1

QUALIFY ROW_NUMBER() OVER(PARTITION BY C1||C2||C3||C4||..||C8 ORDER BY C1||C2||C3||C4||..||C8 )

Highlighted
Junior Contributor

Re: Efficiency of QUALIFY

None of those.

Concatenating 8 columns is unneccessary overhead (additionally existing stats will be lost).

Ordering by the same 8 columns is unneccessary overhead, any single column will be ok.

 

SELECT C1, C2, C3, .. C10, C11
FROM TAB1
QUALIFY
   ROW_NUMBER()
   OVER(PARTITION BY C1, C2, C3, ..., C8
        ORDER BY C1 ) -- do you really don't care which row will be returned?

 

Junior Apprentice

Re: Efficiency of QUALIFY

Hi acumens,

I agree.

My reason for asking was to avoid anyone wasting time answering a question that may not have been the real question - given that using the ROW_NUMBER function in that scenario would always give an answer of 1 for every row. All I did was to question if that was the real requirement.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Efficiency of QUALIFY

Ok, let me clarify my question and a bit more background,

 

So the 8 columns that I said were the composite primary key turned out to not uniquely identify each row as I originally thought it would. 

Now there's a potential 9th column that could be added to form the composite primary key which would make each row unique, however this needs to be confirmed with business as they originally advised that the 8 columns would be enough.

 

So in the event that I should only use the 8 columns, then I would have to somehow deal with the "duplicate" record. Hence why I thought of the QUALIFY PARTITION BY strategy. Now *if* (not saying that I absolutely will) I were to go with this method, what would be best? Concat as VARCHAR or separate column by column in the PARTITION BY? Also I would be using a datetime column in the ORDER BY part to give a unique ordering 1,2,3 etc.

 

Sorry for the confusion. 

Re: Efficiency of QUALIFY

And as acumens already pointed out, concatenating columns is probably not the best idea due to stats and potential redistribution.

I will have a look at the explain plan once I have enough volume in the table. 

Junior Apprentice

Re: Efficiency of QUALIFY

Hi,

Based purely on the explain plan, I think you can expect the PARTITION BY individual columns (not concat) to run faster. If the PARTITION BY columns include concatenation (either directly or by alias) then the data will be redistributed in order to put the rows into the correct partitions.

 

What may also make a difference is whether your PARTITION BY columns include the PI columns or not. If the PARTITION BY columns include the PI columns then the rows for any partition are already on  the same AMP as each other - no redistribution

 

The following come from a TD16.10 system, you may need to check the plan on your system:

 

When the PARTITION BY column(s) match or include all of the PI column(s) then the 'stat function' step shows the following:

  3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from WARD.a by way
     of an all-rows scan with no residual conditions into Spool 5 (Last
     Use), which is built locally on the AMPs in TD_Map1.  The result
     rows are put into Spool 1 (group_amps), which is built locally on
     the AMPs.  The size is estimated with high confidence to be
     1,051,564 rows (8,954,067,460 bytes).  The estimated time for this
     step is 6.02 seconds.

Note that 'spool 5' is "built locally".

 

When the PARTITION BY columns do not include all of the PI column(s) then the 'stat function' step shows the following:

 3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from WARD.a by way
     of an all-rows scan with no residual conditions into Spool 5 (Last
     Use), which is redistributed by hash code to all AMPs in TD_Map1.
     The result rows are put into Spool 1 (group_amps), which is built
     locally on the AMPs.  The size is estimated with high confidence
     to be 1,051,564 rows (8,638,598,260 bytes).  The estimated time
     for this step is 6.02 seconds.

Note that 'spool 5' is "hash redistributed" - based on the 'partition by' columns. This is normally more work for the dbms to do and hence a slower  running query.

 

Some empirical testing with 3M rows (which I know is a lot less than your table) shows that when the PARTITION BY column(s) match the PI column(s): cpu dropped by @ 10-15% compared to not having the PI columns in there.

 

If PARTITION BY column(s) include the PI column(s) then the cpu drop compared to not having the PI columns in there was only @ 1-2%.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Efficiency of QUALIFY

Thanks Dave for the detailed answer.

Cheers.