sql server - Solving Long Running Query, number of executions problem - Database Administrators Stack Exchange

i have following query:

alter procedure [dbo].[spsearchclient]     @searchwords nvarchar(max) = null,     @lowerdate date = null,     @upperdate date = null,     @usercreated nvarchar(450) begin     set nocount on;      declare @useraccountid smallint     declare @searchwordcount int      select @useraccountid = dbo.fngetuseraccountid(@usercreated)      create table #searchwords     (         id int identity(1,1),         word nvarchar(50)     )      insert #searchwords     (         word     )     select          value               string_split(@searchwords, ' ')                trim(value) <> ''      select @searchwordcount = @@rowcount;      select         c.clientid,          c.firstname,         c.lastname,         c.fullname,          c.dateofbirth,         g.gendername,          g.gendericon,          c.verificationcode,         v.lastvisitdate              client c     outer apply (         select max(startdate) lastvisitdate         visit v         c.clientid = v.clientid     ) v     inner join lookup.gender g on         c.genderid = g.genderid             (             exists( -- if have words                     select *                     #searchwords s                     (c.firstname concat('%',s.word,'%'))                        or (c.lastname concat('%',s.word,'%'))                        or (c.verificationcode concat('%',s.word,'%'))                 )             or @searchwordcount = 0 --if don't have words         )         , dateofbirth between isnull(@lowerdate,dateofbirth) , isnull(@upperdate,dateofbirth)      insert usersearchlog     (         searchwords,         lowerdate,         upperdate,         searchresultscount,         usercreated     )     values     (         @searchwords,         @lowerdate,         @upperdate,         @@rowcount,         @useraccountid     )      drop table #searchwords end 

the execution plan https://www.brentozar.com/pastetheplan/?id=bkgzfuehz

the query works how should takes 3-7 seconds run , seems due following has execution every row in client table:

where     (         exists( -- if have words                 select *                 #searchwords s                 (c.firstname concat('%',s.word,'%'))                     or (c.lastname concat('%',s.word,'%'))                     or (c.verificationcode concat('%',s.word,'%'))             )         or @searchwordcount = 0 --if don't have words     ) 

wondering if knew of better more effective way of doing less time consuming?

if sample data useful, please let me know.

there appears problem server prevent getting performance query, when @searchwordcount = 0. consider details node id 15:

this plan executes in row mode , operator @ end of branch, can attribute 20 ms of cpu time , 3962 ms of elapsed time index seek on [visit].[idx_visit_clientid]. when executing query spent 4 seconds waiting other cpu work. looking @ wait stats select operator provides valuable clue:

almost 4 seconds waiting on pageiolatch_sh. can more information io done looking @ runtimeinformation in xml:

<runtimeinformation> <runtimecountersperthread thread="0" actualrows="8155" actualrowsread="8155"     batches="0" actualendofscans="211" actualexecutions="211"     actualexecutionmode="row" actualelapsedms="3962" actualcpums="20"     actualscans="211" actuallogicalreads="726" actualphysicalreads="42"     actualreadaheads="8" actualloblogicalreads="0"     actuallobphysicalreads="0" actuallobreadaheads="0" /> </runtimeinformation> 

sql server needed 50 physical reads index seek, yet waited 4 seconds them. you're reading data @ rate of 100 kb per second. there problem server configuration or perhaps server overloaded. paul randal published detailed instructions here determining root cause of problem , how fix it. luck.

on subject of pattern matching, sql server took third of second single row in temp table. part of query become bottleneck number of rows increases in temp table. won't performance changing part of query without resolving issue pageiolatch_sh waits first.