September 15, 2017 at 11:39 am
Can someone shed light on why this might be? in production the query runs in 1 second, in dev it runs in 12 minutes. the difference in query cost comes from 'compute Scalar' regenerating rows in a view on DEV that apparently doesn't happen in prod.
Could it be related to different VM hosts? or more likely a server setting ?
select
a.CIR_ID as CIRNo,
'CIR',
IsNull(a.GP_Plc,'0') as PlacementNo,
a.EDGE_CandID as EmployeeNo,
cast(a.EDGE_CandID as varchar(15)) as EmployeeID,
a.FullName as EmployeeName,
a.FirstName as FirstName,
a.Initial,
a.LastName as LastName,
a.LastName + ',' + a.FirstName as FullName,
IsNull(a.person_key,0) as person_key,
a.ClassType as PlacementType,
cast(a.StartDate as date) as StartDate,
cast(a.EndDate as date) as EndDate,
a.EZA_JobIDNO as JobOrder,
a.EZA_BsName as CustomerName,
IsNull(cast(a.CustomerID as varchar(50)), ' ') as CustomerID,
cast(a.CreateDate as date) as CreateDate,
b.EZA_IDNO as EdgeCandidateID,
IsNull(b.SSN,' ') as SSN,
b.Gender,
b.FullName as PersonName,
IsNull(b.DB_UNIT,' ') as Company,
c.AddressType,
c.Ref_ID as AddressRefID,
c.Phone1,
c.Phone2,
c.Email as EmailAddress,
c.Email,
Null as SubVendorEmail,
c.RefName as AddressName,
c.FullAddress,
case IsNumeric(a.GP_Plc)
when 1 then CAST(a.GP_Plc as integer)
else 0
end as PlacementNumber,
c.Ref_ID,
c.ID,
GETDATE()
from
inkedserver.dbo.table a
join inkedserver.dbo.table b on a.EDGE_CandID = b.EZA_IDNO
join (select
Ref_ID,
MAX(AddressType) as AddressType,
MAX(RefName)as RefName,
MAX(Phone1) as Phone1,
MAX(Phone2) as Phone2,
IsNull(Left(MAX(Email),200),' ') as Email,
ltrim(rtrim(Left(MAX(IsNull(Address0, ' ') + ' ' + IsNull(City,' ') + ' ' + IsNull([State],' ') + ' ' + IsNull(Zip,' ')),200))) as FullAddress,
max(ID) as ID
from
linkedserver.dbo.table
where
AddressType = 'HOME'
group by
Ref_ID) c on b.ID = c.Ref_ID
September 15, 2017 at 12:18 pm
Could be a server setting, but most likely it is because of different statistics. Update stats on the dev server and see what happens.
If you post the plans we might be able to give more help. If you are concerned about giving away names you can get SentryOne Plan Explorer (free) which will anonymize the plans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2017 at 12:35 pm
Excuse the ignorance, but what type of file would you like? ...Thanks
September 15, 2017 at 12:39 pm
here it is.
Thanks
September 15, 2017 at 12:39 pm
No worries. The estimated execution plan(s) would be fine. This article explains how to create the execution plan and then you can just save it as a .sqlplan file and upload it here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2017 at 12:47 pm
Is that the plan from DEV or production? Honestly there isn't a lot to see in that pan because everything is going across a linked server. There are a number of things that can cause performance issues/inconsistencies when Linked Servers are involved. Check out this post by Tom LaRock about what he's seen. He explains it better than I would.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2017 at 1:14 pm
Thanks, i'll give it a read, the query is from dev.
September 18, 2017 at 6:41 am
Here is the plan minus linked server..Thanks for your help.
September 18, 2017 at 7:47 am
I'd make sure you're doing an apples-to-apples comparison. Ensure the VM for dev has the same number of vCPU's, memory, etc assigned. Then ensure that the SQL server-level settings are the same (max server memory, cost threshold for parallelism, etc.). Then ensure the data is the same on dev as in prod. If you can't backfill the data to dev, then disable the auto_update_statistics option on dev, script out the statistics from prod, and apply to dev.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply