r_noob (7/15/2013)
I've spent a fair bit of time searching, but can't seem to find a succinct answer...is there a more efficient/faster way of performing the following query? I simply have a lookup table with 45k records and want to search a string field for the presence of any of these lookup values. Currently this query takes 45 min on 800k records. Would it help to index [Table1] on [Field1]? Would a subquery with patindex() be faster?UPDATE [Table1]
SET [Field2] = 'Yes'
FROM [Table1] A
JOIN [Lookup] as B
ON (A.Field1 like '% ' + B.LookupValue + ' %')
Thanks!
Try this:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Field1] INT NULL,
[Field2] NVARCHAR(3) NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT 22,'No' UNION ALL
SELECT 31,'No' UNION ALL
SELECT 42,'No' UNION ALL
SELECT 55,'No'
IF OBJECT_ID('tempdb..#LookupTable') IS NOT NULL
DROP TABLE #LookupTable
CREATE TABLE #LookupTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[LookupValue] INT NULL,
[LookDesc] NVARCHAR(30) NULL,
PRIMARY KEY (ID))
INSERT INTO #LookupTable
SELECT 22,'Apple' UNION ALL
SELECT 33,'Orange' UNION ALL
SELECT 44,'Banana' UNION ALL
SELECT 55,'Pear'
UPDATE #TempTable
SET Field2 = 'Yes'
FROM
#TempTable AS A
INNER JOIN
#LookupTable AS B
ON A.ID = B.ID
WHERE
A.Field1 = B.LookupValue
SELECT * FROM #TempTable AS tt