October 14, 2018 at 11:47 pm
Hi,
I have this query SELECT AIM.*
FROM database1.dbo.vw_Customer C WITH(NOLOCK)
LEFT
JOIN database1.dbo.vw_CustomerContact CC WITH(NOLOCK) ON C.CustD = CC.CustID
WHERE C.Code = 'FD'
AND (
C.IsActive = 1
)
This query used to take less than 2 min until friday, all the nothing has changed from the tables being referenced in those Views.
- When I select TOP 10 I don't get any records back
- So my question is:
1. why would the query runs over 20min when I SELECT * but when I SELECT TOP 10 is runs quick and returns nothing
2. What would normally causes a query in one Environment to runs longer than it normally does
October 15, 2018 at 12:42 am
What is the object AIM? it's not referenced anywhere in your query apart from in the SELECT. I also assume you're ok with this view returning incorrect data with the use of NOLOCK?
Are there indexes on the tables? Have the statistics been updated recently?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 15, 2018 at 12:59 am
Thom A - Monday, October 15, 2018 12:42 AMWhat is the object AIM? it's not referenced anywhere in your query apart from in the SELECT. I also assume you're ok with this view returning incorrect data with the use of NOLOCK?Are there indexes on the tables? Have the statistics been updated recently?
AIM was supposed to be C. Incorrect data is better than this code running forever at this stage. I'm not sure about statistics been updated. Tables Indexes are the same in my DEV and QA environment, the query runs fast on DEV and PROD, but all of the sudden it's taking long on my QA, starting from last week Friday.
October 15, 2018 at 7:29 am
There are a bunch of things that could cause differences between environments. ANSI connection settings, cost threshold for parallelism, max degree of parallelism, memory, cpu, disks, data, statistics, structures. Any or all of these in combination can result in differences in execution time. For your two queries, capture the execution plans and compare them. That can get you a better idea of why one is running slow and one is not.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply