Odata $orderby DESC sp_prepexec appending primary key

[RainerAtSpirit]

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

[Serge]

Hello Rainer,

 I am currently looking into this issue and get back to you tomorrow. 
 
Regards,
Serge
the Telerik team

[RainerAtSpirit]

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/

[Serge]

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