InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:40:04 GMT20http://www.sqlservercentral.com/Forums/Topic1409840-392-1.aspx[quote][b]praveen_vejandla (1/22/2013)[/b][hr]UPDATE LII SET value_exists = 1 FROM LII od JOIN VPL sl ON (sl.it like '%' + od.value + '%' AND od.opr = 9 ) WHERE od.tn = 'VPL' AND od.cn = 'it'Is there anyway to rewrite above query for improved performance? I am joining two nvarchar columns (it, value) using LIKE and it seems very slow.Thanks.[/quote] send the index deifnition of Lil and VPL tableTue, 22 Jan 2013 01:05:05 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409840-392-1.aspx[code="sql"]UPDATE od SET value_exists = 1 FROM LII od INNER JOIN VPL sl ON sl.it LIKE '%' + od.value + '%' WHERE od.tn = 'VPL' AND od.cn = 'it' AND od.opr = 9 [/code]The LIKE operator used with a wildcard on the left hand side of the search expression makes the join non-SARGable (Google it), which results in a table scan of table VPL. Do you really need the leading %?Tue, 22 Jan 2013 00:59:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1409840-392-1.aspxUPDATE LII SET value_exists = 1 FROM LII od JOIN VPL sl ON (sl.it like '%' + od.value + '%' AND od.opr = 9 ) WHERE od.tn = 'VPL' AND od.cn = 'it'Is there anyway to rewrite above query for improved performance? I am joining two nvarchar columns (it, value) using LIKE and it seems very slow.Thanks.Tue, 22 Jan 2013 00:49:55 GMTpraveen_vejandla