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
Thanks a million,
Mike