November 13, 2016 at 9:49 pm
Ok the WHERE clause is a Unique index. If I run the SELECT / FROM / JOIN's I get data returned no problem.
If I comment the JOIN statements and run SELECT / FROM / WHERE it is also fast.
All together it just runs forever.
HELP?? Any ideas please? Been battling this with various tables and the same kind of dynamically created statement with no luck and this is NOT good for me or the company.
Any help is appreciated more than you know.
(The query would help)
SELECT * --st.[Active], st.[ConfigOptionGUID], FK1.DestinationID, st.[ConfigOptionValue], st.[FKGUID], st.ConfigOptionID, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] as FK1Table on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND (((FK1.SourceID <> FK1.DestinationID
AND EXISTS (SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283) )
OR (FK1.SourceID = FK1.DestinationID
AND NOT EXISTS (SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283)) ) )
WHERE cast((cast(st.ConfigOptionTypePropertyId as varchar) + convert(varchar (256), st.FKGUID)) as varchar(300))
NOT IN (SELECT (cast(ConfigOptionTypePropertyId as varchar) + convert(varchar (256), FKGUID))
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption])
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 13, 2016 at 11:19 pm
[Left Join & Join and Correlated Sub-query & again Sub-query] All this with {SELECT *} creating heavy query.
Now sure how many records you are dealing.
My guess is LEFT JOIN and EXISTS creating long run.
ThanksSaurabh.D
November 13, 2016 at 11:23 pm
I commented the actual list of columns for testing. It is not SELECT * in principal / production.
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] ([Active], [ConfigOptionGUID], [ConfigOptionTypePropertyId], [ConfigOptionValue], [FKGUID] ,ImportIdentity, GUIDIdentity)
SELECT st.[Active], st.[ConfigOptionGUID], FK1.DestinationID, st.[ConfigOptionValue], st.[FKGUID], st.ConfigOptionID, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] as FK1Table on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
JOIN [ETL].[dbo].[ETLCorrelation] FK1 on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND (((FK1.SourceID <> FK1.DestinationID
AND EXISTS (SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283) )
OR (FK1.SourceID = FK1.DestinationID
AND NOT EXISTS (SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283)) ) )
WHERE cast((cast(st.ConfigOptionTypePropertyId as varchar) + convert(varchar (256), st.FKGUID)) as varchar(300))
NOT IN (SELECT (cast(ConfigOptionTypePropertyId as varchar) + convert(varchar (256), FKGUID))
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption])
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 14, 2016 at 12:41 am
The first thing I suggest is to rewrite the WHERE clause to eliminate the conversion and concatenation, either change to an EXIST or JOIN clause.
😎
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption]
(
[Active]
, [ConfigOptionGUID]
, [ConfigOptionTypePropertyId]
, [ConfigOptionValue]
, [FKGUID]
, ImportIdentity
, GUIDIdentity
)
SELECT
st.[Active]
, st.[ConfigOptionGUID]
, FK1.DestinationID
, st.[ConfigOptionValue]
, st.[FKGUID]
, st.ConfigOptionID
, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] FK1Table
on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
JOIN [ETL].[dbo].[ETLCorrelation] FK1
on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND
(
(
(
FK1.SourceID <> FK1.DestinationID
AND EXISTS
(
SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283
)
)
OR
(
FK1.SourceID = FK1.DestinationID
AND NOT EXISTS
(
SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283
)
)
)
)
WHERE CAST (
(
CAST(st.ConfigOptionTypePropertyId AS VARCHAR)
+ CONVERT(varchar (256), st.FKGUID)
) as varchar(300)
)
NOT IN (
SELECT
(
cast(ConfigOptionTypePropertyId as varchar)
+ convert(varchar (256), FKGUID)
)
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption]
)
;
November 14, 2016 at 8:05 am
Can you give an example please? This is a NOT IN and the cast / convert is present because the two columns together are a unique index.
A join would be binding records where a match exists no?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 14, 2016 at 8:22 am
What about rewriting the WHERE clause to this?
WHERE NOT EXISTS(
SELECT 1
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] co
WHERE st.ConfigOptionTypePropertyId = co.ConfigOptionTypePropertyId
AND st.FKGUID = co.FKGUID)
The JOIN clauses seem weird and mainly useless.
November 14, 2016 at 9:42 am
Here is another option, no need to concatenate the key columns
😎
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption]
(
[Active]
, [ConfigOptionGUID]
, [ConfigOptionTypePropertyId]
, [ConfigOptionValue]
, [FKGUID]
, ImportIdentity
, GUIDIdentity
)
SELECT
st.[Active]
, st.[ConfigOptionGUID]
, FK1.DestinationID
, st.[ConfigOptionValue]
, st.[FKGUID]
, st.ConfigOptionID
, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] FK1Table
on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
JOIN [ETL].[dbo].[ETLCorrelation] FK1
on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND
(
(
(
FK1.SourceID <> FK1.DestinationID
AND EXISTS
(
SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283
)
)
OR
(
FK1.SourceID = FK1.DestinationID
AND NOT EXISTS
(
SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283
)
)
)
)
LEFT OUTER JOIN [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] EXCLUTION_SET
ON st.FKGUID = EXCLUTION_SET.FKGUID
AND st.ConfigOptionTypePropertyId = EXCLUTION_SET.ConfigOptionTypePropertyId
WHERE EXCLUTION_SET.FKGUID IS NULL;
November 14, 2016 at 9:46 am
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.
November 14, 2016 at 9:51 am
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.
November 14, 2016 at 9:57 am
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.
Definitely better than nothing, feels at the moment like hunting black ghosts down in an unlit coal mine.
😎
November 14, 2016 at 11:05 am
A few pointers in here too:
DECLARE @MergedRecord INT = 0
IF EXISTS ( -- this subquery isn't correlated - it returns a CONSTANT
SELECT 1
FROM ETLCorrelation
WHERE DestinationTable = 'ConfigOptionTypeProperty'
AND MergedRecord = 1
AND BatchID = 6283)
SET @MergedRecord = 1
IF @MergedRecord = 1
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] ([Active], [ConfigOptionGUID], [ConfigOptionTypePropertyId], [ConfigOptionValue], [FKGUID] ,ImportIdentity, GUIDIdentity)
SELECT st.[Active], st.[ConfigOptionGUID], FK1.DestinationID, st.[ConfigOptionValue], st.[FKGUID], st.ConfigOptionID, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
-- this doesn't appear to be used
-- LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] as FK1Table
-- on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
INNER JOIN [ETL].[dbo].[ETLCorrelation] FK1
on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND FK1.SourceID <> FK1.DestinationID
WHERE NOT EXISTS (
SELECT 1
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] co
WHERE co.ConfigOptionTypePropertyId = st.ConfigOptionTypePropertyId
AND co.FKGUID = st.FKGUID
)
IF @MergedRecord = 0
INSERT INTO [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] ([Active], [ConfigOptionGUID], [ConfigOptionTypePropertyId], [ConfigOptionValue], [FKGUID] ,ImportIdentity, GUIDIdentity)
SELECT st.[Active], st.[ConfigOptionGUID], FK1.DestinationID, st.[ConfigOptionValue], st.[FKGUID], st.ConfigOptionID, '0000'
FROM [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOption] st
-- this doesn't appear to be used
-- LEFT JOIN [addb20].[Source_MergeAMD3_WithData].[dbo].[ConfigOptionTypeProperty] as FK1Table
-- on FK1Table.ConfigOptionTypePropertyID = st.ConfigOptionTypePropertyId
INNER JOIN [ETL].[dbo].[ETLCorrelation] FK1
on FK1.SourceID = ST.ConfigOptionTypePropertyId
AND FK1.SourceTable = 'ConfigOptionTypeProperty'
AND FK1.BatchID = 6283
AND FK1.SourceID = FK1.DestinationID
WHERE NOT EXISTS (
SELECT 1
FROM [addb20].[Target_MergeAMD4_WithData].[dbo].[ConfigOption] co
WHERE co.ConfigOptionTypePropertyId = st.ConfigOptionTypePropertyId
AND co.FKGUID = st.FKGUID
)
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
November 15, 2016 at 8:46 am
Why do the JOIN clauses seem weird and most useless?
And thank you for the suggestion I am about to test this. The statements are dynamically generated so this will take some time but I will let you know how it turns out.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 15, 2016 at 8:48 am
Would love to. In fact I would kind of like to see it too!! 🙂 I could likely fix this if I did.
The query just runs indefinitely so the Execution plan never materializes.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
November 15, 2016 at 8:51 am
Jeffery Williams (11/15/2016)
Would love to. In fact I would kind of like to see it too!! 🙂 I could likely fix this if I did.The query just runs indefinitely so the Execution plan never materializes.
Have you checked the dynamic management views?
😎
November 15, 2016 at 9:04 am
Jeffery Williams (11/15/2016)
Why do the JOIN clauses seem weird and most useless?And thank you for the suggestion I am about to test this. The statements are dynamically generated so this will take some time but I will let you know how it turns out.
Because the subqueries are not correlated, so there's additional work in there. They also seem to cover all cases, but I'm just making assumptions and can't be sure without some data.
Why are the statements dynamically generated? If the query is prone to be slow, you should avoid that as it might create non-optimal code.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply