491 | | === Optimization === |
492 | | |
493 | | One use case was submitted that requested new capabilities to better control the building of a FDO filter. In this specific use case, the filter was build with multile use of the ''!FdoFilter::Combine()'' interface to combine different parts of a filter via the ''OR'' operation. The provided information described that the execution of a select command will fail on some providers if the number of those sub filters excceeds a certain limit. The request was for a new capability that would indicate how many sub-filters can be combined within a provider. |
494 | | |
495 | | Investigation into this use case, however, showed that there is an issue with the process that transforms a filter to a SQL statement. If a node in the tree is reached that combines two sub-filters then the process does not look at the sub-filters to determine if it is required to nest them but rather goes ahead and nests them anyway. This way, the resulting SQL statement contains a deep nesting of sub filters. The following demonstrates this on a small scale: |
496 | | |
497 | | {{{ |
498 | | |
499 | | Filter: |
500 | | |
501 | | a = 1 or a = 2 or a = 3 or a = 4 |
502 | | |
503 | | Generated SQL statement: |
504 | | |
505 | | ((((a = 1) or (a = 2)) or (a = 3)) or (a = 4)) |
506 | | |
507 | | }}} |
508 | | |
509 | | Because of the highly nested SQL statement, some providers like ODBC and SQL Server may not be able to handle the complexity and issue an exception. |
510 | | |
511 | | The nesting in general is valid but not always - like in the above example - necessary. This will be addressed as part of this RFC and optimized. The process will be modified to nest sub filters if this is not required. For the above example, this will result in the following SQL statement: |
512 | | |
513 | | {{{ |
514 | | |
515 | | Filter: |
516 | | |
517 | | a = 1 or a = 2 or a = 3 or a = 4 |
518 | | |
519 | | Generated SQL statement: |
520 | | |
521 | | (a = 1) or (a = 2) or (a = 3) or (a = 4) |
522 | | |
523 | | }}} |
524 | | |
525 | | Once the nesting is eliminated the originally reported issue no longer appears. As a result there is no need for additional capabilities. |
526 | | |
527 | | |