No join predicate has been detected - help

Transact-SQL
1

Hi all,

Any help would be greatly appreciated.

A software told me that query messing up performance

Error:
No join predicate has been detected

The search condition for a joined table is missing in the either the ON clause or the FROM clause. This could result in the query running longer than necessary.

Recommendation:
It is recommended that you add the search condition to either the ON clause or the FROM clause for all joined tables.

SQL:

select name,Price ,Image, down, Stock, id, url, oldprice,quant,CategoryID,lang from V_Product_category_new
WHERE (CategoryID = @CategoryID) AND
(Lang = @lang) AND (Country <> @country)
GROUP BY id, Name, price, Image, down, stock,url,oldprice,quant,CategoryID,lang

    order by    quant desc,stock desc

Same error:

SQL:

CREATE PROCEDURE dbo.SP_Product_Invoice
(
@Lang nvarchar(2),
@OrderID int
)
AS
SELECT T_Order_Detail.Quantity, T_Product_Option.id, T_Product_Option.Quantity As OptionValue,T_Product_Description.Type, T_Order_Detail.Quantity * T_Order_Detail.Cost AS total,
T_Order_Detail.OptionID, T_Order_Detail.Cost, T_Product_Main.Tax, T_Order_Detail.OrderID, T_Product_Description.Name,
T_Order_Ship.shipping
FROM T_Product_Main INNER JOIN
T_Product_Description ON T_Product_Main.id = T_Product_Description.ProductID INNER JOIN
T_Product_Option ON T_Product_Main.id = T_Product_Option.ProductID INNER JOIN
T_Order_Detail ON T_Product_Option.id = T_Order_Detail.OptionID INNER JOIN
T_Order_Ship ON T_Order_Detail.OrderID = T_Order_Ship.OrderID
WHERE (T_Product_Description.Lang = @Lang) AND (T_Order_Detail.OrderID = @OrderID)

Error:

The function "PROBE" on column [dev_shayana].[dbo].[T_Order_Detail].[OrderID] may be causing a table scan

The function or expression "PROBE" in a query in SP_Customer_AlsoBought from .Net SqlClient Data Provider in [dev_shayana] submitted from WEB02 by sa can cause index suppression resulting in poor performance due to a scan being performed instead of a seek.

Recommendation:
It is recommended that you consider redesigning this WHERE clause to prevent index suppression and performance degradation.

Note: This recommendation can be caused from T-SQL that is nested in a function, view or stored procedure.

SQL: (huge mess probabbly)

ALTER PROCEDURE [dbo].[SP_Customer_AlsoBought]
(
@ProductID int,
@lang varchar(2),
@country varchar(50)
)
WITH RECOMPILE
AS

declare @counter AS int =0

SELECT @counter=COUNT(*)
FROM (SELECT DISTINCT ProductID
FROM (SELECT TOP (50) T_Order_Detail.ProductID
FROM V_productid_orderid_shipped FULL OUTER JOIN
T_Order_Detail ON V_productid_orderid_shipped.ORDERID = T_Order_Detail.OrderID
WHERE (V_productid_orderid_shipped.ProductID = @ProductID)
ORDER BY V_productid_orderid_shipped.ORDERID DESC) AS DERIVEDTBL_1) AS DERIVEDTBL INNER JOIN
T_Product_Description ON DERIVEDTBL.ProductID = T_Product_Description.ProductID INNER JOIN
T_Product_Main ON DERIVEDTBL.ProductID = T_Product_Main.id
WHERE (T_Product_Description.Lang = @lang) AND (DERIVEDTBL.ProductID <> @ProductID) AND (NOT (DERIVEDTBL.ProductID IN
(SELECT Productid
FROM T_Product_Hide
WHERE (Country = @country))))

IF @counter>0

SELECT TOP (4) DERIVEDTBL.ProductID, T_Product_Description.Name AS name, T_Product_Main.Image
FROM (SELECT DISTINCT ProductID
FROM (SELECT TOP (50) T_Order_Detail.ProductID
FROM V_productid_orderid_shipped FULL OUTER JOIN
T_Order_Detail ON V_productid_orderid_shipped.ORDERID = T_Order_Detail.OrderID
WHERE (V_productid_orderid_shipped.ProductID = @ProductID)
ORDER BY V_productid_orderid_shipped.ORDERID DESC) AS DERIVEDTBL_1) AS DERIVEDTBL INNER JOIN
T_Product_Description ON DERIVEDTBL.ProductID = T_Product_Description.ProductID INNER JOIN
T_Product_Main ON DERIVEDTBL.ProductID = T_Product_Main.id
WHERE (T_Product_Description.Lang = @lang) AND (DERIVEDTBL.ProductID <> @ProductID) AND (NOT (DERIVEDTBL.ProductID IN
(SELECT Productid
FROM T_Product_Hide
WHERE (Country = @country))))

        ELSE

             SELECT top 7  V_Top10_mostsold.ProductID, T_Product_Description.Name, T_Product_Main.Image

FROM V_Top10_mostsold INNER JOIN
T_Product_Description ON V_Top10_mostsold.ProductID = T_Product_Description.ProductID INNER JOIN
T_Product_Main ON T_Product_Description.ProductID = T_Product_Main.id
WHERE (NOT (V_Top10_mostsold.ProductID IN
(SELECT Productid
FROM T_Product_Hide
WHERE (Country = @country)))) AND (T_Product_Description.Lang = @lang)
ORDER BY V_Top10_mostsold.quant DESC

aah if someone has time to support me on skype (couple of days a month), of course you will be payed :wink:

Thanks a million,

Mike

2

I see no function call to PROBE in any of the queries you posted. Where is that coming from?[quote="mike13, post:1, topic:5092"]
A software told me that query messing up performance
[/quote]
What software, specifically (Name and version info please)

3

Software that dedected the problem is SQL Doctor from Idera

The DP are my own

thank

4

Ok, though I still don't see a call to a function called PROBE. Pretty hard to help when I can't see the whole query!

5

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

No join predicate has been detected <<

That is fine; you will get a CROSS JOIN or Cartesian product. But this is usually an error known as a Cartesian explosion.

What little posted is a total mess of vague generic data elements names with irregular capitalization to make it a bitch to read.

There is no such crap as generic id in RDBMS! But old COBOL and Autocoder programmers use it for IDENTITY to get a physical rerecord number replacement.

I also see that you put the search conditions in redundant parentheses, so you must be an old Assembly language programmer. No SQL programmer writes code like that.

There is no “category_id” in RDBMS; an attribute can be “product_category” or “product_id”, but not a hybrid of two attribute properties! This was the first week of any class on data modeling!

But it gets worse. Your “V_Product_category_new” looks like the “V_” means VIEW, but we do not put meta-data in a table name. The postfix “new” looks like a role this view plays in the data model. But that would be a prefix in ISO-11179 naming conventions. That would get us “New_Product_Categories” as a table. But since a category is an attribute, would be a simple look up table or a column constraint
“CHECK (product_category IN (..))” in a valid schema design.

The rest of this awful. I will guess that your “language” and “country” are supposed to be the ISO Standard language_code and country_code. But who knows? I see that when you use it as a parmeter, it is NVARCHAR(2) in the improperly named “PROCEDURE dbo.SP_Product_Invoice”, so perhaps I guess right. But wait! The ISO country code is fixed length and uses only the ISO Latin subset that all the UNICODE languages have to support. Why did you waste space and throw away data integrity? Do you know that the “sp_” prefix on names means in T-SQL? Google it! What does this procedure do? Where is the verb? Print? Update? Delete?

I often think that there is a “screwup_” procedure in accounting systems:confused:

Finally, we do not math on product or order identifiers, so they are never numeric. The products ought to use a GRIN, EAN, UPC or other industry standard.

The function "PROBE" on column [dev_shayana].[dbo].[Order_Detail].[order_id] may be causing a table scan <<

Where is that function? Why do you have only one order detail? Oh, more 1970's COBOL programming with singular file names.

SQL: (huge mess probably) <<

No, it is certain. Might be time to update the resume and leave the sinking ship :scream: Sorry.

6

:frowning: i came for help.
i never claimed to be SQL programmer, i know its a HUGE mess. My reason to seek Help.
If i knew what you know i would be asking these questions. i'm a self tough asp, asp.net programmer.
I do no nothing about rules or standards, the few, like SP_ V_ i learned from colleagues 17 years a ago.

Okay let not spend energy focusing on the problem.

I will spend sometime and breaking down the the problems