We help IT Professionals succeed at work.
Research Question

query slow with different parameter input

67 Views
Last Modified: 2020-10-29
hi,

we have a query sometime slow sometime fast ( finish 2 x sec)

with a as (

select
100 as priority,
'IPD' as type,
adm.[billing.accountCode] as accountNo,
adm.admissionDate as date
from
active_admission_list_view adm
where
adm.[patient.id] = 94888

union all

select
50 as priority,
'IMAGE_ORDER' as type,
today_billing.account_code,
today_wq.arrive_time
from
waiting_queue wq
join cpoe_order_serving task on task.waiting_queue_id = wq.id
join lab_test lt on task.lab_test_id = lt.id
join lab_order lo on lt.lab_order_id = lo.id
join encounter e on lo.encounter_id = e.id
join billing b on e.billing_id = b.id
join waiting_queue order_wq on order_wq.encounter_id = e.id
join clinic order_clinic on order_clinic.id = order_wq.clinic_id
join waiting_queue today_wq
on today_wq.clinic_id = order_clinic.id
and today_wq.arrive_time >= CONVERT(DATE, getdate(), 101)
and today_wq.arrive_time < dateadd(day, 1, CONVERT(DATE, getdate(), 101))
and today_wq.patient_id = wq.patient_id
join queue_status_enum st with (nolock) on today_wq.status_id = st.id
join encounter today_encounter on today_wq.encounter_id = today_encounter.id
join billing today_billing on today_encounter.billing_id = today_billing.id
where
wq.id = 706701
and st.label not in ('Cancelled', 'NoShow', 'Block', 'Aborted')

)
select
top 1 *
from
a
order by
priority desc


any idea on why is it? I can't remember the reason why query, when passing different parameters, will perform VERY differently?

the execution plan do not suggest any index !

because it has some kind of cached query plan which happens to be bad? is it ok to clear the plan cache ?

Comment
Watch Question

marrowyungSenior Technical architecture (Data)

Author

Commented:
here attached the execution plan

slow parameterize query.sqlplan
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
graph version for anyone interested...
https://www.brentozar.com/pastetheplan/?id=H18gL7H_v
marrowyungSenior Technical architecture (Data)

Author

Commented:
you just plug in my query and it shows that out ?


marrowyungSenior Technical architecture (Data)

Author

Commented:
and our developer seems plug in the old plan for better performance, why ?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is it returning one record all the time?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you just plug in my query and it shows that out ?

internet is good...
yes, why?
marrowyungSenior Technical architecture (Data)

Author

Commented:
is it returning one record all the time?
most likely!

it just sometime it is fast and some time it is slow and our developer just plug in the OLD plan.


HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
add this to 2nd query
select top 1
 50 as priority,
...
to see any difference...
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
first part always return 1 record?
I get this from developer and it can retrieve some record but usually returns fast except sometime.

add this to 2nd query
why it does matter?

also add top 1 to the first query so it becomes

and it will looks like :

select
top 1  50 as priority,
'IMAGE_ORDER' as type,
today_billing.account_code,
today_wq.arrive_time

?

can you explain the logic behind on why it is faster 
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
this is what I mean in my previous posts...

with a as (

  select top 1 
    100 as priority,
    'IPD' as type,
    adm.[billing.accountCode] as accountNo,
    adm.admissionDate as date
  from
    active_admission_list_view adm
  where
    adm.[patient.id] = 94888
-- ADD SOME ORDER HERE, otherwise it looks meaningless unless it always returns 1 record

  union all

  select top 1
    50 as priority,
    'IMAGE_ORDER' as type,
    today_billing.account_code,
    today_wq.arrive_time
  from
    waiting_queue wq
    join cpoe_order_serving task on task.waiting_queue_id = wq.id
    join lab_test lt on task.lab_test_id = lt.id
    join lab_order lo on lt.lab_order_id = lo.id
    join encounter e on lo.encounter_id = e.id
    join billing b on e.billing_id = b.id
    join waiting_queue order_wq on order_wq.encounter_id = e.id
    join clinic order_clinic on order_clinic.id = order_wq.clinic_id
    join waiting_queue today_wq on today_wq.clinic_id = order_clinic.id
    and today_wq.arrive_time >= CONVERT(DATE, getdate(), 101)
    and today_wq.arrive_time < dateadd(day, 1, CONVERT(DATE, getdate(), 101))
    and today_wq.patient_id = wq.patient_id
    join queue_status_enum st with (nolock) on today_wq.status_id = st.id
    join encounter today_encounter on today_wq.encounter_id = today_encounter.id
    join billing today_billing on today_encounter.billing_id = today_billing.id
  where
    wq.id = 706701
    and st.label not in ('Cancelled', 'NoShow', 'Block', 'Aborted')
-- ADD SOME ORDER HERE, otherwise it looks meaningless unless it always returns 1 record

)

select
  top 1 *
from
  a
order by
  priority desc
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks. but why top 1 is faster  ? as SQL server will load only the first record faster but the rest slower ? if  I remember correctly from my previous project.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
Tomas ,

What I deduce from looking at the query is that the reason for the slowness sometimes may be due to a shortage of space in the tempdb database at the time of the query execution

this make me interested as we just add 12 more tempdb during this weekend of the same size, disk still has a lot of space left,.

And as the column is in fact with fixed data (100 and 50) and the fact that the query only returns the first row ( top 1)
originally is not top 1,HainKurt added to it.

I see that the data produced by the query below the union all statement is never returned nor considered to be returned.

how can you know second one

select
50 as priority,
'IMAGE_ORDER' as type,
today_billing.account_code,
today_wq.arrive_time
from
waiting_queue wq
join cpoe_order_serving task on task.waiting_queue_id = wq.id
join lab_test lt on task.lab_test_id = lt.id
join lab_order lo on lt.lab_order_id = lo.id
join encounter e on lo.encounter_id = e.id
join billing b on e.billing_id = b.id
join waiting_queue order_wq on order_wq.encounter_id = e.id
join clinic order_clinic on order_clinic.id = order_wq.clinic_id
join waiting_queue today_wq
on today_wq.clinic_id = order_clinic.id
and today_wq.arrive_time >= CONVE
.
.
.
.

do not return at all?

and this one:

wq.id = 706701

means sth will return back.

HainKurt ,

first part always return 1 record? what if it is not ?

second one returns many... (Because too many join?)
you union all then get one of them base don priority? (what is base don?)
but what's the sort on second query?  the third one is sorting, right? order by, rigtht?





marrowyungSenior Technical architecture (Data)

Author

Commented:
gentlmen,

this is the key part we haven't discussed yet:

any idea on why is it? I can't remember the reason why query, when passing different parameters, will perform VERY differently?

from my point of view only, when a parameter make SQL server hints a much LARGER data set as the result the performance will be very slow, that's why sometimes it is slow and sometimes it is fast!

and how to solve this problem?
data partition the table related?

this seems related to data pattern ? or move that large set of data to another SP so that the data set is handle separately ?

marrowyungSenior Technical architecture (Data)

Author

Commented:
any idea ?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
@thomas

I therefore strongly suggest you rewrite the query and lose the second part below the union all statement as that data is never returned but produces a lot of unnessary workload on the database at the execution time

I guess first part returns 0-many
second one returns 0-many

that's why I suggested a few times

with t as (
select top 1 100 priority, ... from ... order by ...
 union all
select top 1  50 priority, ... from ... order by ...)
select top 1 * from t order by priority

marrowyungSenior Technical architecture (Data)

Author

Commented:
yeah, will come back to this later.

how about this :

from my point of view only, when a parameter make SQL server hints a much LARGER data set as the result the performance will be very slow, that's why sometimes it is slow and sometimes it is fast!


and how to solve this problem?
data partition the table related?


this seems related to data pattern ? or move that large set of data to another SP so that the data set is handle separately ? 



HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
can you please use

when quoting something we say, and use

when you add some code...
marrowyungSenior Technical architecture (Data)

Author

Commented:
when quoting something we say, and use

ok. aaaha

marrowyungSenior Technical architecture (Data)

Author

Commented:
my point is how to solve parameter query which has this problem in case a parameter hits the high volumn result set, then both logical and physical read super high because of this.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
 I doubled checked and you are right.

first part always return 1 record

marrowyungSenior Technical architecture (Data)

Author

Commented:
any why


order by admissionDate desc

and

order by arrive_time desc

?
marrowyungSenior Technical architecture (Data)

Author

Commented:
and one thing, we found that clear  the execution plan for this query and use the old one can solve problem, usually why clear plan cache can fix it?

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
order by admissionDate desc
is redundant if it always return 0/1 record...

order by arrive_time desc
is necessary if may returns multiple record, since you want one of them max, and do not want any random one...
thats why you need to sort it somehow before picking top 1
marrowyungSenior Technical architecture (Data)

Author

Commented:
is necessary if may returns multiple record, since you want one of them max, and do not want any random one...
thats why you need to sort it somehow before picking top 1

ok

marrowyungSenior Technical architecture (Data)

Author

Commented:
by Tomas:
 The reason is that your query retrieves "a lot" of data from both queries above and below the union all statement and combines it prior to ordering the data in descending order by the column priority. And as the column is in fact with fixed data (100 and 50) and the fact that the query only returns the first row ( top 1) in the "ordered" resultset, I see that the data produced by the query below the union all statement is never returned nor considered to be returned.

so the correct way to do is, the result set has to order first then union ?

and also this:
100 as priority,
and
50 as priority,
is no need at all ? as 100 as priority should replaced by top 1 as we only need the first 1 row ?

and the union all result set is large so order by will be a time consuming operation as it is on the much larger result set ?

why you think tempdb space is not enough and second query do not returns any data?
marrowyungSenior Technical architecture (Data)

Author

Commented:
HainKurt ,

is redundant if it always return 0/1 record...

so actually this one no need at all ?

order by admissionDate desc


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
so actually this one no need at all ?
if you are 100% sure, that query returns MAX 1 record, then you dont need...
and that order is harmless even if it is there...
you cannot sort 1 record anyways :)
ignore it, leave it, harmless...
marrowyungSenior Technical architecture (Data)

Author

Commented:
and that order is harmless even if it is there...

agree!

and for my statement:

so the correct way to do is, the result set has to order first then union ? 
and the union all result set is large so order by will be a time consuming operation as it is on the much larger result set ?

do you think my logic is correct?

HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
then you sort them again by priority and select top 1...

one from the top and one from the bottom query ?

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
one from the top and one from the bottom query ?

yes...
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi man,

please help on this:

https://www.experts-exchange.com/questions/29198142/change-from-SQL-server-2016-standard-edition-FCI-active-passive-to-active-active.html

 if we convert SQL server 2016 2 x nodes standard edition active/passive FCI to active/active FCI, both nodes need to upgrade from SQL server 2016 standard edition to SQL server 2016 enterprise edition ?

how about Windows 2012 R2 standard edition? both server OS also need to upgrade to windows 2012R2 enterprise edition?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
hi man,
please help on this:

sorry, I am not DBA...
I am mostly working on integrating/development/coding/debugging/bug resolving issues...
no xp on db / network management part :)
marrowyungSenior Technical architecture (Data)

Author

Commented:
ok .tks.   I will wait for more comment, I understand no DB person are strong on infra side and development side.

and I am more on infra side.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi man,

with different parameter input


this hasn't been answered yet! this is the key of this equestion.

but you already helped a lot of other question.

but I think my memory still ok on this, it is when a parament hits a ROW in the DB which involve MUCH MORE data to returns, then only that parameter give long run result set. ! we have to wait for data.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
it is like

"search all the books which includes 'love'"
vs
"search all the books which includes 'I do not like racism!'"

:) one returns many many books, other returns a couple maybe...
marrowyungSenior Technical architecture (Data)

Author

Commented:
aaha , so what is your comment on my original question ?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
your  original question?
I gave you answer...
more data from each join, more time to process...
marrowyungSenior Technical architecture (Data)

Author

Commented:
this is one of these:

any idea on why is it? I can't remember the reason why query, when passing different parameters, will perform VERY differently?

marrowyungSenior Technical architecture (Data)

Author

Commented:
gave you one more LIKE. :):)

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I expected you could do better than that...
a nice testimonial :)
so, when I lose my job, I will use those LOL to find my next job..
marrowyungSenior Technical architecture (Data)

Author

Commented:
actually this is one of the article I read before, and I hope I can understand here one more time as I can remember sth wrong.
marrowyungSenior Technical architecture (Data)

Author

Commented:
another actually is, developer found out make it faster by only clean the execution plain cache and it is fast! no change in queries.

I seems heard that this is one of the problem of SQL server, and it still exists.

 I think that's why from time to time developer prefer to clear the plan cache regularily .