In Teiid single should not be monopoly the all the JVM available. It should be in bounds defined by the processor sizes and buffer manager settings. So, something anomaly is at play. You can
1) Do memory profiling and see who is holding on to the memory. That would be helpful insight to debug or point where the culprit is. See VisualVM or JConsole tools.
2) You can also test with latest version of Teiid and see if the behavior is same, as could have been fixes in buffer manager area. Typically if we found any bugs like these they also go into dot releases like 9.0.1, 9.0.2 etc, but that depends upon the found bugs. You can look through the JIRA history.
After investigation, Teiid is dumping the table in the memory when using LPAD function into the group by.
Here is a simplified query that illustrates the case:
SELECT sum("StreamCards"."Amount") AS SumAmount ,
LPAD(Month("StreamCards"."DateTime"),2,'0') as "MonthNo"
FROM "implify"."dbo"."StreamCards" "StreamCards"
group by LPAD(Month("StreamCards"."DateTime"),2,'0')
The group by clause is not sent by Teiid in the executed JDBC query;
when removing LPAD , group by is sent in the JDBC query.
You find attached the query plan.
query plan.xml.zip 949 bytes
Mark, there are two things to follow up on here. The first is that ldap is not pushed down to sql server. That can be compensated for with an expression, so that needs captured as a jira.
The other issue is to determine if the processing is appropriate when it is not pushed down. That will largely depend on your memory settings - have you changed anything from the defaults and are you experiences an out of memory error?
No i haven't change anything in the default memory settings.
As long as you are not seeing an out of memory error, it is entirely possible that Teiid may be using that amount of memory - although it may not all be held through hard references. You'd have to take a heap dump to understand further what is happening.
[TEIID-4761] Support lpad/rpad pushdown to sql server - JBoss Issue Tracker was logged to track the pushdown.