[teiid-issues] [JBoss JIRA] Commented: (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:comment-tabpanel&focusedCommentId=12591382#comment-12591382 ] 

Steven Hawkins commented on TEIID-1529:
---------------------------------------

The processing order here is the same as the logical application of the clauses, since we can't push limits past order bys.  So we have a plan that looks like:

limit
  order by
    project
      ...

What you are looking for is an enhancement to push the application of the order by and the limit ahead of the projection, or alternative to raise the subquery evaluation above the ordered limit.

That should be easily doable in 7.4.



> 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
>
> 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