[teiid-issues] [JBoss JIRA] Resolved: (TEIID-1529) LIMIT not pushed down for queries with subselects and ORDER BY

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1529?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-1529.
-----------------------------------

    Fix Version/s: 7.4
       Resolution: Done


Added a check in ruleplansorts to move projection above ordered limits when possible.  The logic is fairly basic and targeted at simple ordering (ordering on an expression may cause the optimization not to happen) situations instead of being a general approach to raising projection.

> LIMIT not pushed down for queries with subselects and ORDER BY
> --------------------------------------------------------------
>
>                 Key: TEIID-1529
>                 URL: https://issues.jboss.org/browse/TEIID-1529
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 7.3
>            Reporter: Mark Addleman
>            Assignee: Steven Hawkins
>             Fix For: 7.4
>
>
> It doesn't look like the engine pushes down LIMIT in the following query:
> SELECT B."SYSID", B."USERID", (SELECT COUNT(*)  FROM 
> (SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B ORDER BY B."USERID" ASC LIMIT 10 
> However, the engine does push down the LIMIT in the same query without the ORDER BY:
> SELECT B."SYSID", B."USERID", (SELECT COUNT(*)  FROM 
> (SELECT * FROM notes.RETRIEVE_NOTES WHERE OBJECT_PKEY = XMLSERIALIZE(XMLELEMENT("SECURITY.BASEUSER", XMLATTRIBUTES(B."SYSID",B."USERID")) as String)) as foo) as C_notesForObject, 'SECURITY.BASEUSER' as "__objecttype__" FROM "SECURITY.BASEUSER" as B LIMIT 10 
> Another piece of relevant information is notes.RETRIEVE_NOTE is a stored procedure backed by java code in a translator.
> Since SECURITY.BASEER contains ~20,000 rows and each row requires a select against the RETRIEVE_NOTE stored procedure, the difference in processing time is huge:  about two minutes with the ORDER BY versus a few hundred milliseconds without the ORDER BY.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira