Searching with a lateral join extremely slow

Question

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 = ''
)

What is the first query supposed to do? Return parents whose most recently created Children record has a matching name (this is what the query does) or return parents who have at least one child with a matching name (this seems like what you would want). — Bacon Bits, Jul 01 '20 at 14:26
the data returned should be the name and ID of all parents and the first and last name of their most recent child. then that first and last name of the children should be searchable. does that make more sense @BaconBits? — GregH, Jul 01 '20 at 14:31
Using a "catch all" query usually generates a fairly poor plan, since the optimizer will either generate a plan when a @search is supplied or not, but the same plan won't work in any other cases. Common solution is to use dynamic SQL to completely avoid the multiple ORs. I'd also suggest filtering the names first and then checking if the child is the latest or not. — EzLo, Jul 01 '20 at 14:43
@EzLo do you have any examples or links that can make these suggestions more clear? What part of the above queries are you referring to as a "catch all" query — GregH, Jul 01 '20 at 14:47
Suggested reading: [Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search.html). — HABO, Jul 01 '20 at 16:42

Kgeorge · Accepted Answer · 2020-07-01T15:51:42.470

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/

`OPTION (RECOMPILE)` did the trick - the query is back to running in under 1 second — GregH, Jul 01 '20 at 15:59
Look at this https://stackoverflow.com/a/40437749/7351346 to see which is better `OPTION (RECOMPILE)` or `OPTION(OPTIMIZE FOR UNKNOWN)` — Kgeorge, Jul 01 '20 at 17:39

Searching with a lateral join extremely slow

1 Answers1