How to Join Sub-Queries?

[former_member202551]

Hi Experts

I have a problem with the following query, if I execute only "SISTEMA" sub-query it take 3 seconds and return 7.8 Million records , if I execute "entidad" sub.query it take another 3 seconds and return 15.8 million records, but when I join those two sub-queries it take about 1 minute, any idea of how improve that

SELECT *

from

(

SELECT      deo_cod,

                   MAX(cla_ncod) AS CLASIF,

                        COUNT(DISTINCT ENT_NCOD) AS NumeroEntidades

FROM        "_SYS_BIC"."sbs/AN_DETALLE"

WHERE       per_ncod = '20130131' 

GROUP BY  deo_cod 

) SISTEMA ,

(

SELECT      deo_cod,

                  ent_cdec,

                  cla_ncod

FROM        "_SYS_BIC"."sbs/AN_DETALLE"

WHERE       per_ncod = '20130131'

GROUP BY deo_cod, ent_cdec, cla_ncod

) entidad

WHERE sistema.deo_cod = entidad.deo_cod  

Regards

Cristian

[Former Member]

So your approach is a good one: you are using Analytic Views to get great performance from large star schemas. A Window Function will definitely not work here because Window Functions unfortunately execute in the row store this will transfer a truck load of data and be very slow.

Normally in this case I recommend building a calc view on top of both Analytic Views and then using Union with Constant Values.

Is there any reason why you can't do this rather than a join on two subqueries?

If you really need a join then I recommend you create a scripted calc view and use CE Functions. I've used this in your scenario and it performs well.

Hope this helps.

John

[henrique_pinto]

Hi John,

how would you implement COUNT DISTINCT aggregation in CE functions?

Best,

Henrique.

[Former Member]

First of all there two queries are actually inline views rather than sub queries...

Now coming to your issue looks like the only reason you are trying to have two inline views and then joining is to have the grouping at different grain level. I will suggest using windows functions to get the same results as it will eliminate the requirement of splitting to two inline views and joining them back together. And the main reason your performance is not so good is the join clause and after you eliminate that you should get a much better performance..

Now this query will become something like below

SELECT      deo_cod,

                   MAX(cla_ncod) AS CLASIF,

                        COUNT(DISTINCT ENT_NCOD) AS NumeroEntidades

FROM        "_SYS_BIC"."sbs/AN_DETALLE"

WHERE       per_ncod = '20130131'

GROUP BY  deo_cod

SELECT      deo_cod,

                   MAX(cla_ncod) over (partition by deo_cod ) as CLASIF,

Please check the below link for Windows function reference..

http://help.sap.com/hana/html/_esql_functions_window.html

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a353327519101495dfd0a87060a0d3/content.htm

[former_member202551]

Hi

I already tried to use Windows functions, the problem with that the time is similar to the way that I posted and DISTINCT function is not supported.

To solve the two sub queries the database take 3 seconds and has to read a Start schema with a fact table of 3.7 billion records and many dimensional tables of 10 millions records, so I don't understand with the JOIN of the result of those two queries take 1 minute consider that is less data with no aggregates than prior queries.

Regards

Cristian