Inefficient query with conditional expressions

6 posts, 0 answers
  1. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 24 Mar 2014 Link to this post

    When using conditional expressions with the following pattern
    ... cond1 ? val1 : cond2 ? val2 : cond3 ? val3 : ...
    or with expression syntax:
    ... Expression.Condition(cond1, val1,
          Expression.Condition(cond2, val2,
            Expression.Condition(cond3, val3 ...
            )
          )
        ) ...
    this generates something like the following:
    ... CASE WHEN cond1 THEN val1 ELSE (CASE WHEN cond2 THEN val2 ELSE (CASE WHEN cond3 THEN val3 ... END) END) END) ...
    There is a T-SQL limit that CASE statements can only be nested 10 times that is very easy to hit by this pattern. However the nested statements can be collapsed into a single statement:
    ... CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 WHEN cond3 THEN val3 ... END ...
    which no longer triggers this limit. Entity Framework actually does this.
    A possible workaround is to use:
    ... (cond1 ? val1 : null) ?? (cond2 ? val2 : null) ?? (cond3 ? val3 : null) ...
    this bloats the query. Another possible workaround is to use ExtensionMethods.SQL which is not very linqish and error-prone.
  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 27 Mar 2014 Link to this post

    Hi Greg,

    I am sorry for the inconvenience you have experienced.
    Can you define your use case scenario where you need more than 10 chained conditional operators?
    Are you using these in your WHERE filter or in SELECT clause?

    We will need this information in order to have better context when analyzing and evaluating this limitation and the way we can overcome it in our product.

    I will make sure to contact you back when we have any status update on the described issue.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 31 Mar 2014 in reply to Viktor Zhivkov Link to this post

    Hi!

    I have an arbitrary (but very small) number of status codes and the localized strings for these are in resx dictionaries. I need server-side paging of records ordered by the localized status name where the culture is determined by the client request. This is implemented by pre-sorting the status codes and substituting them with the index in the sorted array. E.g. case status when 'code1' then 0 when 'code2' then 1 ... end. So to answer your question I'm using these in the OrderBy clause.
    This has been working nice for quite a long time up to the point where I actually used it for a status code that has more than 10 possible values. I also use hierarchical codes e.g. 3 status types with 4 subcodes each (for a total of 12 cases) that would use a single level of nesting. I cannot devise a case when more than 10 levels of nesting is a must and even if I could these can be collapsed into fewer levels with a trade-off of increased variable count and a longer command text.
  5. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 31 Mar 2014 in reply to Viktor Zhivkov Link to this post

    I'm using it in the ORDER BY clause. It's basically a mapping of status codes to indexes where the index is the position of the specific status code when sorted by it's localized string representation. This is implemented by chained conditionals e.g. c.status == 6 ? 0 : c.status = 2 ? 1 : c.status=5 ? 2 : ... These codes have a very limited set of possible values (a handful at most), but that is still enough to trigger this problem. I also use composite status codes e.g. 3 status types with 4 substatus codes each (a total of 12 cases), which would require a single level of nesting. I cannot devise a scenario where more than a couple of levels are needed, but even if there are it could also be written as a flat case at the expense of additional query variables and longer command text. That is if the current OA implementation would not generate one additional nesting level per case.
  6. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 31 Mar 2014 in reply to Greg Link to this post

    Sorry, double post, the first returned a 500 error. Please remove the second.
  7. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 03 Apr 2014 Link to this post

    Hello Greg,

    Based on your description I can see three possible ways to work around the issue:
    1. Implement SQL Function that calculates the status and define a mapped C# function that can be used in LINQ
    2. Postpone the status calculation to be executed in-memory if it is possible. If you have to load some localized strings based on these values you may be able to utilize Level 2 Caching in order to limit the number of queries required.
    3. Use as you have suggested "[raw sql statements]".SQL<int>() method to define a "SQL island" inside your query.
    If you are unable to implement any of these suggestions or have any questions, please let us know.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner