Improving performance of query with `WHERE <> ''`

Question

I have a qeuery that checks where two columns are not '' (blank) - [blank and NULL are not the same in this case]

Is there any way to improve the performance of the query

SELECT *
FROM myTable
WHERE Currency <> ''
AND Country <> ''

From what I understand, <> is causing a complete table scan?

Answer 1

Do those columns have indexes on them at all? That is rather vital information for questions like this. If there is no index covering either of those columns at all then there is nothing it can do except table scan to match those filters.

If there is at least one index covering one (or both) that it could use, it may be deciding against it anyway due to the SELECT *. If the selectivity of the index suggests to it that many rows will match then a table scan may actually be more efficient than an index seek/scan followed plus row lookups to get the rest of the data that is not contained within the index.

For instance if I run

SELECT parent_id FROM org WHERE parent_id <> ''

on one of our systems it performs an index seek only. Running

SELECT * FROM org WHERE parent_id <> ''

performs a table scan instead. If I force the index choice with

SELECT * FROM org (WITH(INDEX(ix_org_parent_id) WHERE parent_id <> ''

it performs an index seek to locate the start of the records, then the RID lookups to find all the other column data. According to the query plan output it judges this to be ~6 times as costly as just doing the table scan, the estimated costs of the three queries above being 0.22926, 1.81429, and 11.1612 respectively. In this case the number of rows matching the WHERE clause is ~55% of ~50,000.

Answer 2

I assume you're using SQL Server (T-SQL reference there).

When you execute the T-SQL you can choose to include the actual execution plan, this will allow to see how SQL went about its execution, including whether a scan took place. Within this execution plan, SQL may even give hints on how to improve the performance which you may or may not decide to take up.

http://technet.microsoft.com/en-us/library/ms189562.aspx http://sqlserverplanet.com/optimization/execution-plans

Hope this helps!

Answer 3

Instead of using 'not equal' you could use 'greater than'.

select
    t.c
from
(
    values (''), ('a'), ('b'), ('c')
) as t(c)
where c > '';

Without the predicate four rows are returned; with, three. This is the desired outcome.

This assumes a Western-ish collation. Otherwise have a test to see what is returned.

Of course the optimizer may still choose not to use the index depending on many criteria.