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

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.

Answers


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.


Need Your Help

Testing an asynchronous WCF service using MSTest

c# .net wcf async-await mstest

I've implemented a WCF service using the async\await method in C#. The service operation contract is as follows:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.