Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
What percent of the rows will match the condition? (Currency <> '' AND Country <> '') –  ypercube Sep 4 '13 at 11:16
    
About 33% roughly. There are also queries on Currency = '' AND Country <> '' etc. etc.. And also Currency = '' AND Country = ''...which don't seem to be an issue - they run much quicker. –  BlueChippy Sep 4 '13 at 11:29
1  
I suggest you at least provide the table definition (CREATE TABLE). Partial indexes may help here. And add a tag with the DBMS you use (SQL-Server I suppose) and the version (2008, 2008R2, 2012, etc.) –  ypercube Sep 4 '13 at 11:49
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Followed your examples (table has covering indexes) and found the same as you. Table is approx 750,000 rows and found that you are right...the table scan was actually faster - so it seems that this is the most efficient way (and now I know why too!) –  BlueChippy Sep 5 '13 at 5:15
add comment

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!

share|improve this answer
    
I ran Ctrl-L/Estimated plan and it didn't suggest anything...trying Actual Plan now...same as Ctrl-L...just seems to be slow –  BlueChippy Sep 4 '13 at 11:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.