SQL Server index - ideas?

Question

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?

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago) and its use is discouraged — marc_s
– marc_s, Commented Jul 30, 2017 at 8:50
The query is slow because you are using functions on your predicates. This does not allow SQL to use the statistics to create a good plan. It has to scan every single row in order to figure out what the value of REPLACE is on that column as well as the CONCAT on the other side of the equal sign. Go with the computed column as was suggested. — JMabee
– JMabee, Commented Jul 31, 2017 at 18:14

Pரதீப் · Accepted Answer · 2017-07-30 08:34:55Z

1

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

answered Jul 30, 2017 at 8:34

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server index - ideas?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related