Help with a query runs forever but returns nothing.

Question

Help with a query runs forever but returns nothing.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Luis Cazares (11/14/2016)
Eirikur Eiriksson (11/14/2016)
Can you post the actual execution plan, would make this a lot easier.
😎
If the plan contains sensitive data then you can use SQL Sentry Plan Explorer to anonymize it before posting it.
I'm not sure if the plan would be helpful. I'd expect a lot of Remote Query operators.

And it may well me more efficient when run on the remote server - only one table to bring over.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Ken McKelvey SSCoach Points: 18843 More actions · Answer 2

I also suspect running the lot remotely will be more efficient but there might be two tables to copy as [ETL].[dbo].[ETLCorrelation] and ETLCorrelation could be different.

You should get something going by doing the remote part of the querty with OPENQUERY as the full tables will not need to be dragged over the link.

Something like:

WITH AddB20

AS

(

SELECT *

FROM OPENQUERY

(addb20

,'SELECT st.ConfigOptionTypePropertyId, st.Active, st.ConfigOptionGUID

,st.ConfigOptionValue, st.FKGUID, st.ConfigOptionID

FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st

LEFT JOIN [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption]) st2

ON st.ConfigOptionTypePropertyId = st2.ConfigOptionTypePropertyId

AND st.FKGUID = st2.FKGUID

-- This does not seem to be referenced but could add more rows.

--LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] FK1Table

--ON st.ConfigOptionTypePropertyID = FK1Table.ConfigOptionTypePropertyId

WHERE st2.ConfigOptionTypePropertyId IS NULL'

)

,ETL

AS

(

SELECT FK1.SourceID, FK1.DestinationID

FROM [ETL].[dbo].[ETLCorrelation] FK1

OUTER APPLY

(

SELECT TOP (1) MergedRecord

FROM ETLCorrelation E

WHERE E.DestinationTable = 'ConfigOptionTypeProperty'

AND E.MergedRecord = 1

AND E.BatchID = 6283

) A

WHERE FK1.SourceTable = 'ConfigOptionTypeProperty'

AND FK1.BatchID = 6283

AND

(

FK1.SourceID <> FK1.DestinationID

AND A.MergedRecord IS NOT NULL

)

OR

(

FK1.SourceID = FK1.DestinationID

AND A.MergedRecord IS NULL

)

--INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] ([Active], [ConfigOptionGUID], [ConfigOptionTypePropertyId], [ConfigOptionValue], [FKGUID] ,ImportIdentity, GUIDIdentity)

SELECT

A.Active,

A.ConfigOptionGUID,

E.DestinationID,

A.ConfigOptionValue,

A.FKGUID,

A.ConfigOptionID,

'0000'

FROM AddB20 A

JOIN ETL E

ON A.ConfigOptionTypePropertyId = E.FK1.SourceID;

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

Tried this same result.

The NOT EXISTS option seems to have worked. I am working out a couple other things.

Thank you everyone.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh