1

I have a table with 50 million rows. It is indexed on zip. A query

Select * from table where zip = '12345'

returns 20,000 rows in about 2 seconds

When I add an additional condition it jumps to 15 minutes...

Select * from table where zip = '12345' and otherField = 'Y'

My expectation is SQL would use the index, identify the 20,000 rows and then scan all for that non indexed condition. Maybe a few seconds at max. Instead it seems to be scanning the whole database table.

I cannot add another index... There are 150+ columns we might test, most have Y/N values.

Is there a better way to write this type of query?

Could I have a setting wrong in the server config?

Thank you!

Improve this question
10
  • What are the indexes on the table? What's the table definition? Why are you doing SELECT * when it sounds like you have 150 columns on the table. Do you really need all 150 returned? Commented Apr 11, 2015 at 11:05
  • We don't need all. I've done queries selecting just 10 columns, same result. When select * with test index field only, it is just a few seconds. The issue is with the additional where clause condition that is not indexed. Commented Apr 11, 2015 at 11:26
  • The table definition is 200 columns, mostly varchar, with indexes on state, zip, last+firstname. There are around 150 YN or 1 char fields on each row. Commented Apr 11, 2015 at 11:29
  • Is your index just on Zip or are you using INCLUDE to include the columns returned? Sounds like SQL Server making a choice between a scan and a seek/lookup due to a poor index. Might be worth posing the execution plans for both a good and bad query. You also don't have to add another index, indexing on (zip,otherField) INCLUDE(myotherfields) would satisfy both queries. Commented Apr 11, 2015 at 11:33
  • 1
    Ok, no problem. I'd recommend reading up about covering indexes here simple-talk.com/sql/learn-sql-server/… Commented Apr 11, 2015 at 11:42

2 Answers 2

Reset to default
1

Is there a better way to write this type of query?

No, the way you've written it is the "best" way. Unless, of course, that way doesn't work. The joy and frustration of using a declarative language is the optimiser. It is your best friend when it works and worst enemy when it doesn't.

One way to kick the optimiser into doing the right thing is to re-write your query in a semantically identically way such as:

select <whatever> from table as t inner join ( Select <primary key columns> from table as b where b.zip='12345' ) as z on t.PrimaryKey = z.PrimaryKey -- repeat if multi-column key and t.otherField = 'Y';

Now the optimiser is starting from a different place and may, possibly end up with a different, better, answer. For simple single-table queries I fear it is unlikely to show much change, however.

I would only suggest you try this after you're absolutely sure you've done all you can with indexes. Re-writing queries in non-obvious ways adds maintenance overhead to the application. It may also prove counter-productive when you patch or upgrade SQL Server.

Improve this answer
1

You can try selecting into a temp table with the zip criteria only, and then select from the temp table with the additional criteria.

SELECT * INTO #MyTemp FROM (SELECT * FROM MyTable where zip='1234') data SELECT * from #MyTemp where other = 'Y' DROP Table #MyTemp

Not sure if this will be faster, but since the zip-only query runs fast, then you're just running a second query on 20,000 records. Whether this is OK or not may depend on how much data is in a row, how much memory the server has, how fast the disk is where tempdb is located, etc. Easy to try, anyway.

Improve this answer
1
  • 1
    I also like this temp table solution since you can dynamically create another index on "otherfield" since that can be any column from your base table. This way you avoid maintaining a huge index but it will be at the expense of relying more on tempdb. Choices, choices... Commented Apr 13, 2015 at 18:33

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.