Ticket S131883
Visible to All Users
Duplicate

Automatically split queries with In-Operator when too many operands are involved

created 16 years ago

Hello,
in some places we use the "InOperator" class to specify a list of values for filtering.
As we know in these cases XPO creates an SQL statement that containes a where clause of the form "where x in (1,2,3,4,…)" from this operator.
As we also know Oracle has a constraint on the number of operands in the "in" expression that is 1000. If one creates a select statement with an in expression that containes i.e. 1001 values an SQL Error
SQL Error: ORA-01795: "maximum number of expressions in a list is 1000"
will be returned.
Unfortunately XPO doesent take care of this constraint and therefore creates failing sql statements if more than 1000 values are provided to the InOperator.
Could you change the behaviour of the Oracle ConnectionProvider so that it createes multiple combined in-expressions in case there have been more than 1000 operands in the in expression?
best regards and thanks
Florian
Proposed Solution:
Change the behaviour of the Oracle ConnectionProvider to create multiple combined in-expressions in case there are more than 1000 operands in an in expression?
For example:
Instead of creating a in expression with more than 1000 values
select * from Orders where OrderId in (1,2,3,4,5,6,7,…,1201,1202,1203)
create multiple in expressions that are combined with an or operator
select * from Orders where (OrderId in (1,2,3,…,998,999) or OrderId in (1000,1001,…1201,1202,1203))

Comments (2)
Dennis Garavsky (DevExpress) 16 years ago

    Hello Andreas,
    Thanks for contacting us. We have discussed your suggestion with our XPO Team, and they stated that the variant with the OR operator won't work in a general case, and there is only one way to solve this problem - to split a large query into several smaller queries and then combine their results.
    It is very difficult to implement this task in a general case and benefits from this functionality won't justify all the effort spent on the correct implementation of this feature. But, this task can be easily solved for particular cases in an end application.
    Thanks,
    Dennis

      Hello Dennis,
      can you explain why "the variant with the OR operator won't work in a general case"?
      I think it should well work in a general case as actually a "x in (1,2,3,4)" expression can be translated to "x = 1 OR x = 2 OR x = 3 OR x = 4", exactly the same way that "x in (1,2) OR x in (3,4)" can be translated to the same expression "x = 1 OR x = 2 OR x = 3 OR x = 4".
      This should always work no matter what x is or what or how many values are inside the value list.

      Best regards
      Florian

      Answers approved by DevExpress Support

      created 16 years ago (modified 10 years ago)

      Hello Andreas,
      Sure, we can. First we also thought that splitting would work, but later we wrote a test, which failed with the System.Data.OracleClient.OracleException:
      ORA-03113: end-of-file on communication channel
         at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
         at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidD
      escriptor, ArrayList& resultParameterOrdinals)
         at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
         at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
         at System.Data.OracleClient.OracleCommand.ExecuteReader()
         at OracleIn.Program.Main(String[] args) in E:\work\OracleIn\Program.cs:line 24
      This appears when about 14000 parameters are used.
      While it seems that no generic solution exists for this particular scenario, these limitations of the database server can be covered by the end application depending on a precise business scenario and requirements.

      See Also:
      Maximum Capacity Specifications for SQL Server.
      A problem occurs with the InOperator when the collection exceds 2100 items

      Thanks,
      Dennis

        Comments (1)
        Dennis Garavsky (DevExpress) 16 years ago

          Hello Andres,
          Just a follow up to my previous message. I think I should apologize, because my last sentence was not completely correct. By saying "covered by the end application", I wanted to say that there is no way to completely handle these situations at the XPO level, because there will always be some limitation at the database level. I believe that the best way to handle this situation is to either restructure the application business logic to prevent this criteria from being created or manually split long statements to separate collections and then merge them after required objects are loaded.
          Please feel free to contact us if you have any additional questions related to this task. We'll be happy to help you.
          Thanks,
          Dennis

          Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

          Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.