[IQUG] [EXTERNAL] Re: more strange IQ16 performance

John KIRKWOOD john.kirkwood at uk.bnpparibas.com
Thu Jul 19 07:37:01 MST 2018


Yes it could but the volume for one clc_dt of data is pm_prd: 1G and deal_val_exp: 2G in each of the tables

So cannot see that having an impact – maybe SAP can tell me how to estimate that

JOhn


From: John Anthony [mailto:chirayithaj at gmail.com]
Sent: 19 July 2018 15:29
To: John KIRKWOOD
Cc: iqug at iqug.org
Subject: [EXTERNAL] Re: [IQUG] more strange IQ16 performance

Can lower memory in TEST not explain the difference ?

Overall sortMerge is preferred when QP estimates runtime available memory to be insufficient for other strategies - a push down does more in-memory filters.

-JA

On Thu, Jul 19, 2018 at 9:12 AM John KIRKWOOD <john.kirkwood at uk.bnpparibas.com<mailto:john.kirkwood at uk.bnpparibas.com>> wrote:
We refreshed our TEST database with our PRD database

The command

select count(*)
from deal_val_exp as expo_deal
inner join pm_prd as prd on prd.clc_dt = expo_deal.clc_dt and prd.prd_hsh_id = expo_deal.prd_hsh_id
where case when prd.rsk_site = 'NA' then expo_deal.rsk_sit_cod else prd.rsk_site end in ('ALM-BNL-IRD')
and (((expo_deal.sim_mtd_cod in ('OFFICIAL')))) and expo_deal.clc_dt  = '2017/12/29' and expo_deal.node_cod in ('RN_ALM_BNL_IRD')

registers a fetch time of 620ms in PRD  (0.48s on the plan)
it does a sort-merge push down join on pm_prd to project 664 rows from both tables

the same command registers a fetch time of 1s in TEST (0.85s on the plan)
it does sort-merge join projecting 664 rows from deal_val_exp and 10,166,043 rows from pm_prd (based on clc_dt)

I cannot get it to do a push down join even with the appropriate join_preference setting

Schema/indexes is same; data distribution is same – although again I cannot see any cardinality on sp_iqhelp for the TEST tables – which is very annoying and extremely suspicious as it shows on PRD sp_iqhelp
Prd_hsh_id and clc_dt both have HG index on both tables
Datatypes are identical on both tables hsh_id unsigned bigint; clc_dt date

This is part of a much larger command which runs in 1 second in PRD but runs over our 10 minute timeout in TEST

PRD has main and temp buffers of 140G
TEST has main and temp buffer space of 50G

Appreciate any help on why the plans are different?
The command does not exactly stretch the boundaries of anything that I can see

JOhn


___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for additional disclosures.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug

___________________________________________________________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for additional disclosures.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180719/56884885/attachment-0001.html>


More information about the IQUG mailing list