IQ 16 join a fact table with results from a stored procedure

[former_member187856]

For syntax wide, there is no issue here; but it is taking forever to execute.

The proc entitlement b returns few records; which just has 3 columns such as ( fiscal_year, month, gtran_id).

select sum(c.price), c.id

from facts c join entitlement ( 2015, 12, 12345) b

on c.fiscal_year = b.fiscal_year

and c.month = b.month

and c.id = b.gtran_id

and c.fiscal_year = 2015

The fact table C has billion rows; it has all the correct HG indexes.

Is there an issue with this kind of join?

Rey

[markmumy]

Yes, there is an issue.  When you have a procedure in the FROM clause, it causes the join to happen in the SA engine ano not in IQ.  Additionally, you are pushing data into the SA temp store which could fill up your default file system for it (typically /tmp).

Sometime this can be fast if the procedure is a single statement with no parameters.  In most cases, though, they are too complex To be optimized.

Can you run the same query but break it into two pieces?

select * into #temp_table from sp_yourproc();

select * from main_table, #temp_table where ......

See what the performance is on that.  If it is significantly faster then you know the issue is with the procedure.  If performance didn't change, I'd be surprised if it didn't, then the issue is with the main table, indexes, etc.

Mark

[former_member187856]

You are right on SA temp store; is there an easy way to know SA temp is used here, any msg or srv logging or set option to display out more debug information?

Thanks

[markmumy]

You would need to look into the SA options for monitoring.  Some info, or rather a lack of it, will be contained in the HTML query plans for IQ.  You should also be able to see the right info in the SA query plans.

Or simply watch the size of SA temp on disk.  If it's growing then someone is using it.  You really want to avoid using it at all as it can drag down performance.

Mark