query performance from good to unacceptable

Hi,
We have a website on which you can sell and buy products. You can post
ads for free and you can search on a variety of parameters as ...
- price range (eg between 50 and 100 EURO)
- region (eg 10, 15, 20 mile from a certain city or town, ...)
- a piece of text (eg bike)
- only ads with uploaded images
- main category (eg hobby)
- subcategory (eg sports, culture, music, instruments, ...)
- etc.
Since the user has a lot of options when start searching we've chosen to
use a stored procedure in which the query is builded on the fly and at
the end it's executed with EXEC sp_executesql @.sSQL.
The following query is an example how it looks like once it's builded ...
SELECT TOP 1000 x_adid, x_boonline, x_datetime,
replace(replace(rtrim(fx_title), ' ...', '') + ', ' + left(fx_text,
50) + ' ...', ',,', ',') as titel, fx_text, x_photo, x_region,
x_count, cast(x_price as decimal(14, 2)) as x_price,
x_video, (select ORDescription from OR_lookup where hr_id = x_hr
and or_id = x_or) as x_ORDesc, x_producttype_web as x_CSS
FROM x JOIN xFULL ON x_adid = fx_adid
WHERE (x_ADID IN (SELECT Fx_ADID FROM xFULL WHERE (CONTAINS(*,
'antwerpen') ) ) )
AND (x_HR = 2)
AND (x_OR in (7)) (**1)
AND (x_HR <> 10)
AND (x_PRICE <= 400)
AND(x_STAT < 10)
AND NOT (x_OVER = 'J')
AND NOT (x_USRID = 896436)
ORDER BY x_adid DESC
Our database contains about 150.000 ads and the search engines mostly
works all right (average response times < 500 ms).
Now since some days we have dedected that in some cases execution slows
down to more than a minute when you select certain subcategories (x_OR)
of ceratin main categories (x_HR). Mainly this happens when there are a
lot of matches inside the subcategory or subcategories.
So when condition **1 is not there (because the user didn't select a
subcategory) the system works fine, but when he does, performance is lost.
I think I've set my indexes well. I have an index on main category x_HR
and subcategory x_OR togheter, not seperatly because you can not search
on subcategory without selecting a main category.
I use x_OR in (7, 8, 9 ...) since the user can select multiple
subcategories in one search. Might this affect the use of the index?
I've enabled execution plan, trace and statistics in the query analyzer
but unfortunatly I've not enough know how to understand all messages
(actually I'm a VB programmer using SQL Server). So I cannot find why
the query runs well without **1 and poor with condition **1.
I'd like to find some days or ws to learn to understand this kind of
issues (traces, execution plans, ...) and how to solve, but I've no time
for the moment.
What can I do? Have you any suggestions (besides hiring a specialist to
finetune the system :-)
Thanks,
Peter van Wilrijk>the query runs well without **1 and poor with condition **1.
This is because
Like '%blauhblauh' --is not SARGable while and uses table scan
Like 'blaughblaugh%'is SARGable - and uses indexes
again
IN does not use indexes, better use between and if possible.
Post you query to know exactly.
--
Regards
R.D
--Knowledge gets doubled when shared
"Peter Van Wilrijk" wrote:

> Hi,
> We have a website on which you can sell and buy products. You can post
> ads for free and you can search on a variety of parameters as ...
> - price range (eg between 50 and 100 EURO)
> - region (eg 10, 15, 20 mile from a certain city or town, ...)
> - a piece of text (eg bike)
> - only ads with uploaded images
> - main category (eg hobby)
> - subcategory (eg sports, culture, music, instruments, ...)
> - etc.
> Since the user has a lot of options when start searching we've chosen to
> use a stored procedure in which the query is builded on the fly and at
> the end it's executed with EXEC sp_executesql @.sSQL.
> The following query is an example how it looks like once it's builded ...
> SELECT TOP 1000 x_adid, x_boonline, x_datetime,
> replace(replace(rtrim(fx_title), ' ...', '') + ', ' + left(fx_text,
> 50) + ' ...', ',,', ',') as titel, fx_text, x_photo, x_region,
> x_count, cast(x_price as decimal(14, 2)) as x_price,
> x_video, (select ORDescription from OR_lookup where hr_id = x_hr
> and or_id = x_or) as x_ORDesc, x_producttype_web as x_CSS
> FROM x JOIN xFULL ON x_adid = fx_adid
> WHERE (x_ADID IN (SELECT Fx_ADID FROM xFULL WHERE (CONTAINS(*,
> 'antwerpen') ) ) )
> AND (x_HR = 2)
> AND (x_OR in (7)) (**1)
> AND (x_HR <> 10)
> AND (x_PRICE <= 400)
> AND(x_STAT < 10)
> AND NOT (x_OVER = 'J')
> AND NOT (x_USRID = 896436)
> ORDER BY x_adid DESC
> Our database contains about 150.000 ads and the search engines mostly
> works all right (average response times < 500 ms).
> Now since some days we have dedected that in some cases execution slows
> down to more than a minute when you select certain subcategories (x_OR)
> of ceratin main categories (x_HR). Mainly this happens when there are a
> lot of matches inside the subcategory or subcategories.
> So when condition **1 is not there (because the user didn't select a
> subcategory) the system works fine, but when he does, performance is lost.
> I think I've set my indexes well. I have an index on main category x_HR
> and subcategory x_OR togheter, not seperatly because you can not search
> on subcategory without selecting a main category.
> I use x_OR in (7, 8, 9 ...) since the user can select multiple
> subcategories in one search. Might this affect the use of the index?
> I've enabled execution plan, trace and statistics in the query analyzer
> but unfortunatly I've not enough know how to understand all messages
> (actually I'm a VB programmer using SQL Server). So I cannot find why
> the query runs well without **1 and poor with condition **1.
> I'd like to find some days or ws to learn to understand this kind of
> issues (traces, execution plans, ...) and how to solve, but I've no time
> for the moment.
> What can I do? Have you any suggestions (besides hiring a specialist to
> finetune the system :-)
> Thanks,
> Peter van Wilrijk
>|||examnotes (RD@.discussions.microsoft.com) writes:
> This is because
> Like '%blauhblauh' --is not SARGable while and uses table scan
> Like 'blaughblaugh%'is SARGable - and uses indexes
> again
There was not even a LIKE in the query as far as I could see.

> IN does not use indexes, better use between and if possible.
Huh? "x IN (1, 2, 3)" is just a shortcut for "x = 1 OR x = 2 OR x = 3"
and will use indexes, if they are suitable for the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Peter Van Wilrijk (pro@.koopjeskrant.be) writes:
> The following query is an example how it looks like once it's builded ...
> SELECT TOP 1000 x_adid, x_boonline, x_datetime,
> replace(replace(rtrim(fx_title), ' ...', '') + ', ' + left(fx_text,
> 50) + ' ...', ',,', ',') as titel, fx_text, x_photo, x_region,
> x_count, cast(x_price as decimal(14, 2)) as x_price,
> x_video, (select ORDescription from OR_lookup where hr_id = x_hr
> and or_id = x_or) as x_ORDesc, x_producttype_web as x_CSS
> FROM x JOIN xFULL ON x_adid = fx_adid
> WHERE (x_ADID IN (SELECT Fx_ADID FROM xFULL WHERE (CONTAINS(*,
> 'antwerpen') ) ) )
> AND (x_HR = 2)
> AND (x_OR in (7)) (**1)
> AND (x_HR <> 10)
> AND (x_PRICE <= 400)
> AND(x_STAT < 10)
> AND NOT (x_OVER = 'J')
> AND NOT (x_USRID = 896436)
> ORDER BY x_adid DESC
> Our database contains about 150.000 ads and the search engines mostly
> works all right (average response times < 500 ms).
>...
> So when condition **1 is not there (because the user didn't select a
> subcategory) the system works fine, but when he does, performance is lost.
Without knowledge of the table and indexes, it's quite difficult to say.
The advice I could give right now is to run
UPDATE STATISTICS x WITH FULLSCAN
Also check for fragmentation with DBCC SHOWCONTIG, on the table, and
on the indexes.
Maybe we can provide more input if you post:
o CREATE TABLE statements for the tables.
o CREATE INDEX statements for the tables.
o The output when you have done SET STATISTICS PROFILE ON for a
query with and without the condition slows down the query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp