Lynn Pettis (6/12/2013)
What is also missing is the DDL for the tables you are using along with the current indexes defined on those tables.
Part of the problem you are having may also be attributed to poor indexing of the tables.
Part of the requirement here is that records be presented in a default sort order of date desc, then docid asc. Additionally, the user can specify an arbitrary third sort column, which can be asc or desc. Indexing the primary table with the default sort in mind results in reasonable performance (not great, but acceptable). However, allowing any one of another 65 columns to be specified in the sort makes the server work overtime trying to achieve this. A less than ideal solution to this has been to add suitable indexes to specifically handle the sort orders that might be specified (where the index would be restrictive enough to be used), but obviously this has resulted in more space being consumed in indexes than there is actual data in the table. However, it makes the queries perform at an acceptable level again, so has to remain.
I'm sure a suitably normalised table structure with appropriate indexes would outperform this monstrosity hands down, but it's not an option in the short term