Query Tuning Index question
-
Hi all
I have the following query
SELECT T1.ITEMID,T1.ITEMTYPE,
T1.PURCHMODEL,
T1.HEIGHT,
T1.WIDTH,
T1.SALESMODEL,
T1.COSTGROUPID,
T1.REQGROUPID,
T1.EPCMANAGER,
T1.PRIMARYVENDORID,
T1.NETWEIGHT,
T1.DEPTH,
T1.UNITVOLUME,
T1.BOMUNITID,
T1.ITEMPRICETOLERANCEGROUPID,
T1.DENSITY,
T1.COSTMODEL,
T1.USEALTITEMID,
T1.ALTITEMID,
T1.MATCHINGPOLICY,
T1.INTRACODE,
T1.PRODFLUSHINGPRINCIP,
T1.MINIMUMPALLETQUANTITY,
T1.PBAITEMAUTOGENERATED,
T1.WMSARRIVALHANDLINGTIME,
T1.BOMMANUALRECEIPT,
T1.PHANTOM,
T1.INTRAUNIT,
T1.BOMLEVEL,
T1.BATCHNUMGROUPID,
T1.AUTOREPORTFINISHED,
T1.ORIGCOUNTRYREGIONID,
T1.STATISTICSFACTOR,
T1.ALTCONFIGID,
T1.STANDARDCONFIGID,
T1.PRODPOOLID,
T1.PROPERTYID,
T1.ABCTIEUP,
T1.ABCREVENUE,
T1.ABCVALUE,
T1.ABCCONTRIBUTIONMARGIN,
T1.COMMISSIONGROUPID,
T1.SALESPERCENTMARKUP,
T1.SALESCONTRIBUTIONRATIO,
T1.SALESPRICEMODELBASIC,
T1.NAMEALIAS,
T1.PRODGROUPID,
T1.PROJCATEGORYID,
T1.GROSSDEPTH,
T1.GROSSWIDTH,
T1.GROSSHEIGHT,
T1.STANDARDPALLETQUANTITY,
T1.QTYPERLAYER,
T1.SORTCODE,
T1.SERIALNUMGROUPID,
T1.ITEMBUYERGROUPID,
T1.TAXPACKAGINGQTY,
T1.WMSPALLETTYPEID,
T1.ORIGSTATEID,
T1.WMSPICKINGQTYTIME,
T1.TARAWEIGHT,
T1.PACKAGINGGROUPID,
T1.SCRAPVAR,
T1.SCRAPCONST,
T1.ITEMDIMCOSTPRICE,
T1.FORECASTDMPINCLUDE,
T1.PRODUCT,
T1.PALLETTAGGING,
T1.ITEMTAGGINGLEVEL,
T1.DEFAULTDIMENSION,
T1.BOMCALCGROUPID,
T1.PBAITEMCONFIGURABLE,
T1.PBAINVENTITEMGROUPID,
T1.PBAHIDEDIALOG,
T1.PBAHIDEAPPROVAL,
T1.PBAAUTOSTART,
T1.PBAMANDATORYCONFIG,
T1.PDSFREIGHTALLOCATIONGROUPID,
T1.PDSITEMREBATEGROUPID,
T1.PDSVENDORCHECKITEM,
T1.PMFPLANNINGITEMID,
T1.PMFPRODUCTTYPE,
T1.PMFYIELDPCT,
T1.BATCHMERGEDATECALCULATIONMETHOD,
T1.MODIFIEDDATETIME,
T1.DEL_MODIFIEDTIME,
T1.MODIFIEDBY,
T1.CREATEDDATETIME,
T1.DEL_CREATEDTIME,
T1.CREATEDBY,
T1.RECVERSION,
T1.PARTITION,
T1.RECID,
T2.UOMSEQGROUPID,
T2.MAXPICKQTY,
T2.ITEMID,
T2.RFDESCRIPTION1,
T2.RFDESCRIPTION2,
T2.PACKSIZECATEOGRYID,
T2.FILTERCODE,
T2.FILTERCODE2_,
T2.FILTERCODE3_,
T2.FILTERCODE4_,
T2.FILTERGROUP,
T2.FILTERGROUP2_,
T2.FILTERCHANGED,
T2.PRODQTY,
T2.PHYSDIMID,
T2.PACKAGECLASSID,
T2.PICKWCNEG,
T2.MODIFIEDDATETIME,
T2.MODIFIEDBY,
T2.RECVERSION,
T2.PARTITION,
T2.RECID,
T3.ITEMDATAAREAID,
T3.MODELGROUPID,
T3.ITEMID,
T3.MODELGROUPDATAAREAID,
T3.RECVERSION,
T3.PARTITION,
T3.RECID,
T4.ITEMID,
T4.ITEMDATAAREAID,
T4.ITEMGROUPID,
T4.ITEMGROUPDATAAREAID,
T4.RECVERSION,
T4.PARTITION,
T4.RECID,
T5.TRACKINGDIMENSIONGROUP,
T5.ITEMID,
T5.ITEMDATAAREAID,
T5.RECVERSION,
T5.PARTITION,
T5.RECID,
T6.STORAGEDIMENSIONGROUP,
T6.ITEMID,
T6.ITEMDATAAREAID,
T6.RECVERSION,
T6.PARTITION,
T6.RECID,
T7.PRODUCTMASTER,
T7.RETAITOTALWEIGHT,
T7.VARIANTCONFIGURATIONTECHNOLOGY,
T7.INSTANCERELATIONTYPE,
T7.DISPLAYPRODUCTNUMBER,
T7.SEARCHNAME,
T7.PRODUCTTYPE,
T7.MODIFIEDBY,
T7.RECVERSION,
T7.RELATIONTYPE,
T7.PARTITION,
T7.RECID,
T8.DEFAULTSITE,
T8.EQUIVALENTFACTOR,
T8.FOESEARCHTEXT,
T8.ITEMID,
T8.ITEMPRICEGROUPID,
T8.ITEMSTATUS,
T8.MASTERITEMID,
T8.PACKSIZE,
T8.PURCH_MANDATORYFIXEDASSET,
T8.PURCH_SPECIFICTEXT,
T8.MODIFIEDDATETIME,
T8.CREATEDDATETIME,
T8.RECVERSION,
T8.PARTITION,
T8.RECID,
T9.PRODUCTDIMENSIONGROUP,
T9.RECVERSION,
T9.RECID,
T10.NAME,
T10.RECVERSION,
T10.RECID,
T11.NAME,
T11.RECVERSION,
T11.RECID
FROM INVENTTABLE T1 LEFT
OUTER
JOIN WHSINVENTTABLE T2 ON (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID='abr'))
AND (T1.ITEMID=T2.ITEMID)) LEFT
OUTER
JOIN INVENTMODELGROUPITEM T3 ON ((T3.PARTITION=5637144576)
AND ((T1.ITEMID=T3.ITEMID)
AND (T1.DATAAREAID=T3.ITEMDATAAREAID))) LEFT
OUTER
JOIN INVENTITEMGROUPITEM T4 ON ((T4.PARTITION=5637144576)
AND ((T1.ITEMID=T4.ITEMID)
AND (T1.DATAAREAID=T4.ITEMDATAAREAID))) LEFT
OUTER
JOIN ECORESTRACKINGDIMENSIONGROUPITEM T5 ON ((T5.PARTITION=5637144576)
AND ((T1.ITEMID=T5.ITEMID)
AND (T1.DATAAREAID=T5.ITEMDATAAREAID))) LEFT
OUTER
JOIN ECORESSTORAGEDIMENSIONGROUPITEM T6 ON ((T6.PARTITION=5637144576)
AND ((T1.ITEMID=T6.ITEMID)
AND (T1.DATAAREAID=T6.ITEMDATAAREAID))) CROSS
JOIN ECORESPRODUCT T7 LEFT
OUTER
JOIN MARINVENTTABLE T8 ON (((T8.PARTITION=5637144576)
AND (T8.DATAAREAID='abr'))
AND (T1.ITEMID=T8.ITEMID)) LEFT
OUTER
JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T9 ON ((T9.PARTITION=5637144576)
AND (T7.RECID=T9.PRODUCT)) LEFT
OUTER
JOIN ECORESPRODUCTTRANSLATION T10 ON ((T10.PARTITION=5637144576)
AND ((T10.LANGUAGEID='en-gb')
AND (T7.RECID=T10.PRODUCT))) LEFT
OUTER
JOIN ECORESPRODUCTDIMENSIONGROUP T11 ON ((T11.PARTITION=5637144576)
AND (T9.PRODUCTDIMENSIONGROUP=T11.RECID))
WHERE ((T1.PARTITION=5637144576)
AND (T1.DATAAREAID='abr'))
AND ((T7.PARTITION=5637144576)
AND (T1.PRODUCT=T7.RECID))
ORDER BY T1.ITEMID OPTION(FAST 6)
With STATISTICS IO ON I get the following
(2849 row(s) affected)Table 'ECORESPRODUCTDIMENSIONGROUP'. Scan count 1, logical reads 5699, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECORESPRODUCTTRANSLATION'. Scan count 0, logical reads 8734, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECORESPRODUCTDIMENSIONGROUPPRODUCT'. Scan count 2849, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MARINVENTTABLE'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECORESSTORAGEDIMENSIONGROUPITEM'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECORESTRACKINGDIMENSIONGROUPITEM'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTITEMGROUPITEM'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTMODELGROUPITEM'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WHSINVENTTABLE'. Scan count 2849, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ECORESPRODUCT'. Scan count 0, logical reads 5885, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTABLE'. Scan count 1, logical reads 554, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
I thought I would take a look at the ECORESPRODUCTTRANSLATION table as it had the most reads. On that table are the following indexes already:
/****** Object: Index [I_6869PRODUCTLANGUAGEIDX] Script Date: 04/08/2014 15:56:37 ******/CREATE UNIQUE CLUSTERED INDEX [I_6869PRODUCTLANGUAGEIDX] ON [dbo].[ECORESPRODUCTTRANSLATION]
(
[PARTITION] ASC,
[PRODUCT] ASC,
[LANGUAGEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [I_6869RECIDPRODUCTLANGUAGEID] Script Date: 04/08/2014 15:48:15 ******/
CREATE UNIQUE NONCLUSTERED INDEX [I_6869RECIDPRODUCTLANGUAGEID] ON [dbo].[ECORESPRODUCTTRANSLATION]
(
[PARTITION] ASC,
[RECID] ASC,
[PRODUCT] ASC,
[LANGUAGEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD CONSTRAINT [I_6869RECID] PRIMARY KEY NONCLUSTERED
(
[RECID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I had thought that if I create a new NC index like the following I would seen an improvement. My thinking being that the there would be less pages to navigate however it made no difference.
CREATE NONCLUSTERED INDEX [Test] ON [dbo].[ECORESPRODUCTTRANSLATION](
[PARTITION] ASC,
[PRODUCT] ASC,
[LANGUAGEID] ASC
)
INCLUDE (NAME,
RECVERSION,
RECID)
I then accidentally created an index as follows:
CREATE UNIQUE NONCLUSTERED INDEX [Test] ON [dbo].[ECORESPRODUCTTRANSLATION](
LANGUAGEID ASC,
PRODUCT ASC
)
INCLUDE (NAME,
RECVERSION,
RECID)
And I got 3k less logical reads for that table. Why is this more efficient? Is it to do with the clustered information being in the leaf level of the NC index already?
I have also attached the plans with and without the index for information.
Thanks
-
What's the definition of the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Here you go
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ECORESPRODUCTTRANSLATION](
[DESCRIPTION] [nvarchar](1000) NOT NULL,
[NAME] [nvarchar](60) NOT NULL,
[PRODUCT] [bigint] NOT NULL,
[LANGUAGEID] [nvarchar](7) NOT NULL,
[RECVERSION] [int] NOT NULL,
[PARTITION] [bigint] NOT NULL,
[RECID] [bigint] NOT NULL,
[MODIFIEDBY] [nvarchar](8) NOT NULL,
CONSTRAINT [I_6869RECID] PRIMARY KEY NONCLUSTERED
(
[RECID] 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
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ('') FOR [DESCRIPTION]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ('') FOR [NAME]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ((0)) FOR [PRODUCT]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ('') FOR [LANGUAGEID]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ((1)) FOR [RECVERSION]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ((5637144576.)) FOR [PARTITION]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] ADD DEFAULT ('?') FOR [MODIFIEDBY]
GO
ALTER TABLE [dbo].[ECORESPRODUCTTRANSLATION] WITH CHECK ADD CHECK (([RECID]<>(0)))
GO
-
The Non-clustered Index is performing a seek on LANGUAGEID and PRODUCT and using a predicate to filter on PARTITION.
The Clustered Index is performing a seek on LANGUAGEID, PRODUCT and PARTITION.
The non-clustered index is smaller so I would expect to see an improvement on logical reads.
<RelOp AvgRowSize="91" LogicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0217425"><SeekPredicates><Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AX2012_DEV]" Schema="[dbo]" Table="[ECORESPRODUCTTRANSLATION]" Alias="[T10]" Column="LANGUAGEID" />
<ColumnReference Database="[AX2012_DEV]" Schema="[dbo]" Table="[ECORESPRODUCTTRANSLATION]" Alias="[T10]" Column="PRODUCT" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="N'en-gb'">
<ScalarOperator ScalarString="[ECORESPRODUCT].[RECID]">
<Identifier><ColumnReference Column="RECID" /></Identifier>
<Predicate>
<ScalarOperator ScalarString="[PARTITION]=(5637144576.)"><Compare CompareOp="EQ">
<RelOp AvgRowSize="83" LogicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0228019"><SeekPredicates><Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AX2012_DEV]" Schema="[dbo]" Table="[ECORESPRODUCTTRANSLATION]" Alias="[T10]" Column="PARTITION" />
<ColumnReference Database="[AX2012_DEV]" Schema="[dbo]" Table="[ECORESPRODUCTTRANSLATION]" Alias="[T10]" Column="PRODUCT" />
<ColumnReference Database="[AX2012_DEV]" Schema="[dbo]" Table="[ECORESPRODUCTTRANSLATION]" Alias="[T10]" Column="LANGUAGEID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(5637144576.)">
<ScalarOperator ScalarString="[ECORESPRODUCT].[RECID]">
<Identifier><ColumnReference Column="RECID" /></Identifier>
<ScalarOperator ScalarString="N'en-gb'">
-
Try recreating the NC index with Partition as the third key column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Sean Pearce (8/5/2014)
The Non-clustered Index is performing a seek on LANGUAGEID and PRODUCT and using a predicate to filter on PARTITION.The Clustered Index is performing a seek on LANGUAGEID, PRODUCT and PARTITION.
The non-clustered index is smaller so I would expect to see an improvement on logical reads.
Hi Sean - so is it better to try to get indexes to use a predicate filter to reduce IO? (I assume this is because the clustered key is also the filter?) If that logic follows then if I create a NC index like this:
CREATE NONCLUSTERED INDEX [Test] ON [dbo].[ECORESPRODUCTTRANSLATION](
PRODUCT ASC
)
INCLUDE (NAME,
RECVERSION,
RECID)
Then wouldn't that be even better as I am trying to force a seek on PRODUCT and filter using PARTITION and LANGUAGEID? I have created this index and is had no improvement. Are you able to enlighten me as to how this works?
Gail - I also created the index with PARTITION as the 3rd key but again no improvement.
-
With so much repetition of columns in the output, isn't index refactoring a little "cart before the horse"? I'd recommend you eliminate duplicated data from the output before considering indexing. You will end up with way too many columns in your INCLUDEs.
SELECTT1.ITEMID,
T1.ITEMTYPE,
T1.PURCHMODEL,
T1.HEIGHT,
T1.WIDTH,
T1.SALESMODEL,
T1.COSTGROUPID,
T1.REQGROUPID,
T1.EPCMANAGER,
T1.PRIMARYVENDORID,
T1.NETWEIGHT,
T1.DEPTH,
T1.UNITVOLUME,
T1.BOMUNITID,
T1.ITEMPRICETOLERANCEGROUPID,
T1.DENSITY,
T1.COSTMODEL,
T1.USEALTITEMID,
T1.ALTITEMID,
T1.MATCHINGPOLICY,
T1.INTRACODE,
T1.PRODFLUSHINGPRINCIP,
T1.MINIMUMPALLETQUANTITY,
T1.PBAITEMAUTOGENERATED,
T1.WMSARRIVALHANDLINGTIME,
T1.BOMMANUALRECEIPT,
T1.PHANTOM,
T1.INTRAUNIT,
T1.BOMLEVEL,
T1.BATCHNUMGROUPID,
T1.AUTOREPORTFINISHED,
T1.ORIGCOUNTRYREGIONID,
T1.STATISTICSFACTOR,
T1.ALTCONFIGID,
T1.STANDARDCONFIGID,
T1.PRODPOOLID,
T1.PROPERTYID,
T1.ABCTIEUP,
T1.ABCREVENUE,
T1.ABCVALUE,
T1.ABCCONTRIBUTIONMARGIN,
T1.COMMISSIONGROUPID,
T1.SALESPERCENTMARKUP,
T1.SALESCONTRIBUTIONRATIO,
T1.SALESPRICEMODELBASIC,
T1.NAMEALIAS,
T1.PRODGROUPID,
T1.PROJCATEGORYID,
T1.GROSSDEPTH,
T1.GROSSWIDTH,
T1.GROSSHEIGHT,
T1.STANDARDPALLETQUANTITY,
T1.QTYPERLAYER,
T1.SORTCODE,
T1.SERIALNUMGROUPID,
T1.ITEMBUYERGROUPID,
T1.TAXPACKAGINGQTY,
T1.WMSPALLETTYPEID,
T1.ORIGSTATEID,
T1.WMSPICKINGQTYTIME,
T1.TARAWEIGHT,
T1.PACKAGINGGROUPID,
T1.SCRAPVAR,
T1.SCRAPCONST,
T1.ITEMDIMCOSTPRICE,
T1.FORECASTDMPINCLUDE,
T1.PRODUCT,
T1.PALLETTAGGING,
T1.ITEMTAGGINGLEVEL,
T1.DEFAULTDIMENSION,
T1.BOMCALCGROUPID,
T1.PBAITEMCONFIGURABLE,
T1.PBAINVENTITEMGROUPID,
T1.PBAHIDEDIALOG,
T1.PBAHIDEAPPROVAL,
T1.PBAAUTOSTART,
T1.PBAMANDATORYCONFIG,
T1.PDSFREIGHTALLOCATIONGROUPID,
T1.PDSITEMREBATEGROUPID,
T1.PDSVENDORCHECKITEM,
T1.PMFPLANNINGITEMID,
T1.PMFPRODUCTTYPE,
T1.PMFYIELDPCT,
T1.BATCHMERGEDATECALCULATIONMETHOD,
T1.MODIFIEDDATETIME,
T1.DEL_MODIFIEDTIME,
T1.MODIFIEDBY,
T1.CREATEDDATETIME,
T1.DEL_CREATEDTIME,
T1.CREATEDBY,
T1.RECVERSION,
T1.PARTITION,
T1.RECID,
T2.UOMSEQGROUPID,
T2.MAXPICKQTY,
T2.ITEMID,
T2.RFDESCRIPTION1,
T2.RFDESCRIPTION2,
T2.PACKSIZECATEOGRYID,
T2.FILTERCODE,
T2.FILTERCODE2_,
T2.FILTERCODE3_,
T2.FILTERCODE4_,
T2.FILTERGROUP,
T2.FILTERGROUP2_,
T2.FILTERCHANGED,
T2.PRODQTY,
T2.PHYSDIMID,
T2.PACKAGECLASSID,
T2.PICKWCNEG,
T2.MODIFIEDDATETIME,
T2.MODIFIEDBY,
T2.RECVERSION,
T2.PARTITION,
T2.RECID,
T3.ITEMDATAAREAID,
T3.MODELGROUPID,
T3.ITEMID,
T3.MODELGROUPDATAAREAID,
T3.RECVERSION,
T3.PARTITION,
T3.RECID,
T4.ITEMID,
T4.ITEMDATAAREAID,
T4.ITEMGROUPID,
T4.ITEMGROUPDATAAREAID,
T4.RECVERSION,
T4.PARTITION,
T4.RECID,
T5.TRACKINGDIMENSIONGROUP,
T5.ITEMID,
T5.ITEMDATAAREAID,
T5.RECVERSION,
T5.PARTITION,
T5.RECID,
T6.STORAGEDIMENSIONGROUP,
T6.ITEMID,
T6.ITEMDATAAREAID,
T6.RECVERSION,
T6.PARTITION,
T6.RECID,
T7.PRODUCTMASTER,
T7.RETAITOTALWEIGHT,
T7.VARIANTCONFIGURATIONTECHNOLOGY,
T7.INSTANCERELATIONTYPE,
T7.DISPLAYPRODUCTNUMBER,
T7.SEARCHNAME,
T7.PRODUCTTYPE,
T7.MODIFIEDBY,
T7.RECVERSION,
T7.RELATIONTYPE,
T7.PARTITION,
T7.RECID,
T8.DEFAULTSITE,
T8.EQUIVALENTFACTOR,
T8.FOESEARCHTEXT,
T8.ITEMID,
T8.ITEMPRICEGROUPID,
T8.ITEMSTATUS,
T8.MASTERITEMID,
T8.PACKSIZE,
T8.PURCH_MANDATORYFIXEDASSET,
T8.PURCH_SPECIFICTEXT,
T8.MODIFIEDDATETIME,
T8.CREATEDDATETIME,
T8.RECVERSION,
T8.PARTITION,
T8.RECID,
T9.PRODUCTDIMENSIONGROUP, T9.RECVERSION, T9.RECID,
T10.NAME, T10.RECVERSION, T10.RECID,
T11.NAME, T11.RECVERSION, T11.RECID
-- 5518 rows in table
FROM INVENTTABLE T1
-- 0 rows in table
LEFT OUTER JOIN WHSINVENTTABLE T2
ON (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID='abr'))
AND (T1.ITEMID=T2.ITEMID))
-- 5517 rows in table
LEFT OUTER JOIN INVENTMODELGROUPITEM T3
ON ((T3.PARTITION=5637144576)
AND ((T1.ITEMID=T3.ITEMID)
AND (T1.DATAAREAID=T3.ITEMDATAAREAID)))
-- 5517 rows in table
LEFT OUTER JOIN INVENTITEMGROUPITEM T4
ON ((T4.PARTITION=5637144576)
AND ((T1.ITEMID=T4.ITEMID)
AND (T1.DATAAREAID=T4.ITEMDATAAREAID)))
-- 5517 rows in table
LEFT OUTER JOIN ECORESTRACKINGDIMENSIONGROUPITEM T5
ON ((T5.PARTITION=5637144576)
AND ((T1.ITEMID=T5.ITEMID)
AND (T1.DATAAREAID=T5.ITEMDATAAREAID)))
-- 5517 rows in table
LEFT OUTER JOIN ECORESSTORAGEDIMENSIONGROUPITEM T6
ON ((T6.PARTITION=5637144576)
AND ((T1.ITEMID=T6.ITEMID)
AND (T1.DATAAREAID=T6.ITEMDATAAREAID)))
--CROSS JOIN ECORESPRODUCT T7
-- 2934 rows in table
INNER JOIN ECORESPRODUCT T7
ON T7.PARTITION=5637144576 AND T1.PRODUCT=T7.RECID
-- 5518 rows in table
LEFT OUTER JOIN MARINVENTTABLE T8
ON (((T8.PARTITION=5637144576)
AND (T8.DATAAREAID='abr'))
AND (T1.ITEMID=T8.ITEMID))
-- 0 rows in table
LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUPPRODUCT T9
ON ((T9.PARTITION=5637144576)
AND (T7.RECID=T9.PRODUCT))
-- 10248 rows in table
LEFT OUTER JOIN ECORESPRODUCTTRANSLATION T10
ON ((T10.PARTITION=5637144576)
AND ((T10.LANGUAGEID='en-gb')
AND (T7.RECID=T10.PRODUCT)))
-- 0 rows in table
LEFT OUTER JOIN ECORESPRODUCTDIMENSIONGROUP T11
ON ((T11.PARTITION=5637144576)
AND (T9.PRODUCTDIMENSIONGROUP=T11.RECID))
WHERE T1.PARTITION=5637144576
AND T1.DATAAREAID='abr'
--AND ((T7.PARTITION=5637144576)
--AND (T1.PRODUCT=T7.RECID))
ORDER BY T1.ITEMID
OPTION(FAST 6)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Yes I have raised this but its Microsoft Dynamics AX and without a lot of customisation to the way AX uses X++ to generate the SQL then unfortunately its not possible, so I am where I am.
-
Kwisatz78 (8/6/2014)
Yes I have raised this but its Microsoft Dynamics AX and without a lot of customisation to the way AX uses X++ to generate the SQL then unfortunately its not possible, so I am where I am.I feel your pain.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply