July 5, 2011 at 4:53 am
Hi everybody,
since this is my first post here i want to say thank you for delivering such great content every day and making this community such a valuable resource.
I have a problem with a query that is running slow and taking a lot of CPU Time while beeig executed. Since i cant state the original queries and tables i have recreated a similar buildup to show what i mean.
The scenario is, i have a product table... the products are shipped into different countries in different languages and so on. There are different versions of the product. The different versions can be valid for different countrys and they can have other descriptions than in the main product description.
To see all the languages of the description there are multiple joins on the language tables. I think that is a lot of unnecessary reads but i don't know how to reduce them (maybe its not even possible).
Here are the exampletables and the example Query:
/****** Object: Table [dbo].[Product] Script Date: 07/04/2011 11:20:06 ******/
CREATE TABLE [dbo].[Product](
[ProductId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[BuyerNo] [int] NULL,
[CountryId] [smallint] NOT NULL,
[BuserAst] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY NONCLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProductLng] Script Date: 07/04/2011 11:20:06 ******/
CREATE TABLE [dbo].[ProductLng](
[ProductId] [uniqueidentifier] NOT NULL,
[LanguageId] [smallint] NOT NULL,
[Description] [nchar](50) NOT NULL,
CONSTRAINT [PK_ProductLng] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProdVersion] Script Date: 07/04/2011 11:20:06 ******/
CREATE TABLE [dbo].[ProdVersion](
[ProductId] [uniqueidentifier] NOT NULL,
[VersionID] [int] NOT NULL,
[ValidForCountry] [int] NOT NULL,
CONSTRAINT [PK_ProdVersion] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[VersionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProdVersionLng] Script Date: 07/04/2011 11:20:06 ******/
CREATE TABLE [dbo].[ProdVersionLng](
[ProductId] [uniqueidentifier] NOT NULL,
[VersionId] [int] NOT NULL,
[RegionId] [smallint] NOT NULL,
[LanguageId] [int] NOT NULL,
[Description] [varchar](50) NULL,
[Name] [varchar](10) NULL,
CONSTRAINT [PK_ProdVersionLng] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[VersionId] ASC,
[RegionId] ASC,
[LanguageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ProdVersionReg] Script Date: 07/04/2011 11:20:06 ******/
CREATE TABLE [dbo].[ProdVersionReg](
[ProductId] [uniqueidentifier] NOT NULL,
[VersionId] [int] NOT NULL,
[RegionId] [smallint] NOT NULL,
[BuyerNo] [int] NULL,
[RegInfo] [nchar](10) NULL,
CONSTRAINT [PK_ProdVersionReg] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[VersionId] ASC,
[RegionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
And the Query:
Select Distinct
p.ProductId,
p.BuyerNo AS IntBuyer,
P.BuserAst AS IntBuyerAst,
ProductCurrentLng.Description AS ProdDesc,
ProductIntlLng.Description AS IntlDesc,
ProdVersion.VersionID,
PVR2.BuyerNo as BuyerReg2,
PVR3.BuyerNo as BuyerReg3,
PVR4.BuyerNo as BuyerReg4,
PVR6.BuyerNo as BuyerReg6,
PVLCur.Description as CurVerDesc,
PVLInt.Description as IntlVerDesc
from
dbo.Product P
LEFT OUTER JOIN dbo.ProductLng as ProductCurrentLng on p.ProductId = ProductCurrentLng.ProductId and ProductCurrentLng.LanguageId = 1
LEFT OUTER JOIN dbo.ProductLng as ProductIntlLng on p.ProductId = ProductIntlLng.ProductId and ProductIntlLng.LanguageId = 0
LEFT OUTER JOIN dbo.ProdVersion on p.ProductId = ProdVersion.ProductId
LEFT OUTER JOIN dbo.ProdVersionReg as PVR2 on ProdVersion.ProductId = PVR2.ProductId AND ProdVersion.VersionID = PVR2.VersionId AND PVR2.RegionID=2
LEFT OUTER JOIN dbo.ProdVersionReg as PVR3 on ProdVersion.ProductId = PVR3.ProductId AND ProdVersion.VersionID = PVR2.VersionId AND PVR3.RegionID=3
LEFT OUTER JOIN dbo.ProdVersionReg as PVR4 on ProdVersion.ProductId = PVR4.ProductId AND ProdVersion.VersionID = PVR4.VersionId AND PVR4.RegionID=4
LEFT OUTER JOIN dbo.ProdVersionReg as PVR6 on ProdVersion.ProductId = PVR6.ProductId AND ProdVersion.VersionID = PVR6.VersionId AND PVR6.RegionID=6
LEFT OUTER JOIN dbo.ProdVersionLng as PVLCur on ProdVersion.ProductId = PVLCur.ProductId AND ProdVersion.VersionID = PVLCur.VersionId AND PVLCur.LanguageId = 1
LEFT OUTER JOIN dbo.ProdVersionLng as PVLInt on ProdVersion.ProductId = PVLInt.ProductId AND ProdVersion.VersionID = PVLInt.VersionId AND PVLInt.LanguageId = 0
Thank you in advance for your help!
July 5, 2011 at 6:01 am
1. Pivoting will reduce joins
2. Try to get rid of the distinct if possible.
3. Try to make some of the outer joins inner joins.
You could start with the following, which has not been tested due to lack of test data.
;WITH PL
AS
(
SELECT ProductId, [0] AS IntlDesc, [1] AS ProdDesc
FROM
(
SELECT ProductId, LanguageId, [Description]
FROM dbo.ProductLng
) AS S
PIVOT
(
MAX([Description])
FOR LanguageId IN ([0], [1])
) AS P
)
, PVR
AS
(
SELECT ProductId, VersionID, [2] AS BuyerReg2, [3] AS BuyerReg3, [4] AS BuyerReg4, [6] AS BuyerReg6
FROM
(
SELECT ProductId, VersionID, RegionID, BuyerNo
FROM dbo.ProdVersionReg
) AS S
PIVOT
(
MAX(BuyerNo)
FOR RegionID IN ([2], [3], [4], [6])
) AS P
)
, PVL
AS
(
SELECT ProductId, VersionID, [0] AS IntlVerDesc, [1] AS CurVerDesc
FROM
(
SELECT ProductId, VersionID, LanguageId, [Description]
FROM dbo.ProdVersionLng
) AS S
PIVOT
(
MAX([Description])
FOR LanguageId IN ([0], [1])
) AS P
)
SELECT
P.ProductId
,P.BuyerNo AS IntBuyer
,P.BuserAst AS IntBuyerAst
,PL.ProdDesc
,PL.IntlDesc
,PV.VersionID
,PVR.BuyerReg2
,PVR.BuyerReg3
,PVR.BuyerReg4
,PVR.BuyerReg6
,PVL.CurVerDesc
,PVL.IntlVerDesc
FROM dbo.Product P
LEFT JOIN PL
ON P.ProductId = PL.ProductId
LEFT JOIN dbo.ProdVersion PV
ON P.ProductId = PV.ProductId
LEFT JOIN PVR
ON PV.ProductId = PVR.ProductId
AND PV.VersionID = PVR.VersionID
LEFT JOIN PVL
ON PV.ProductId = PVL.ProductId
AND PV.VersionID = PVL.VersionID
July 6, 2011 at 2:52 am
Thank you very much for your reply and sorry for my late one.
I am trying it right away and let you know if it works!
July 8, 2011 at 12:12 am
Hi Ken,
your solution worked really good. I was able to rewrite the query and reduce the runtime by a couple of minutes!
Thank you very much.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply