June 22, 2018 at 4:57 pm
Morning Guys,
The following query is complaining about having no join predicate on a nested loop
EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?
SELECT DISTINCT
Wizprog
,rar.RequestID
,rar.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN [A00].[ReferralAnonymousRequest] rar
ON rar.ContractREFERENCE = xcand.WIZPROG
AND (rar.Processed IS NULL OR rar.Processed = 1)
AND AnonymiseIA = 1
WHERE pg.REFERENCE = 23423
My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?
Cheers
Alex
June 25, 2018 at 6:33 am
How do you like working with Adapt?
June 25, 2018 at 6:39 am
alex.sqldba - Friday, June 22, 2018 4:57 PMMorning Guys,The following query is complaining about having no join predicate on a nested loop
EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?
SELECT DISTINCT
Wizprog
,rar.RequestID
,rar.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN [A00].[ReferralAnonymousRequest] rar
ON rar.ContractREFERENCE = xcand.WIZPROG
AND (rar.Processed IS NULL OR rar.Processed = 1)
AND AnonymiseIA = 1
WHERE pg.REFERENCE = 23423My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?
Cheers
Alex
Are any of the objects in the FROM clause views rather than tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2018 at 6:42 am
I know a good portion of your schema, but obviously not all of it. I've seen this warning pop up in unexpected places in some of my queries. Is rar.ContractREFERENCE the primary key on [A00].[ReferralAnonymousRequest]?
June 25, 2018 at 7:31 am
heb1014 - Monday, June 25, 2018 6:33 AMHow do you like working with Adapt?
What is "Adapt" and how do you know it's in use here?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2018 at 7:34 am
It's a recruiting/staffing application. I know the schema 😉
June 25, 2018 at 8:32 am
heb1014 - Monday, June 25, 2018 7:34 AMIt's a recruiting/staffing application. I know the schema 😉
Got it. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2018 at 9:53 am
alex.sqldba - Friday, June 22, 2018 4:57 PMMorning Guys,The following query is complaining about having no join predicate on a nested loop
EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?
SELECT DISTINCT
Wizprog
,rar.RequestID
,rar.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN [A00].[ReferralAnonymousRequest] rar
ON rar.ContractREFERENCE = xcand.WIZPROG
AND (rar.Processed IS NULL OR rar.Processed = 1)
AND AnonymiseIA = 1
WHERE pg.REFERENCE = 23423My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?
Cheers
Alex
It may be nothing to worry about, sometimes SQL Server actually can resolve the join as a filter on the source tables as described in this blog post:
https://www.brentozar.com/archive/2018/03/certainly-join-predicate/
So check what the predicate is on the Seek in your execution plan.
June 26, 2018 at 6:49 am
Jeff Moden - Monday, June 25, 2018 6:39 AMalex.sqldba - Friday, June 22, 2018 4:57 PMMorning Guys,The following query is complaining about having no join predicate on a nested loop
EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?
SELECT DISTINCT
Wizprog
,rar.RequestID
,rar.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN [A00].[ReferralAnonymousRequest] rar
ON rar.ContractREFERENCE = xcand.WIZPROG
AND (rar.Processed IS NULL OR rar.Processed = 1)
AND AnonymiseIA = 1
WHERE pg.REFERENCE = 23423My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?
Cheers
AlexAre any of the objects in the FROM clause views rather than tables?
+1,000 to that.... It seems rather likely... I have yet to see such a warning appear without there being a view involved when the query itself offers no realistic reason for such a warning.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2018 at 8:53 am
Chris Harshman - Monday, June 25, 2018 9:53 AMalex.sqldba - Friday, June 22, 2018 4:57 PMMorning Guys,The following query is complaining about having no join predicate on a nested loop
EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?
SELECT DISTINCT
Wizprog
,rar.RequestID
,rar.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN [A00].[ReferralAnonymousRequest] rar
ON rar.ContractREFERENCE = xcand.WIZPROG
AND (rar.Processed IS NULL OR rar.Processed = 1)
AND AnonymiseIA = 1
WHERE pg.REFERENCE = 23423My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?
Cheers
AlexIt may be nothing to worry about, sometimes SQL Server actually can resolve the join as a filter on the source tables as described in this blog post:
https://www.brentozar.com/archive/2018/03/certainly-join-predicate/
So check what the predicate is on the Seek in your execution plan.
Interesting. I'm amazed that there are execution plans available to begin with and that there are people that come up with the repeatable demonstrable code to explain the anomalies.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2018 at 5:45 pm
heb1014 - Monday, June 25, 2018 6:33 AMHow do you like working with Adapt?
Hi Tom,
Wow, I am pleased (whilst feeling a bit sorry for you) someone recognised that snippet!
You don't work at Bond/Adapt do you? I hope not. How do I like working with it? I don't. I absolutely abhor it. I find their centralised table of running ID's an utter shambles; their atrocious way of storing data in generic fields horrid.
Normally, I am not too bothered about naming conventions and prefixes, but I think at the very least in a database you should denote when a view is a view and not make a 45 table join query LOOK like a table. Because that bites.
Oh, and security handled by a comma separated list of values stored in a varchar(max) field. Really?!
Oh no one last beauty. Let's save text data as BLOB. Twice.
I don't know drugs they were on but it must have been some strong stuff.
The only saving grace is we're on an inherited very very old version. That is in the process of being decommissioned. But it's a slow process and GDPR is making it slower.
October 2, 2019 at 4:28 pm
This was removed by the editor as SPAM
October 2, 2019 at 4:31 pm
This was removed by the editor as SPAM
October 2, 2019 at 4:38 pm
Hey Alex. Can you give me the DDL for dbo.PROP_X_CAND_AVAIL and [A00].[ReferralAnonymousRequest]?
October 2, 2019 at 4:44 pm
Hi there, I know I'm late to the party but I've just come across this having had the same trouble myself with this code snippet (its an inner subquery from a much longer query) taking around 4 minutes to execute with a No Join Predicate warning
select si.* from (
select i.BillingRunId,i.RecId,i.Created,i.Quantity,i.ServiceLink_RecID,
case
when c.OwnerLinkRecid is null then i.EmployeeLink_RecID
else c.OwnerLinkRecid
end EmployeeLink_RecID,
i.TariffLink_RecID,i.CILink_RecID,i.OrgUnitLink_RecID,i.DepartmentChargeCode
from Billing.ServiceInstances i
left join Billing.CIs c
on i.CILink_RecID = c.recid and i.BillingRunId = c.BillingRunId) si
inner join Billing.Employees em on si.EmployeeLink_RecId = em.RecId and si.BillingRunId = em.BillingRunId
where si.billingrunid = 286
I then tried changing the CASE expression to a condition in the join onto the employees table with no joy - still impaired performance and No Join Predicate warning
select si.* from (
select i.BillingRunId,i.RecId,i.Created,i.Quantity,i.ServiceLink_RecID,
c.OwnerLinkRecid,
i.EmployeeLink_RecID,
i.TariffLink_RecID,i.CILink_RecID,i.OrgUnitLink_RecID,i.DepartmentChargeCode
from Billing.ServiceInstances i
left join Billing.CIs c
on i.CILink_RecID = c.recid and i.BillingRunId = c.BillingRunId) si
inner join Billing.Employees em
on si.BillingRunId = em.BillingRunId
and (
(em.RecId = si.[EmployeeLink_RecID] and si.[OwnerLinkRecid] is null)
or
(em.RecId = si.[OwnerLinkRecid] and si.[OwnerLinkRecid] is not null)
)
where si.billingrunid = 286
So I thought well if its complaining about the join let's replace it with a union and Eureka! Query now runs sub 1 second and no errors in the query plan
select si.* from (
select i.BillingRunId,i.RecId,i.Created,i.Quantity,i.ServiceLink_RecID,
c.OwnerLinkRecid EmployeeLink_RecID
,i.TariffLink_RecID,i.CILink_RecID,i.OrgUnitLink_RecID,i.DepartmentChargeCode
from Billing.ServiceInstances i
inner join Billing.CIs c
on i.CILink_RecID = c.recid and i.BillingRunId = c.BillingRunId
where c.OwnerLinkRecid is not null
union all
select i.BillingRunId,i.RecId,i.Created,i.Quantity,i.ServiceLink_RecID,
i.EmployeeLink_RecID,
i.TariffLink_RecID,i.CILink_RecID,i.OrgUnitLink_RecID,i.DepartmentChargeCode
from Billing.ServiceInstances i
left join Billing.CIs c
on i.CILink_RecID = c.recid and i.BillingRunId = c.BillingRunId
where c.OwnerLinkRecid is null) si
inner join Billing.Employees em on si.EmployeeLink_RecId = em.RecId and si.BillingRunId = em.BillingRunId
where si.billingrunid = 286
So perhaps you could change your conditional join to union? Something like
SELECT DISTINCT
Wizprog
,a.RequestID
,a.Processed
FROM dbo.PROP_X_CAND_WP xcand
JOIN dbo.PROP_PERSON_GEN pg
ON pg.REFERENCE = xcand.CANDIDATE
JOIN ENTITY_TABLE et
ON et.ENTITY_ID = xcand.WIZPROG
AND et.STATUS ='Y'
LEFT JOIN (
select rar.RequestID, rar.Processed, rar.ContractREFERENCE, AnonymiseIA from
[A00].[ReferralAnonymousRequest] rar
where rar.Processed is null
union all
select rar.RequestID, rar.Processed, rar.ContractREFERENCE, AnonymiseIA from
[A00].[ReferralAnonymousRequest] rar
where rar.Processed = 1
) a
ON a.ContractREFERENCE = xcand.WIZPROG
AND a.AnonymiseIA = 1
WHERE pg.REFERENCE = 23423
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply