Searching with a lateral join extremely slow

I’m currently implementing a search function in one of our grids and when doing so, my query goes from running in 1 second or less to running in about 16 seconds (I’ve added suggested indices, etc). The data returned from this query will be the parent’s id, parent’s name, most recent child’s first name and most recent child’s last name. The following simplified query runs extremely slow (15+ seconds) but functions correctly:

SELECT 
   p.id
  ,p.name
  ,c.firstname
  ,c.lastname
FROM Parents p
CROSS APPLY (
  SELECT TOP (1) 
     c.firstname
    ,c.lastname
  FROM Children c
  WHERE c.ParentId = p.Id
  ORDER BY c.datecreated DESC
  ) i
INNER JOIN Users s
  ON p.UserId = u.Id
WHERE (
    @search IS NOT NULL
    AND @search != ''
    AND (
      @search = c.firstname
      OR @search = c.lastname
      OR p.name = @search
      )
    OR @search IS NULL
    OR @search = ''
    )

The following query (notice the first name and last name searching is not included) runs quickly but is missing desired functionality:

SELECT 
   p.id
  ,p.name
  ,c.firstname
  ,c.lastname
FROM Parents p
CROSS APPLY (
  SELECT TOP (1) 
     c.firstname
    ,c.lastname
  FROM Children c
  WHERE c.ParentId = p.Id
  ORDER BY c.datecreated DESC
  ) i
INNER JOIN Users s
  ON p.UserId = u.Id
WHERE (
    @search IS NOT NULL
    AND @search != ''
    AND p.name = @search
    )
  OR (@search IS NULL)
  OR (@search = '')

How can I optimize my searching query such that it runs quickly? In practice, there are many other joins and much more activity in my query however I believe this to be the issue as when I comment out the first and last name searching, my query runs very quickly.

All help is appreciated.

EDIT: I’ve also tried with an inner join and subquery as shown below however this produced even worse results than the lateral join attempt originally shown (~25 second execution time)

SELECT
  p.id, 
  p.name,
  c.firstname,
  c.lastname
from Parents P
INNER JOIN children c 
ON c.ParentId = p.Id
INNER JOIN Users s
ON p.UserId = s.Id
WHERE c.datecreated = (
select max(c1.datecreated) from children c1 where c1.ParentId = c.ParentId
)
and @search IS NOT NULL
AND @search != ''
AND (
  @search = c.firstname
  OR @search = c.lastname
  OR p.name = @search
  )
OR @search IS NULL
OR @search = ''
)

Answer

Based on just this part of your question, I am confident you have a parameter sniffing problem.

my query goes from running in 1 second or less to running in about 16 seconds

Please add OPTION (RECOMPILE) to the end of query and see what happens (But please investigate the implications of this and understand the CPU consequences). You can also look at OPTIMIZE FOR UNKNOWN

---

Going in blind, a few ideas that you could try

• Reduce 1 lookup to Children i.e. WHERE c.datecreated = (select max(c1.datecreated) from children c1 where c1.ParentId = c.ParentId)
• Clean up the @Search logic by reducing it

SELECT p.id,
       p.name,
       c.firstname,
       c.lastname
FROM Parents P
    INNER JOIN
    (
        select c1.firstname,
               c1.lastname,
               c1.ParentId,
               row_number() OVER (PARTITION BY c1.ParentId ORDER BY c1.datecreated DESC) as RN
        from children c1
    ) as c
        ON c.ParentId = p.Id
           AND c.RN = 1 --/*Get the Latest First,Lastname based on datecreated*/
    INNER JOIN Users s
        ON p.UserId = s.Id
WHERE 1 = 1
      AND (
              c.firstname = @search
              OR c.lastname = @search
              OR p.name = @search
              -- if @search is either NULL / '' it will return
              OR NULLIF(@search, '') IS NULL
          )
--OPTION (RECOMPILE) /*Uncomment this and see does it improve*/

Also just beaware that you could be running into a Parameter Sniffing problem i.e. since @search is a query parameter and it looks like it can vary a lot.

Can you try adding OPTION RECOMPILE to the end of your query and seeing if that makes a difference

Also things to try if you haven’t done so already.

• update statistics on tables
• defrag/rebuild indexes
• look at implementing ola.hellengren maintenance plans

And finally, to get some more help can you paste the query plan using https://www.brentozar.com/pastetheplan/