Optimizing query

[tshad]

I have a query where the execution plan is showing a table scan with 32%
cost.

I have tried to put indexes on pretty much all the fields and still get the
same result.

The predicate from the Query Analysers execution plan for this table is:

[dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND
([@NameChoice] IS NULL OR CASE WHEN
isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN
([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE
CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')=''
THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END
like [@NameChoice]+'%')

And the query is:

SELECT RID, EnvID, RType, Type, FullType, RName, EmailAddress,
MemberMissing
FROM (
SELECT
ReviewerID as RID
,EnvironmentID as EnvID
,@reviewerType as RType
,'R' as Type
,'Reviewer' as FullType
,Case
When isnull(FirstName, '') <> '' then LastName + ', ' + FirstName
When isnull(FirstName, '') = '' then LastName
End as RName
,EmailAddress
,'' as MemberMissing
FROM REVIEWER
WHERE EnvironmentID = @EnvironmentID AND
(@TypeChoice IS NULL OR @TypeChoice = @reviewerType)
) as a
Where (@NameChoice IS NULL OR (RName LIKE @NameChoice + '%'))

I have indexes on:

EnvironmentID
ReviewerID
EnvironmentID + ReviewerID
LastName
FirstName

There is no Primary Key - and no clustered index. The ReviewerID is unique
(and really should be the Primary Key) but it isn't.

Would it be better to make the indexes unique by changing some of the keys
to something like:

LastName + ReviewerID

This would make the index a little bigger and not sure if it would help the
searches or not by much.

But why isn't it using any of the indexes?

Thanks,

Tom

[Eric Isaacs]

I'm guessing that the time is being consumed with the string parsing
that you're doing for each row that is being processed. Instead of
using the case statement in your where clause, parse out the
@NameChoice into @LastName and @FirstName before the statement by
splitting it at the comma, then do the like comparisons against the
last name and the first name separately in your where clause, which
will take advantage of the indexes on those columns. When you combine
them, the indexes on those columns are ignored.

Having indexes on multiple fields isn't helping you either as the
optimizer will only select one index to use. You're better off having
an index on LastName, FirstName (with both fields in the same index)
than one on lastname and another on firstname. Since it looks like
EnvironmentID is required by your WHERE clause, you could do an index
on EnvironmentID, LastName, FirstName for more speed. Maybe even
throw ReviewID in there either before the firstname or after it,
depending on which is more likely to be included in the criteria.
That should help.

I think the most important thing to do is to parse out the @NameChoice
into @LastName and @FirstName before your query though. It will be
only one parse as opposed to one parse per row in your table.

-Eric Isaacs

[Erland Sommarskog]

As the query is written, there is not much more you can do about it.
Only the index on EnvironmentID is really useful - and only if it's
selective enough.

If you want better speed when you have a NameChoice, you would need
to persist the CASE expression and index it. Furthermore you would
need an IF statement to selevt between two different SELECT depending
on input.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Sylvain Lafontaine]

With a 32% of the total cost, there is probably nothing that you can do
about it and part of it is because it's not worth it. As a rule of thumb,
whenever the optimizer determines that a query will return 10% or more of
the total number of row of a table, it will choose to do a table scan
because the alternative will cost more in term of I/O and CPU: looking at a
table heap through an index is a costly operation and it's simply not worth
it to do it instead of making of a full table scan if the number of rows to
be looked at is to great.

The rule is slightly different for a clustered index but the basic principle
is the same but in this case, the execution plan will indicate a table scan
even if it's only a partial table scan. However, you did explicitely
mention in your post that there were no clustered index on your table.

In this case, the execution plan shows that the table scan is taking 32% of
the total costs. Without knowing the total numbers of rows retrieved and in
the table, it's impossible to say but it's quite possible that a table scan
is used here simply because the alternative is not worth it because either
the table is too small or the percentage of retrieved rows to great.

You have EnvironId and (EnvironId + ReviewerID) as two indexes; one of these
two is probably useless. The case of the index on the single field ReviewID
is different because ReviewerID comes in second for the composite index
(EnvironId + ReviewerID).

If you're really desperate for using an index, one possibility might be to
try with a index on (EnvironId, LastName, FirstName). This way, SQL-Server
can determine is a row will be part of the result by looking exclusively at
the index without touching the table heap. However, at 32% of the total
cost for the table scan, I won't be surprised if even this changes nothing.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"tshad" <t...@dslextreme.com> wrote in message
news:%23SNXku7...@TK2MSFTNGP05.phx.gbl...

[Dave Ballantyne]

Try this article by Gail Shaw

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/