Curious performance issue when running a query

[Paul Mateer]

Hi,

I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.

The table has approximately 5 million rows and includes the following
columns:

DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)

If I issue the following query:

SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'

it takes about 23 seconds to return the 481 hits.

The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.

The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:

SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'

it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.

Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:

SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'

it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.

What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?

All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/

Thanks

Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,

[Uri Dimant]

Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.

"Paul Mateer" <p.******@meridio.com> wrote in message
news:42*************************@posting.google.co m...

Hi,

I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.

The table has approximately 5 million rows and includes the following
columns:

DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)

If I issue the following query:

SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'

it takes about 23 seconds to return the 481 hits.

The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.

The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:

SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'

it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.

Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:

SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'

it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.

What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?

All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/

Thanks

Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,

[Paul Mateer]

Hi Uri. Thanks for the reply. I tried your suggestion of a covering
index and it worked.

I have a new problem however. In order to keep things simple the queries
that I posted were simplified versions of the actual query that I need
to execute. I cannot create a covering index for all of the required
columns because SQL 2000 limits the key length to 900 bytes and the
total length of all the column data involved in the query is over 1000
bytes in length.

Any suggestions on how to resolve this problem would be greatly
appreciated.

Paul Mateer
Meridio Limited
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Gert-Jan Strik]

The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.

For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.

I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.

Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:

SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'

Hope this helps,
Gert-Jan
Paul Mateer wrote:

Hi,

I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.

The table has approximately 5 million rows and includes the following
columns:

DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)

If I issue the following query:

SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'

it takes about 23 seconds to return the 481 hits.

The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.

The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.

If I issue the following query:

SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'

it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.

Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.

All this is fine, however when I issue the following query:

SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'

it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.

What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?

All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/

Thanks

Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,