SAP IQ QUERY PLAN

[former_member596754]

Would like to know how to identify the problem node from the HTML query plan:

file-001.png

How can we fix Generated result rows vs Estimated result Rows big difference.

Est. temp space used and Act. Temp space used also having big difference.

PFA

[markmumy · Accepted solution]

This is only a problem if you feel that the query should run faster. The difference between actuals and estimates can stem from many areas. I could be related to not having an index that has distribution statistics. Could be related to a search argument that does something like a SUBSTR, PATINDEX, etc where the matching values is unknown until runtime. Could be related to what we think is a many-to-many join but in reality it is more on the order of one-to-one or one-to-many. Without the full plan it is impossible to diagnose.

I would also advise that you look at the response on the IQUG for this same query. It was answered there as well.

Definitely conditions like this one doesn’t help IQ to “estimate” the nb of rows:

"RIGHT"(ISNULL(DP_AC_TXN_DTL.ORGN_TR_NB,DP_AC_TXN_DTL.ATM_LOC_ADDR_TX),7) = cast(SWIFT_INBND_OTB_TXN_DTL.TXN_ID as varchar(7))

Would be best for the above to add an extra column containing the value of right(…, 7); and add an HG index on that column.

Then during data load, feed the column with the formula right(…,7) where it will be populated at load time (or via storedproc; up to you!)

Mark

[former_member596754]

@Mark, Thanks for your advise.

Need one more help; As per the following query the join is retuning many to many in the query plan since it has to fetch the latest record from both the tables. The cost of join also very high; can we extract the latest record from the main tables to a temp and compare? Kindly advise.

SELECT A.ACC_NB FROM EDW_CORE_V.DP_AC_PRFL A

INNER JOIN EDW_CORE_V.PTY B

ON A.RIM_NB=B.RIM_NB

WHERE A.OBSV_END_DT='9999.12.31' AND B.OBSV_END_DT='9999.12.31'