optimizing search statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

optimizing search statement

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-03-20 : 06:48:11

Hi all,

I got this search statement, but i want to optimize it

SELECT DISTINCT V_Product.id, V_Product.Image, V_Product.Name, V_Product_URL_Complete.url
FROM V_Product INNER JOIN
dbo.T_Product_Description ON dbo.V_product.id = dbo.T_Product_Description.ProductID INNER JOIN
V_Product_URL_Complete ON V_Product.id = V_Product_URL_Complete.id
WHERE (V_Product.Lang = @lang)
AND (V_Product_URL_Complete.Lang = @lang)
AND (V_Product_URL_Complete.lang2 = @lang)
AND (NOT (V_Product.id IN
(SELECT ProductID
FROM T_Product_Hide
WHERE Country = @country)))
AND ((dbo.T_Product_Description.Name LIKE '%' + @search + '%') or
(dbo.T_Product_Description.Description LIKE '%' + @search + '%'))
ORDER BY V_Product.Name

thanks a lot

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-03-20 : 07:05:47

What do you understand by optimise? Do you know how to look at execution plans? Do you know how to read execution plans and understand what they are telling you?

If not, you may want to read up on them and if you are still stuck with the performance, then come back and explain where and with what.

http://www.simple-talk.com/sql/performance/execution-plan-basics/
https://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/
http://www.simple-talk.com/sql/performance/understanding-more-complex-query-plans/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-20 : 13:38:02

If possible try transforming the highlighted text into a join instead of the "NOT IN"

SELECT DISTINCT V_Product.id, V_Product.Image, V_Product.Name, V_Product_URL_Complete.url
FROM V_Product INNER JOIN
dbo.T_Product_Description ON dbo.V_product.id = dbo.T_Product_Description.ProductID INNER JOIN
V_Product_URL_Complete ON V_Product.id = V_Product_URL_Complete.id
WHERE (V_Product.Lang = @lang)
AND (V_Product_URL_Complete.Lang = @lang)
AND (V_Product_URL_Complete.lang2 = @lang)
AND (NOT (V_Product.id IN
(SELECT ProductID
FROM T_Product_Hide
WHERE Country = @country)))
AND ((dbo.T_Product_Description.Name LIKE '%' + @search + '%') or
(dbo.T_Product_Description.Description LIKE '%' + @search + '%'))
ORDER BY V_Product.Name

Still, if slow, then use execution plan in order to identify the problematic areas.

Cheers
MIK

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-03-21 : 05:10:08

So turn it into a left join on id = id and country = @country, then add to your where a is null check on the id from t_Product_Hide. See if that is any faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources