Is there a more efficient way to do this SQL Select?

Question

I have this SQL query:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY CASE IMAGENAME
  WHEN 'IMG1' THEN 1
  WHEN 'IMG2' THEN 2
  WHEN 'IMG3' THEN 3
  WHEN 'IMG4' THEN 4
  WHEN 'IMG5' THEN 5
  WHEN 'IMG6' THEN 6
  ELSE 7
END

I cannot guarantee that the list of IMAGENAMEs will be in alphabetical order, hence the case statement, but I would prefer to sort in the DB rather than in code because I trust their sorting code better than mine :)

SQL server analyses that 78% of the execution time is spent sorting - can I reduce this?

It needs to be fairly vanilla SQL as we target SQL Server and Oracle.

Any tuning advice would be fantastic.

Answer 1

Could you strip the IMG (or non-numeric) part of the IMAGENAME to a new column when writing to the database, then sort on the new numeric-only column?

For example:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENO
END

where IMAGENO would contain 1, 2, 3, 4, 5, 6 for each row in your example, respectively.

Answer 2

You could try to sort by

CAST(SUBSTRING(IMAGENAME, 4, LEN(IMAGENAME) -3) as INTEGER)

This probably won't work in Oracle without some adjustment however, and also it may not be any faster than what you have

I think you might be better adding some sort of Rank column to the table and setting that when you write to it

Answer 3

SELECT *
FROM IMAGES
WHERE IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY IMAGENAME ASC

You don't need to strip the imagename; you're only selecting the few cases noted above, and order by can order string well enough. Also, the 'else' condition makes no sense, because it would never be used, since you stripped out all other options in the where clause.

note: this is based on the query you posted, if there are more conditions or possibilities, you should give them as well.

Answer 4

If all your images are named 'IMG' then you should be able to substring the name from char 4 and then convert it to a number. I don't know the SQL Server functions for this, but in Oracle it would be:

SELECT * FROM IMAGES WHERE
IMAGENAME in ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
ORDER BY TO_NUMBER(SUBSTR(IMAGENAME,4));

Answer 5

I should have said before that the strings I gave were dummy strings, the actual fields don't have numbers at the end unfortunately, but thanks for that advice.

I'll have a think about adding a numeric column to make sorting quicker.

Answer 6

I'm not sure if this is available in your target databases, but it might hopefully work at least as inspiration for a better approach. In MySQL, you would do what you want with the FIELD() function like this:

SELECT * FROM IMAGES WHERE
  IMAGENAME IN ('IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6')
  ORDER BY FIELD(IMAGENAME, 'IMG1', 'IMG2', 'IMG3', 'IMG4', 'IMG5', 'IMG6');

The FIELD() function takes the first parameter, and then returns an integer based on the position in the list of parameters, or zero if it does not exist in the parameter list. This enables you to create a completely custom ordering for your query.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

I would expect something similar might be available in SQL Server or Oracle.

Answer 7

There is an almost equivalent way to do what ChrisThomas123 suggested in SQL Server (but not ORACLE) which is to say:

ORDER BY CHARINDEX(IMAGENAME, 'A,B,C,D,E,F,G')

but it doesn't seem to be any quicker.

Answer 8

If you have the option of adding an index, then (on sql server) if you define a clustered index on IMAGENAME then the data will be pre-sorted. Even if the rows weren't sorted in the right order, they would at least be grouped by IMAGENAME so the sort should be quicker. If you add the numecic column put the clustered index on this. An alternative to adding a column would be a lookup table. But you may no have these options open

Answer 9

Create a new table that contains the relation

(IMAGENAME, SEQUENCENUMBER).

Load it up with ('IMG1', 1), ('IMG2', 2), ('IMG3', 3) ,... Join this table in your query, and order by SEQUENCENUMBER.

It should run a lot faster.