SELECT: too many nested operators
All,
I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with "SELECT: too many nested operators" error while executing this SQL.
While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it's set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can't split the SQL too.
Regards, Sreejith
On Tue, Dec 19, 2017 at 03:57:46PM +0000, Sharma, Sreejith wrote:
All,
I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with “SELECT: too many nested operators” error while executing this SQL.
While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it’s set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can’t split the SQL too.
I think most thread libraries have the default THREAD_STACK_SIZE at 2M. The 16K is a boundary we need, as not all functions are directly protected by this stack overflow protection mechanism.
niels
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Even if your scenario is valid, perhaps you could reduce the depth of nesting without changing the query semantics? Or rearrange the order of things? e.g.
cond1 AND (cond2 AND ( cond3 AND cond4 )
could become
( cond1 AND cond2 ) AND ( cond3 AND cond4 )
or
( ( cond1 AND cond2 ) AND cond3 ) AND cond4
and that might not get to such a full stack.
Eyal
On 12/19/17 4:57 PM, Sharma, Sreejith wrote:
All,
I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with “SELECT: too many nested operators” error while executing this SQL.
While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it’s set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can’t split the SQL too.
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
The thread limit is an OS limit and not something to play around to solve a rather poor query generator. I suggest to consider another kind of code generation.
regards, Martin
On 19/12/2017 19:26, Eyal Rozenberg wrote:
Even if your scenario is valid, perhaps you could reduce the depth of nesting without changing the query semantics? Or rearrange the order of things? e.g.
cond1 AND (cond2 AND ( cond3 AND cond4 )
could become
( cond1 AND cond2 ) AND ( cond3 AND cond4 )
or
( ( cond1 AND cond2 ) AND cond3 ) AND cond4
and that might not get to such a full stack.
Eyal
On 12/19/17 4:57 PM, Sharma, Sreejith wrote:
All,
I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with “SELECT: too many nested operators” error while executing this SQL.
While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it’s set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can’t split the SQL too.
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
This is a questionable hack and bypasses the first question: What is the performance you expect to get from such code? A simple scientific experiment would charter the performance curve from 1K, 2K, 4K.... You might quickly detect that the work to be performed by an optimizer might go beyond your limits and you have to think about smarter ways to implement your BI solution.
It is well-known that particularly ORM-based systems can have a bad SQL generation component, due to many reasons, which call for high-level assessment of the task ahead. This code looks like a code generator that walks through a dimension table to encode in a very poor way a join /set operations then using the fact that the dimension table is already in the server and fast join/set operators are available.
regards, Martin
On 19/12/2017 19:26, Eyal Rozenberg wrote:
Even if your scenario is valid, perhaps you could reduce the depth of nesting without changing the query semantics? Or rearrange the order of things? e.g.
cond1 AND (cond2 AND ( cond3 AND cond4 )
could become
( cond1 AND cond2 ) AND ( cond3 AND cond4 )
or
( ( cond1 AND cond2 ) AND cond3 ) AND cond4
and that might not get to such a full stack.
Eyal
On 12/19/17 4:57 PM, Sharma, Sreejith wrote:
All,
I have a valid scenario where our application generates a SQL which is around 4.4MB and contains 75K of combination of AND and OR operators. This SQL fails with “SELECT: too many nested operators” error while executing this SQL.
While I went through the code, I noticed any thread size above THREAD_STACK_SIZE - 16 * 1024 is getting aborted. Is there any possibility that we can change this? Any reason why it’s set to 16MB? As server our server has enough memory and storage and we need to understand if this can be handled in code or via parameter as this is valid scenario. Also, we can’t split the SQL too.
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Eyal Rozenberg -
Martin Kersten -
Niels Nes -
Sharma, Sreejith