skip to main content
Designing and coding the IP : Join processing : Implementing join pushdown : Limitations of using join pushdown on large joins
 

Limitations of using join pushdown on large joins

The current implementation of join pushdown does not use disk storage for saving the inner table rowsets that the IP returns. Therefore, depending on the number of tables and the amount of data in the tables, large amounts of memory can be used when you implement join pushdown. Based on the amount of physical memory available, the OpenAccess SDK SQL engine may exceed the resource usage and not be able to complete the query execution.
Consider the following query:
SELECT ... FROM T1, T2, T3, T4, T5, T6 WHERE...
If the IP is capable of building pushdown rowsets for each of the inner tables (T2 to T6) and if each table averages 100 matching inner rows for corresponding outer rows, this causes (100*100*100*100*100*100) pushdown rows to be built.
Based on your database size, you may need to restrict when the IP builds pushdown rowsets. You may need to check the ProcessOrder of the inner table and if it is more than some value (for example, 3), avoid building pushdown rowsets. In this case, the OpenAccess SDK SQL engine uses regular join processing to get results for the corresponding inner tables.
/* get the next join table */
hstmt_next = dam_getNextJoinStmt(pParentStmtDA->dam_hstmt);
if (!hstmt_next) return DAM_SUCCESS;
 
/* check the query process order and the size of the join */
{
Uint16 iValue;
int iValueLen;
 
iRetCode = dam_getInfo(NULL, hstmt_next, DAM_INFO_QUERY_PROCESS_ORDER,
(void *)&iValue, sizeof(iValue), &iValueLen);
iProcessOrder = iValue;
}
 
/* disable pushdown joins on tables with high process order */
if (iProcessOrder >= 2) return DAM_SUCCESS;
 
/* continue building pushdown rowsets */