CODEWITHSUNDEEP
sql-serverdatabaset-sqlindexing
Mohd Shakhtour
Mohd Shakhtour

Reputation: 175

SQL Server index - ideas?

I have this query : 

SELECT 
    c.violatorname
FROM 
    dbo.crimecases AS c,
    dbo.people AS p
WHERE 
    REPLACE(c.violatorname, ' ', '') = CONCAT(CONCAT(CONCAT(p.firstname, p.secondname), p.thirdname), p.lastname);

The query is very slow, I need to create an index on violatorname column with replace function. Any ideas?

Upvotes: 0

Views: 57

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

I would suggest you to add computed columns and create index on it. 

ALTER TABLE crimecases
  ADD violatornameProcessed AS Replace(violatorname, ' ', '') PERSISTED 

ALTER TABLE people
  ADD fullName AS Concat(firstname, secondname, thirdname, lastname) PERSISTED

Persisted will store the computed data on the disk instead of computing every time. Now create index on it.

CREATE INDEX Nix_crimecases_violatornameProcessed
  ON crimecases (violatornameProcessed)
  include (violatorname)

CREATE INDEX Nix_people_fullName
  ON people (fullName)

Query can be written like 

SELECT c.violatorname
FROM   dbo.crimecases AS c
       INNER JOIN dbo.people AS p
               ON c.violatornameProcessed = p.fullName

Upvotes: 1

Related Questions