January 26, 2016 at 5:40 pm
I'm having problem with this query when changing the value on cat.id for different product category on company's webpage. it is timing out but only for one product category other load up fine. I have checked the query plan and index. All seem fine. Any help would be appreciated
SELECT Prod.*, ProdInfo.*,
dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,
dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice
FROM NetPortal_Ecommerce_Products Prod
LEFT JOIN NetPortal_Ecommerce_Products_Info ProdInfo
ON Prod.ID = ProdInfo.ProductId
INNER JOIN NetPortal_Ecommerce_ProductCategory_Link CatLink
ON Prod.ID = CatLink.ProductId
INNER JOIN NetPortal_Ecommerce_Categories Cat
ON Cat.ID = CatLink.CategoryId
WHERE Cat.IsProductGroup = 0
AND Cat.ID = 1329
AND (Prod.AvailableDate IS NULL OR Prod.AvailableDate <=getdate())
AND (Prod.ExpDate IS NULL OR Prod.ExpDate>=getdate())
AND Prod.Active = 1 ORDER BY CatLink.OrderId
January 26, 2016 at 6:22 pm
This at least will be causing you problems
dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,
dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice
You should also check out the catch all query blog here
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Im sure other people will be able to give you a better breakdown of what is happening.
January 27, 2016 at 1:43 am
ajnubee (1/26/2016)
I have checked the query plan and index. All seem fine. Any help would be appreciated
Well, since the performance is bad they apparently are not as fine as they seem. But you didn't share them with us so how do you expect us to help?
Please post CREATE TABLE statements for all tables involved, including all constraints and indexes; an indication of the number of rows in each table; the code for the user-defined procedures used in the query (which as Matak indicates are a well-known code smell, but not necessarily the cause of your specific issue); and the actual execution plans for both a fast and a slow execution.
January 27, 2016 at 5:57 am
The function dbo.NetPortal_GetProductPrice is the obvious one, so please include the DDL for it in addition to what Hugo asked for.
January 28, 2016 at 11:20 am
try this:
SELECT
Prod.*,
ProdInfo.*,
dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,
dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice
FROM NetPortal_Ecommerce_Categories Cat
INNER JOIN NetPortal_Ecommerce_ProductCategory_Link CatLink ON
CatLink.CategoryId = Cat.ID
INNER JOIN NetPortal_Ecommerce_Products Prod ON
Prod.ID = CatLink.ProductId
AND (Prod.AvailableDate IS NULL OR Prod.AvailableDate <=getdate())
AND (Prod.ExpDate IS NULL OR Prod.ExpDate>=getdate())
AND Prod.Active = 1 ORDER BY CatLink.OrderId
LEFT JOIN NetPortal_Ecommerce_Products_Info ProdInfo ON
ProdInfo.ProductId = Prod.ID
WHERE Cat.IsProductGroup = 0
AND Cat.ID = 1329
January 28, 2016 at 11:25 am
Thank You all, we changed the function how it was calculating the discount price for particular products in select statement
This is Old Function
USE [NetPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <2010/08/09>
-- =============================================
ALTER FUNCTION [dbo].[NetPortal_GetProductPrice]
(
@ProductCodevarchar(24),
@RateStructure varchar(24),--MEMBER or LIST
@Subsystem varchar(10) = null
)
RETURNS numeric(12,2)
AS
begin
declare@CurrentPricenumeric(12,2)
set @CurrentPrice = 0
if @Subsystem = 'PCK'
Begin
select @CurrentPrice = SUM(gppv.PRICE) - SUM(gppv.PRICE)*(select p2.PACKAGE_DISCOUNT_PCT/100
from AMS.ppro.dbo.[PRODUCT] p2 (nolock)
where p2.PRODUCT_CODE=p.product_code)
from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv
join AMS.ppro.dbo.PRODUCT_COMPONENT pc (nolock)
on gppv.product_id = pc.COMPONENT_PRODUCT_ID
join AMS.ppro.dbo.product p (nolock)
on p.product_id = pc.product_id
where p.product_code = @ProductCode
AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())
and gppv.RATE_STRUCTURE = isnull(@RateStructure,gppv.RATE_STRUCTURE)
and gppv.RATE_CODE = 'STD'
group by p.product_code
End
else
Begin
Select @CurrentPrice = PRICE
from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv
Where gppv.Product_Code = @ProductCode
AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())
AND gppv.RATE_STRUCTURE = isnull(@RateStructure,RATE_STRUCTURE)
and gppv.RATE_CODE = 'STD'
order by PRICE_BEGIN_DATE
End
if @@rowcount = 0 and @RateStructure = 'MEMBER'
BEGIN
return dbo.DinfoPortal_GetProductPrice(@ProductCode,'LIST', null)
END
return @CurrentPrice
END
January 28, 2016 at 11:26 am
whoops I did not move the order by.
SELECT
Prod.*,
ProdInfo.*,
dbo.NetPortal_GetProductPrice(externalId,'LIST', ProdInfo.PPRO_SubSystem) ListPrice,
dbo.NetPortal_GetProductPrice(externalId,'MEMBER', ProdInfo.PPRO_SubSystem) MemberPrice
FROM NetPortal_Ecommerce_Categories Cat
INNER JOIN NetPortal_Ecommerce_ProductCategory_Link CatLink ON
CatLink.CategoryId = Cat.ID
INNER JOIN NetPortal_Ecommerce_Products Prod ON
Prod.ID = CatLink.ProductId
AND (Prod.AvailableDate IS NULL OR Prod.AvailableDate <=getdate())
AND (Prod.ExpDate IS NULL OR Prod.ExpDate>=getdate())
AND Prod.Active = 1
LEFT JOIN NetPortal_Ecommerce_Products_Info ProdInfo ON
ProdInfo.ProductId = Prod.ID
WHERE Cat.IsProductGroup = 0
AND Cat.ID = 1329
ORDER BY CatLink.OrderId
January 28, 2016 at 11:27 am
Modified version works like a charm :-):-):-)
USE [NetPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: <2010/08/09>
-- Modify Date: <2016/01/27>
-- =============================================
ALTER FUNCTION [dbo].[NetPortal_GetProductPrice]
(
@ProductCodevarchar(24),
@RateStructure varchar(24),--MEMBER or LIST
@Subsystem varchar(10) = null
)
RETURNS numeric(12,2)
AS
begin
declare@CurrentPricenumeric(12,2)
set @CurrentPrice = 0
if @Subsystem = 'PCK'
Begin
declare @bundlesum numeric(12,2)
select @bundlesum = SUM(gppv.PRICE)
from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv
join AMS.ppro.dbo.PRODUCT_COMPONENT pc (nolock)
on gppv.product_id = pc.COMPONENT_PRODUCT_ID
join AMS.ppro.dbo.product p (nolock)
on p.product_id = pc.product_id
where p.product_code = @ProductCode
AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())
and gppv.RATE_STRUCTURE = isnull(@RateStructure,gppv.RATE_STRUCTURE)
and gppv.RATE_CODE = 'STD'
group by p.product_code
select @CurrentPrice= @bundlesum - @bundlesum*p2.PACKAGE_DISCOUNT_PCT/100
from AMS.ppro.dbo.[PRODUCT] p2 (nolock)
where p2.PRODUCT_CODE=@productcode
End
else
Begin
Select @CurrentPrice = PRICE
from AMS.ppro.dbo.[AHIMA_GetProductPricingView] gppv
Where gppv.Product_Code = @ProductCode
AND getdate() between gppv.PRICE_BEGIN_DATE and ISNULL(gppv.PRICE_END_DATE,getdate())
AND gppv.RATE_STRUCTURE = isnull(@RateStructure,RATE_STRUCTURE)
and gppv.RATE_CODE = 'STD'
order by PRICE_BEGIN_DATE
End
if @@rowcount = 0 and @RateStructure = 'MEMBER'
BEGIN
return dbo.DinfoPortal_GetProductPrice(@ProductCode,'LIST', null)
END
return @CurrentPrice
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply