5

I have 3 indexed fields in a query: int, int, and varchar(250).

The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty of empty string varchar values. Queries with the empty string varchar parameter perform 2-3x slower than those that search a real string (e.g. 'hello'). The query against the varchar column is a straight WHERE clause (i.e. no LIKE, just =).

I've searched around a bit but really only seem to see academic type discussions around this and I, frankly, don't really care about how they can mean different things. I only care about the performance of the queries against a NULL or empty string varchar column.

Is this empty string the cause of the slowness? Would a NULL in it's place improve things? I can easily turn existing empty strings into NULLs and put some new logic in to make sure empty strings are always put in as NULLs. I just figured I'd ask here to get the expert opinion on this.

I'll be toying around with this anyway but it'd be nice to get an outside view telling me if I'm just spinning my wheels on it, if that's the case.

Improve this question
3
  • 4
    Can you post the execution plans?
    – mrdenny
    Commented Oct 25, 2012 at 22:21
  • What is the distribution of values on that varchar column? Is empty string is, for example, a third of the total values on the column? More? Less? We need more information...
    – Fabricio Araujo
    Commented Oct 30, 2012 at 17:33
  • @FabricioAraujo The blank string varchar's do make up about about 25% of the about 125k records in the table. Would that be the reason why? I ended up creating a join table filled with distinct values from that varchar (i.e. only one blank varchar) which are assigned to a key which are then assigned back to the original table. That seems to have improved performance for those queries greatly. I'd still love to hear thoughts. Sorry for the lack of upfront information.
    – Kevin
    Commented Oct 30, 2012 at 21:41

1 Answer 1

Reset to default
3

Using your info about the distribution of values (and some guessing), I would say that your problem is that MSSQL is doing an table scan when receiving an WHERE clause for empty string.

Why? Suposing that your index on varchar column does not cover the query (in other words, include all the columns referenced in the query); MSSQL have to do an seek to get the PK's columns (if it is clustered) - or record-id (if not) - and using that in a bookmark lookup to get all the columns it need.

Where comes the table (or clustered index) scan? Simple: based on index statistics, if the number of pages touched to get that data is over than 25%-33% of the total pages of the table, MSSQL considers that is too much expensive to use the index and goes the table (or clustered index) scan way.

This is discussed in deeper details on this dba.SE question.

As to solve the problem, you can:

  • create (or modify an existing index to be) an covering index to your query
  • Reduce, if possible, the columns on the query - so it become covered by an existing index
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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