0

I got a table like with 10 000 lines.

declare @a table
(
  id bigint not null,
  nm varchar(100) not null,
  filter bigint
  primary key (id)
)

A select, with 4-5 join, is taking x seconds. If a where clause is added, it's now taking 3x seconds. The where clause:

filter = @filder or
filter is null

I applied a nonclustered index on the column, but I'm getting only 10% on perfomance.

Any tips?

edit: the perfomance issue happens when the filter column is added. all joins are on primary keys.

Improve this question
8
  • 2
    Are the columns used for joins part of any index? Commented Feb 13, 2012 at 17:06
  • 1
    What fields are you selecting? If you have 4-5 joins, are those keys indexed on BOTH SIDES of the join? Commented Feb 13, 2012 at 17:06
  • 2
    How does your performance look like when you do a SELECT filter FROM @a WHERE ........ ?? Should you be doing a SELECT * and thus having an index will most likely not help at all since the query optimizer will still scan the whole table (since you're asking for all the columns in your SELECT) .... Commented Feb 13, 2012 at 17:10
  • 1
    just a tip, you can replace '@filter or filter is null' with isnull(@filder, filter) Commented Feb 13, 2012 at 17:10
  • Try by dropping and recreating the Index. Commented Feb 13, 2012 at 17:26

2 Answers 2

Reset to default
1

I have a few thoughts on this:

  1. Chances are that your joins are joining on table.id - which is a primary key and has an index - bingo - high selectivity (because the values are unique). With it being indexed the optimizer can really optimize access to this table when it is used in joins.

  2. I'm not 100% sure but - either you do not have an index on filter or it is not selective enough. If you do not have an index - the optimizer will use a table scan. If you do have an index, but it is not selective enough, it will use a table scan anyways. Scans are expensive.

  3. Even if you do have an index on filter The optimizer does not like OR predicates. Basically when using an OR the optimizer might end up using an index scan instead of an index seek. Try using this instead: @filder = ISNULL(Filter, @filder as @sut13 suggested.

So to improve the performance: add an index on filter if you do not have one and adjust your where clause to not use OR as I have suggested.

Also:

You shouldn't expect the query with the where filter to perform equal to or better than the query with 4-5 joins. If the query with the joins is more selective and makes better use of indexes it is going to perform better

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

It's likely that the lack of index (based on what you described on the structure) on the filter column is leading to a table scan. The only way to be sure is to take a look at the execution plan for the query. That will tell you what the optimizer is doing with the query and usually give you enough information so you can understand why it's doing that and what you need to do to fix it.

Probably, you need an index on the filter column. But, with the 'OR filter IS NULL' might lead to a scan anyway, depending on how many null values are in the data.

If you use the ISNULL as outlined, unfortunately, that's a function on the column and will probably (depending on the indexes used and other columns in the WHERE clause that can be used to initially filter the data, etc.) result in a scan and not a seek.

Improve this answer

Comments

Your Answer

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