-
1. Re: Performance Issue with IN clause when used with TEMP Table
tanmoypalit Jan 2, 2013 9:23 AM (in response to tanmoypalit)Got a very ugly way for doing this but it works with better performance:
CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE string VARIABLES.UI_LIST;
DECLARE string VARIABLES.query;
VARIABLES.UI_LIST = REPLACE(Dev_View.getSTATISTICS.UILIST, ',', ''',''');
VARIABLES.query = (('SELECT * FROM STATS_XML.CHEMISTRY_TRANSACTIONDocument WHERE STATS_XML.CHEMISTRY_TRANSACTIONDocument.CHEMISTRY_TRANSACTION.CHEM_STATS.@Unique_Identifier IN (''' || VARIABLES.UI_LIST) || ''')');
EXECUTE IMMEDIATE VARIABLES.query;
END
-Tanmoy
-
2. Re: Performance Issue with IN clause when used with TEMP Table
shawkins Jan 2, 2013 9:39 AM (in response to tanmoypalit)The issue is with the optimizer not understanding how many records are in the temp table when the xml statement plan is compiled (which is happending prior to execution). Thus the non-performing plan is likely delaying the effect of the identifier filtering to a later join and pulling back too many records.
The use of dynamic sql should also be effective even with the IN subquery as the statement plan will be made when the temp table record count is known. Another approach on Teiid 7.4+ would be to use the dependent join hint for the subquery: "/*+ DJ */ (SELECT VT_LIST.UI FROM VT_LIST)", which instructs the planner to use the IN list as a filter first. See the hints section of the Reference - https://docs.jboss.org/author/display/TEIID/Subquery+Optimization
Ideally we should be automatically handling this case as we should guess at your intent, rather than forcing you to be explicit and/or allowing for the use of something like the SQL Server "WITH RECOMPILE" option to indicate that a query should be replanned. Can you log an issue on more optimistic dependent join planning?
Steve
-
3. Re: Performance Issue with IN clause when used with TEMP Table
tanmoypalit Jan 2, 2013 2:59 PM (in response to shawkins)Thanks Steven.
/*+ DJ */ (SELECT VT_LIST.UI FROM VT_LIST) worked well.
Issue created:
https://issues.jboss.org/browse/TEIIDDES-1552
Regards
Tanmoy Palit