SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 optimizing search statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
211 Posts

Posted - 03/20/2013 :  06:48:11  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 03/20/2013 :  07:05:47  Show Profile  Reply with Quote
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/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/20/2013 :  13:38:02  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 03/20/2013 13:38:34
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 03/21/2013 :  05:10:08  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000