StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUnable to increase performance of the query
text
Body
copied!<p>I have a query as below which is taking about 10 mins to runs, which I have to optimize. I tried my bit but I am unable to increase its performance. Any help is really appreciated</p> <pre><code>SELECT ISNULL(OX.intStore, DX.intStore) AS sStore , OX.dtmCreatedDate , ISNULL(OX.strBarcode, ISNULL(DX.strClientText, DX.FirstBarCode)) AS sBarcode , ISNULL(OX.strAreaName, DX.strAreaName) AS sArea , ISNULL(OX.strDesc, 'N/F') AS sDesc , ISNULL(OX.czBrand, 'N/F') AS sBrand , ISNULL(OX.decCost, 0) AS nCost , ISNULL(DX.SumQty, 0) AS nHT_Unit , ISNULL(OX.fltQty, 0) AS nOH_Unit , (ISNULL(DX.SumQty, 0) * ISNULL(OX.decCost, 0)) AS nEXTCost , (ISNULL(OX.fltQty, 0) * ISNULL(OX.decCost, 0)) AS nOH_EXTCost , (ISNULL(DX.SumQty, 0) - ISNULL(OX.fltQty, 0)) AS nVarUnit , (ISNULL(DX.SumQty, 0) - ISNULL(OX.fltQty, 0)) * ISNULL(OX.decCost, 0) AS nVarCost FROM ( SELECT DISTINCT OH.intStore , OH.strBarcode , SA.intAreaGrp , OH.strDesc , OH.czBrand , OH.decCost , AG.strAreaName , OH.fltQty , OH.dtmCreatedDate FROM tblOnHand AS OH LEFT JOIN dbo.tblStickerAreas AS SA ON (OH.intArea = SA.intAreaNo) LEFT JOIN dbo.tblAreaGrp AS AG ON (SA.intAreaGrp = AG.intAreaGrp) ) AS OX FULL OUTER JOIN ( SELECT DT.intStore , DT.strClientText , SA.intAreaGrp , AG.strAreaName , SUM(DT.fltQty) SumQty , MIN(DT.strBarCode) FirstBarCode FROM dbo.tblDetail AS DT JOIN dbo.tblSticker AS SK ON (DT.intStore = SK.intStore AND DT.intStickerNo = SK.intStickerNo AND DT.dtmstickerdate = SK.dtmstickerdate AND SK.strrescansw = 'N') LEFT JOIN dbo.tblStickerAreas AS SA ON (SK.intAreaNo = SA.intAreaNo) LEFT JOIN dbo.tblAreaGrp AS AG ON (SA.intAreaGrp = AG.intAreaGrp) --WHERE strrescansw = 'N' GROUP BY DT.intStore , DT.strClientText , SA.intAreaGrp , AG.strAreaName ) AS DX ON OX.intStore = DX.intStore AND OX.strBarcode = DX.strClientText </code></pre> <p>The table and indexes are </p> <pre><code>CREATE TABLE [dbo].[tblAreaGrp]( [intAreaGrp] [int] NULL, [strAreaName] [char](20) NULL, [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, CONSTRAINT [PK_tblAreaGrp] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblDetail]( [intStore] [int] NOT NULL, [intStickerNo] [int] NOT NULL, [dtmStickerDate] [datetime] NOT NULL, [intLineNum] [smallint] NOT NULL, [strBarcode] [char](14) NULL, [intBarcodeLength] [tinyint] NULL, [intBarcodeType] [tinyint] NULL, [strBarcodeEntrySW] [char](1) NULL, [strBarcodeSW] [char](1) NULL, [fltQty] [float] NULL, [fltPrice] [decimal](19, 4) NULL, [strPriceSW] [char](1) NULL, [strQtySW] [char](1) NULL, [strAddSW] [char](1) NULL, [strDeleteSW] [char](1) NULL, [strPTCCode1] [char](1) NULL, [strPTCCode2] [char](1) NULL, [strPTCCode3] [char](1) NULL, [strPTCCode4] [char](1) NULL, [strPTCCode5] [char](1) NULL, [strInternalUseOnly] [char](1) NULL, [strMiscText] [varchar](50) NULL, [strClientText] [varchar](50) NULL, [strSKU] [char](14) NULL, [intSKULength] [tinyint] NULL, [intSKUType] [tinyint] NULL, [fltOldQty] [float] NULL, [blnVerifyNOF] [bit] NULL, [blnVerifyTS] [bit] NULL, [blnNOF] [bit] NULL, [EnteredValue] [varchar](30) NULL, [AcceptedValue] [varchar](30) NULL, [MasterFileValue] [varchar](30) NULL, [QuantityEntered] [int] NULL, [intSku] [int] NULL, [intUpc] [bigint] NULL, [Description] [varchar](50) NULL, [SerialNumber] [varchar](30) NULL, [MFQuantity] [varchar](10) NULL, [InvoiceFlag] [varchar](10) NULL, [CancelFlag] [varchar](10) NULL, CONSTRAINT [PK_tblDetail] PRIMARY KEY NONCLUSTERED ( [intStore] ASC, [intStickerNo] ASC, [dtmStickerDate] ASC, [intLineNum] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblOnHand]( [ID] [int] IDENTITY(1,1) NOT NULL, [intStore] [int] NULL, [intArea] [int] NOT NULL, [strBarcode] [varchar](25) NOT NULL, [strDesc] [varchar](25) NOT NULL, [fltQty] [float] NOT NULL, [decCost] [decimal](19, 4) NOT NULL, [czLocation] [varchar](100) NULL, [czLocCode] [varchar](100) NULL, [czPartNum] [varchar](100) NULL, [czPartDesc] [varchar](200) NULL, [czBrand] [varchar](200) NULL, [czCategory] [varchar](200) NULL, [czSEB] [varchar](200) NULL, [czLastRec] [varchar](100) NULL, [czWAC] [varchar](100) NULL, [czExtWAC] [varchar](100) NULL, [dtmCreatedDate] [datetime] NOT NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblSticker]( [intStore] [int] NOT NULL, [intStickerNo] [int] NOT NULL, [dtmStickerDate] [datetime] NOT NULL, [strRescanSW] [char](1) NOT NULL, [intCorrectionID] [int] NULL, [intSessionNo] [int] NULL, [strCorrectionStatus] [char](1) NULL, [strEmptyStatus] [char](1) NULL, [intLastLineNo] [smallint] NULL, [fltTotalUnits] [float] NULL, [fltTotalValue] [decimal](19, 4) NULL, [strDeductionSW] [char](1) NULL, [intDeviceNo] [int] NULL, [intAreaNo] [int] NULL, [dtmAreaStartDate] [datetime] NULL, [dtmTeamStartDate] [datetime] NULL, [dtmTeamPrintedDate] [datetime] NULL, [strTeamNo] [char](9) NULL, [strSessionNo] [char](3) NULL, [strTeamTrainingSW] [char](1) NULL, [dtmFixtureStartDate] [datetime] NULL, [dtmFixtureEndDate] [datetime] NULL, [strFixtureEcsCode] [char](1) NULL, [strHHRecordTypeSW] [char](1) NULL, [strFixtureTrainingSW] [char](1) NULL, [strEmptyFixtureSW] [char](1) NULL, [strCorrectionStatus2] [char](1) NULL, [strMiscText] [varchar](50) NULL, [strClientMisc1] [char](1) NULL, [strClientMisc2] [char](1) NULL, [intClientMisc1] [int] NULL, [intClientMisc2] [int] NULL, [fltClientMisc1] [float] NULL, [fltClientMisc2] [float] NULL, [strClientText] [varchar](50) NULL, [blnPieceCount] [bit] NULL, [bitOverrideMode] [bit] NULL, [blnAuditMode] [bit] NULL, [AutoPrint] [bit] NOT NULL, [AutoPrintDate] [datetime] NULL, CONSTRAINT [PK_tblSticker] PRIMARY KEY NONCLUSTERED ( [intStore] ASC, [intStickerNo] ASC, [dtmStickerDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblStickerAreas]( [intAreaFlag] [tinyint] NOT NULL, [intAreaNo] [tinyint] NOT NULL, [strAreaName] [varchar](20) NOT NULL, [blnAllAreas] [bit] NOT NULL, [intAreaGrp] [int] NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_StoreClientText] ON [dbo].[tblDetail] ( [intStore] ASC, [strClientText] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IDX_CreatedDate] ON [dbo].[tblOnHand] ( [dtmCreatedDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] GO SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [IDX_intArea] ON [dbo].[tblOnHand] ( [intArea] ASC ) INCLUDE ( [intStore], [strBarcode], [strDesc], [fltQty], [decCost], [czBrand], [dtmCreatedDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IDX_StoreAreaBar] ON [dbo].[tblOnHand] ( [intStore] ASC, [intArea] ASC, [strBarcode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [_dta_index_tblSticker_204_2025058250__K4_K1_K2_K3_K14] ON [dbo].[tblSticker] ( [strRescanSW] ASC, [intStore] ASC, [intStickerNo] ASC, [dtmStickerDate] ASC, [intAreaNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] CREATE NONCLUSTERED INDEX [IDX_intAreaNo] ON [dbo].[tblStickerAreas] ( [intAreaNo] ASC ) INCLUDE ( [strAreaName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload