Sorting, what would you do?
-
I have a situation where I have various selection criteria for a select query and I want to return the query sorted on any one of five columns. These will always be in ascending order.
The actual query always returns the same columns.
I am toying with a number of alternatives.
Alternative One
Create a temporary table to receive records from one of many select queries.
Select from my temporary table in the order I want.
Alternative Two
Build a dynamic query string and use sp_executesql.
Alternative Three
As per one but without the temporary table thereby building a long winded stored proc with every combination of selects and ORDER BYs.
The emphasis has to be on speed of execution.
In all honesty I'm leaning to a mix of Alternative Two for the complex stuff and Alternative Three for the simpler ones.
You opinions would be greatly appreciated.
-
We generally (sometimes under protest by all) go through the pain of writing specific statements for each option when:
a) Performance is the highest priority
b) There is a limited number of options
c) The amount of data is fairly small compared to the number of times the process is called. (If the query only returns 100 rows but is called 10,000 / day write a specific sp. If the routine is called 100 times but returns 10,000 rows, dynamic or middle tier creation of the SQL works fine for us.)
You may also find that a separate SP for each option performs better than a single SP with all variations handled through the ELSE clause.
my $0.02
Guarddata-
-
I think guarddata has this wrapped up. Nevertheless, I'll make a brief case for option number 1, which as I understand it is:
1) Do the selection query and insert results into a temp table (or table object if SQL 2000)
2) Select * from the temp table with a dynamic order by clause (based on an input parameter to the query)
I think the main benefit is in making the sp easier to develop/debug/modify. However, this approach might lend itself well to reuse, depending on what your client is/how you are building it.
I take your point that speed of execution is paramount. However, particularly if you are on SQL 2000 and can use a table variable instead of a temp table, you might find that the cost was not significant.
If the fields you are returning are not indexed in their original tables, you *might* even get a cheap fringe benefit by putting an index in your temp table/table object on the field you are going to order by -- I'm far out on a limb here -- feel free to shoot me down people.
-
Thanks for that both of you.
The other trade-off I have to consider is that I will be handing over the database and stored procedure list to other developers so that they effectively call my stored procedures as if it were an interface. This is the reason that I am trying to produce a limited number of stored procedures rather than a large number of bespoke procedures.
I wish it was possible to specify parameters in the ORDER BY clause, even if it only affected ASC or DESC.
-
You can always make the sort column the first column in the select statement, and then use a case statement to determine the actual column e.g.
declare @order int
set @order = 1
select case @order
when 1 then convert(varchar(10),id)
when 2 then name
end as sortcol,
id,name from sysobjects order by 1
Note. You have to convert the non-character values to character if your columns can be a mixture of char and non-character, otherwise you get an error as SQL Server triesto convert the character data to int etc.
Similarly, you can also use the case statement within the order by clause e.g.
declare @order int
set @order = 1
select id,name from sysobjects order by
case @order
when 1 then id
when 2 then name
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply