faster alternative to where clause in SQL

[rajarshi_muhuri]

Hi

Analytical Views have constraint filters that filter data before pulling the data up , The same happens with input parameters ( place holders) that filter data before pulling the data up .

Select A, B , C from tableA where A='constant' does the same function except its filters the data after its fetched , so its inefficient .

What is the SQL statement ( or CE script statement ) that places filters before pulling the data .

Rishi

[former_member184768 · Accepted solution]

Hi Rishi,

HANA tries to push down the filters as much as possible. You can see that from the Query plans (Visual Plan or Explain plan). So I don't think that it is inefficient.

In case you need to use CE function to filter the data, then you can use CE_CALC with the filter like mentioned in the help documents:

===================================

You create a CE_PROJECTION which uses CE_CALC to calulate the VAT rate for the

SALES

column.

with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",

CE_CALC

('"SALES" * :vat_rate', decimal(10,2)) AS "SALES_VAT"], '"CNAME" = '':cname''');

This is semantically equivalent to the following SQL:

with_tax2 = SELECT cid, cname, oid, sales, sales * :vat_rate as sales_vat FROM :product WHERE cname = ':cname';

===================================

Just one point, CE_CALC is really not required to perform the filter, it is just an example code.

Regards,

Ravi

[rajarshi_muhuri · Accepted solution]

Thanks Ravi

[lbreddemann]

Rajarshi Muhuri wrote:
Select A, B , C from tableA where A='constant' does the same function except its filters the data after its fetched , so its inefficient . 

What makes you believe this?

- Lars

[rajarshi_muhuri]

That was the idea given by a consultant from SAP , that placeholders/input param push the filters deep into the data base unlike where clause .

My objective is to left outer join two massive fact tables (400 + million records each).

So I been looping over time period to break up into sizable chunks. 

create procedure sp

as

xyear := 2010;

begin

while year <= 2020

var_a = select dim1, dim2, mea1 , mea2 from _sys_bic.AN_FACT1  where year = : xyear group by dim1, dim2 ;

var_b = select dim3, dim4, mea3, mea4 from _sys_bic.AN_FACT group by dim3, dim4;

var_join = select dim1,dim3 , mea1, mea2, mea3, mea4 from var_a left join var_b on dim2=dim3 ... ;

insert  into ACTUAL_TABLE (select * from var_join);

xyear = : xyear +1;

end while;

end;

What optimizations can i bring to this ( other than using CE_SCRIPTS)

and

I am writing to a actual table , how can i store the looped result sets into a table_type .

Rishi

[former_member184768]

Hi Rishi,

one thought,

Did you try creating a Calc view with the join of analytic views AN_FACT and AN_FACT1 using Input parameter xyear. In the procedure you can fire a select against the Calc view with values passed as parameters and then insert into the table.

There is also another alternative of using the left outer joins in two different analytic views each with one fact table and then using these Analytic views in UNION. But I am not very confident about the performance of this approach over the one mentioned above.

Regards,

Ravi

[rajarshi_muhuri]

Ravi , I tried union with constants ,

TABLE A has DIM1, DIM2, DIM3, MEA1, MEA2

AND

TABLE B HAS DIM4, DIM5, DIM6, MEA3, MEA4

but I could not manage to get DIM`1, DIM2, DIM5, MEA1, MEA2, MEA4 into 1 single line

Can you give more insights , if i am able to do an union , its definitely less expensive than a join .

rishi

[former_member184768]

Hi Rishi,

If you want them to be in a single line, then you have to go for the join. Just that try the join in the Calc view rather than in the procedure.

Regards,

Ravi