March 5, 2019 at 12:41 pm
I am trying to understand this behavior but when I try to use the below code and execute it in the context of a different database it runs extremely slow and takes around 45 mins to complete whereas the same code if I run under the same DB that it is referencing in the code it executes with a fraction of secs. and I don't even understand why this behavior....is there a reason why it does what it does. Please note that this implementation or my question would not sound logical but I definitely need to understand what is the reason for this. Apologies but I can't provide the actual code because of some strict company policies so I am just using the below code as an example...hoping that someone might have come across something like this.
Below is similar to what I am after but this hasn't bee answered.
https://stackoverflow.com/questions/41553310/query-against-a-view-under-master-database-is-much-slower-than-query-directly-un
-- Below code runs for approximately 45 mins.
use master;
select (select count(a.x) from (select co.id, ac.dq_error_msg
from xyz.dbo.zaa as co
left outer join xyz.dbo.zab as ac
on co.id = ac.id
... (some code)
left outer join openquery ('some select code'))) as fail_count,
(select count(a.x) from (select co.id, ac.dq_error_msg
from xyz.dbo.zaa as co
left outer join xyz.dbo.zab as ac
on co.id = ac.id
... (some code)
left outer join openquery ('some select code'))) as opp_count,
getdate() as date_today
Same code when executed in the context of the referenced DB takes fraction of secs to execute.
use xyz;
select (select count(a.x) from (select co.id, ac.dq_error_msg
from xyz.dbo.zaa as co
left outer join xyz.dbo.zab as ac
on co.id = ac.id
... (some code)
left outer join openquery ('some select code'))) as fail_count,
(select count(a.x) from (select co.id, ac.dq_error_msg
from xyz.dbo.zaa as co
left outer join xyz.dbo.zab as ac
on co.id = ac.id
... (some code)
left outer join openquery ('some select code'))) as opp_count,
getdate() as date_today
SQL version: SQL 2016 SP1.
March 5, 2019 at 12:56 pm
Did you look at / compare the query plans?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 5, 2019 at 2:21 pm
ScottPletcher - Tuesday, March 5, 2019 12:56 PMDid you look at / compare the query plans?
It is actually quite same.
March 5, 2019 at 10:14 pm
That is a huge difference. I suspect you run both queries in SMSS ? The execution plan is the exact same (including estimated/actual statistics) ? SET STATISTICS IO ON return the same ?
Have you tried to break down your query to see where exactly it is slow ?
March 6, 2019 at 2:17 am
Feivel - Tuesday, March 5, 2019 2:21 PMScottPletcher - Tuesday, March 5, 2019 12:56 PMDid you look at / compare the query plans?It is actually quite same.
Post the two plans as .sqlplan attachments.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 6, 2019 at 5:18 am
Feivel - Tuesday, March 5, 2019 2:21 PMScottPletcher - Tuesday, March 5, 2019 12:56 PMDid you look at / compare the query plans?It is actually quite same.
Quite the same or exactly the same? Management Studio 17 can compare two plans. Use that to check.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2019 at 12:33 pm
It seems like maybe something is blocking the "slow" run, although I'm not sure what it would be.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 11, 2019 at 11:37 am
Okay so I figured out the issue was with different compatibility levels and so the performance dip...thanks everyone for your suggestions.
March 11, 2019 at 12:07 pm
Feivel - Monday, March 11, 2019 11:37 AMOkay so I figured out the issue was with different compatibility levels and so the performance dip...thanks everyone for your suggestions.
Thanks for sharing. Good to know what the solution was.
BTW, that info would have been visible in the two different execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 11, 2019 at 2:59 pm
Grant Fritchey - Monday, March 11, 2019 12:07 PMFeivel - Monday, March 11, 2019 11:37 AMOkay so I figured out the issue was with different compatibility levels and so the performance dip...thanks everyone for your suggestions.Thanks for sharing. Good to know what the solution was.
BTW, that info would have been visible in the two different execution plans.
Yes that is where I found it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply