Odata $orderby DESC sp_prepexec appending primary key

4 posts, 1 answers
  1. RainerAtSpirit
    RainerAtSpirit avatar
    4 posts
    Member since:
    Jul 2009

    Posted 09 Apr 2012 Link to this post

    Hey,

    While following the example about KendoUI integration I'm running into an issue that each $orderby DESC query leads to very slow repsonse times from the server.
    Datasource is a simple 1.000.000 record people table with an non-clustered index on LastName. Initial return of an unsorted result set is fast, sorting it ASC is fast as well. Using profiler I can see that in addition to the a.[LastName] DESC an a.[PeopleId] is added.
    Comparing the two attached execution plans you can see that adding a.[PeopleId] to a DESC query makes the query inefficient.

    As I'm pretty new to OpenAccess ORM it's probably me doing something wrong in my setup. What would be the recommend way to fine tune the generated sp_prepexec expressions.

    Thanks,

    Rainer

  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 12 Apr 2012 Link to this post

    Hello Rainer,

     I am currently looking into this issue and get back to you tomorrow. 
     
    Regards,
    Serge
    the Telerik team
    Want to use Telerik OpenAccess with SQL Azure? Download the trial version today. 
  3. DevCraft banner
  4. RainerAtSpirit
    RainerAtSpirit avatar
    4 posts
    Member since:
    Jul 2009

    Posted 12 Apr 2012 Link to this post

    Thanks Serge,

    In the meantime I did a workaround at the db layer and added two indices (ASC and DESC) per column that needs to be sorted. While this gets the job done, it would be still great if there's an option to avoid the additional primary key in the OrderBy clause alltogether.

    Thanks,

    Rainer

    BTW: You can take a look at the work in progress at http://openaccess.spirit.de/
  5. Answer
    Serge
    Admin
    Serge avatar
    375 posts

    Posted 13 Apr 2012 Link to this post

    Hello Rainer,

     Unfortunately this seems to be wrong behaviour on our side, by append a sorting based on the primary key of the objects we enforce that the results you get are always in the same order. This is helpful in terms of multiple databases that can be different servers (for example Oracle and MSSQL), usually different backend return the results in different order so that's why we initially put the primary key ordering in place. 

    However, if explicit sorting has been specified (such as your case), we shouldn't have put in the primary key ordering on top of that. And that's something that we will fix. 

    I will suggest creating a single index on both the LastName and OrderId column, that should do the trick as well.

    I would like to apologize for any inconvenience caused. Please find your Telerik points updated for reporting this issue.

    Also the Proof of Concept you are working on looks really great. 

    Greetings,
    Serge
    the Telerik team
    Want to use Telerik OpenAccess with SQL Azure? Download the trial version today. 
Back to Top