• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 865
  • Last Modified:

rowcount + order by w/o the wait ?!?!

Hello all,

Using Sybase 12. Is there a cleaver way to "order by" without retrieving a whole result set?

Please take a look at the simplified example below:

  set rowcount @my_count
  select my_value
  from   my_table
  order  by my_value desc


Now, is there a way to get Sybase to just return @my_count number of rows, using the order by, without having to retrieve the whole result set first, then ordering, then returning the @my_count rows?

Obviously, there is no problem if the data set is small, but given a large data set, then having to order it, can be taxing.

A range (where my_value between @low and @high) is not possible as the ranges aren't known at query time.

A non-unique, clustered index (my_value) exists on the table.

A loop or cursor to get the max(my_value) where less then the last max(my_value) @my_count number of times is not an option either. (Although, if someone can prove that it is the most efficient way for any @my_count and table size, then I'm all for it! :)


Any help would be appreciated.

Thanks,
  -Dave

0
Obfuscate
Asked:
Obfuscate
1 Solution
 
bretCommented:
You might try forcing the use of the index in the select

set rowcount @my_count
select my_value
from   my_table (index index_name)
order  by my_value desc


Do you have the sp_configure option "allow backward scans" turned on?

Alternatively, might help if the index was created in descending order.

-bret
0
 
ObfuscateAuthor Commented:
Bret,

As far as indexing options go, we have two options:
1)     create a clustered index on the my_value column(s) with or without the desc clause.
2)     create a covered non-clustered index on my_value with or without desc

It has been suggested that the non-clustered index will theoretically provide better performance in this case. Assuming that the index is created in the same order as the order by (my_value > 1 column).

Was hoping for a more SQL based optimization. If indexing is the only option, so be it.

If anyone comes up with anything, it'd be much appreciated.

Thanks,
 -Dave
0
 
ChrisKingCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: bret

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ChrisKing
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now