Why does my query take 2 minutes to run?

Note - There are about 2-3 million records in the db.

SELECT     
   route_date, stop_exception_code, unique_id_no, 
   customer_reference, stop_name, stop_comment,
   branch_id, customer_no, stop_expected_pieces, 
    datetime_updated, updated_by, route_code
FROM
   cops_reporting.distribution_stop_information distribution_stop_information
WHERE     
   (stop_exception_code <>开发者_StackOverflow 'null') AND 
   (datetime_updated >= { ts '2011-01-25 00:00:01' })
ORDER BY datetime_updated DESC

If you posted the indexes you already have on this table, or maybe a query execution plan, it would be easier to know. As it is, I'm going to guess that you could improve performance if you create a combined index that contains stop_exception_code and datetime_updated. And I can't promise this will actually work, but it might be worth a shot. I can't say much more than that without any other information...

Some rules of thumb:

• Index on columns that JOIN.
• Index on columns used in WHERE clauses.
• 'Not equals' is always slower than an 'Equals' condition. Consider splitting the table into those that are null and those that are not, or hiving it off into a joined table as a index.
• Using proper JOIN syntax i.e. being explicit about where joins are by writing INNER JOIN speeds things up on some databases (I've seen a 10min+ query get down to 30 secs on mysql just by this change alone)
• use aliases for each table and prefix to each column
• store as a function/procedure and it will precompile and get much quicker

stop_exception_code <> 'null'

Please tell me that 'null' isn't a string in your database. Standard SQL would be

stop_exception_code IS NOT NULL

or

stop_exception_code is NULL

I'm not sure what a NULL stop_exception_code might mean to you. But if it means something like "I don't know", then using a specific value for "I don't know" might let your server use an index on that column, and index that it might not be able to use for NULL. (Or maybe you've already done that by using the string 'null'.)

Without seeing your DDL, actual query, and execution plan, that's about all I can tell you.