Edit Question - KnowledgeHub

Title Body

I had a query as follows (simplified)... SELECT * FROM table1 AS a INNER JOIN table2 AS b ON (a.name LIKE '%' + b.name + '%') For my dataset this was taking around 90 seconds to execute, so I have been looking for ways of speeding it up. For no good reason, I thought I'd try PATINDEX instead of LIKE... SELECT * FROM table1 AS a INNER JOIN table2 AS b ON (PATINDEX('%' + b.name + '%', a.name) > 0) On the same dataset this executes in the blink of an eye and returns the same results. Can anyone explain why LIKE is so much slower than PATINDEX? Given that LIKE is just returning a BOOLEAN whereas PATINDEX is returning the actual location I would have expected the latter to be slower if anything, or is it simply a matter of how efficiently the two functions have been written? Ok, here is each query in full, followed by its execution plan. "#StakeholderNames" is just a temp table of likely names which I am matching against. I have pulled back the live data and run each query several times. The first is taking about 17 seconds (so somewhat less than the original 90 seconds on the live database) and the second under 1 second... SELECT sh.StakeholderID, sh.HoldingID, i.AgencyCommissionImportID, 1 FROM AgencyCommissionImport AS i INNER JOIN #StakeholderNames AS sn ON REPLACE(REPLACE(i.ClientName,' ',''), ',','') LIKE '%' + sn.Name + '%' INNER JOIN Holding AS h ON (h.ProviderName = i.Provider) AND (h.HoldingReference = i.PlanNumber) INNER JOIN StakeholderHolding AS sh ON (sn.StakeholderID = sh.StakeholderID) AND (h.HoldingID = sh.HoldingID) WHERE i.AgencyCommissionFileID = @AgencyCommissionFileID AND (i.MatchTypeID = 0) AND ((i.MatchedHoldingID IS NULL) OR (i.Matc

Tags (comma-separated) Cancel