Complex view query needs improving - CHARINDEX join

[eo]

I have a few tables which are used in a view (SQL 2012), but the query takes far too long to run and I am looking at suggestions
to improve performance. I do howevver know that clear bottleneck is the following join..

  LEFT JOIN dbo.ToneDictionary D
  ON CHARINDEX(Dictionary,Title) > 0

The reason for such a join is because I have to establish if the [Title] contains one or more (or none) of the words in [Dictionary]. There could be millions of [Title]'s and about 20 or so [Dictionary] values.

I know this is a very broad question, but has anyone achieved something similar by using something other than a CHARINDEX join?



EO
Hertfordshire, England

 

[IanWaterman]

YOu have not joined the tables, there must be a relationship, your Charindex is a condition

Syntax should look like

LEFT JOIN dbo.ToneDictionary D
ON Table.Field1 = D.Field2
and CHARINDEX(Dictionary,Title) > 0

Ian

 

[fredericofonseca]

try these - it may be faster

LEFT JOIN dbo.ToneDictionary D
  ON title like '%' + dictionary + '%'

or

LEFT JOIN dbo.ToneDictionary D
  ON title like '%' + rtrim(ltrim(dictionary)) + '%'

and just as a note.. "ON CHARINDEX(Dictionary,Title) > 0" is a valid join condition

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

pressed submit too fast...

regardless of, the search always going to be slow as every record on Title needs to be retrieved and compared.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886