September 2, 2017 at 3:20 am
HI expert,
i need an help to speed up an apparently simply query
I have a table MGMOV with about 4 millions of records
The structure of mgmov is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MgMov](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Id_MgMovTesta] [int] NULL,
[Id_DocRigMp] [int] NULL,
[ID_LPRigheMp] [int] NULL,
[Id_LPAvanzamento] [int] NULL,
[CdMgAna] [char](5) NULL,
[CdMgCau] [char](6) NOT NULL,
[CdArMp] [char](20) NOT NULL,
[CdColori] [char](6) NULL,
[Misura] [varchar](10) NULL,
[Qta] [numeric](18, 6) NULL,
[Data] [smalldatetime] NULL,
[CdEser] [char](4) NULL,
[CdStagioni] [char](6) NULL,
[CdCart] [char](6) NULL,
[Segno] [int] NULL,
[Segno_CaricoScarico] [int] NULL,
[ValoreUnitario] [numeric](18, 6) NULL,
[Sconti] [varchar](20) NULL,
[ValoreTotale] [numeric](18, 6) NULL,
[Carico] [bit] NULL,
[Scarico] [bit] NULL,
[Impegnato] [bit] NULL,
[Ordinato] [bit] NULL,
[Fiscale] [bit] NULL,
[Acquisto] [bit] NULL,
[Vendita] [bit] NULL,
[Rettifica] [bit] NULL,
[Trasferimento] [bit] NULL,
[Deleted] [bit] NOT NULL,
[Userins] [varchar](100) NULL,
[Timeins] [smalldatetime] NULL,
[HostIns] [varchar](100) NULL,
[Userupd] [varchar](100) NULL,
[Timeupd] [smalldatetime] NULL,
[HostUPD] [varchar](100) NULL,
[Userlocked] [varchar](100) NULL,
[Locked] [bit] NOT NULL,
CONSTRAINT [PK_MgMov] PRIMARY KEY CLUSTERED
(
[ID] 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].[MgMov] ADD CONSTRAINT [DF_MgMov_Qta] DEFAULT ((0)) FOR [Qta]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_Data] DEFAULT (CONVERT([char](10),getdate(),(103))) FOR [Data]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_ValoreUnitario] DEFAULT ((0)) FOR [ValoreUnitario]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_Deleted] DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_UserIns] DEFAULT (suser_sname()) FOR [Userins]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_TimeIns] DEFAULT (getdate()) FOR [Timeins]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_HostIns] DEFAULT (host_name()) FOR [HostIns]
GO
ALTER TABLE [dbo].[MgMov] ADD CONSTRAINT [DF_MgMov_Locked] DEFAULT ((0)) FOR [Locked]
GO
CREATE NONCLUSTERED INDEX [IX_MgMov_CdArMp_CdColori_Misura] ON [dbo].[MgMov]
(
[CdArMp] ASC,
[CdColori] ASC,
[Misura] ASC
)
INCLUDE ( [CdMgAna]) 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 [IX_MgMov_CdMgAna] ON [dbo].[MgMov]
(
[CdMgAna] ASC
)
INCLUDE (
[CdArMp],
[CdColori],
[Misura],
[CdMgCau]
) 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 [IX_MgMov_CdMGANA_Data_Qta] ON [dbo].[MgMov]
(
[CdMgAna] ASC,
[Data] ASC,
[Qta] ASC
)
INCLUDE (
[CdMgCau],
[CdArMp],
[CdColori],
[Misura]) 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 [IX_MgMov_MGCau] ON [dbo].[MgMov]
(
[CdMgCau] ASC
)
INCLUDE (
[Acquisto],
[Carico],
[Fiscale],
[Impegnato],
[Ordinato],
[Rettifica],
[Scarico],
[Trasferimento],
[Vendita]) 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
I require to run frequently the following query that is very slow (25-40 seconds)
Apparently if there are more than 4-5 sums in the query, the query becomes very slow
Can I have help or any idea or workaround to do this please?
Thanks
DECLARE
@AllaData SmallDatetime = '20170831'
,@CdMgAna Char(5) = '00001'
,@DataInizio SmallDatetime
SET @DataInizio = CAST(YEAR(@AllaData) AS CHAR(4)) + '0101'
SELECT
YEAR(@AllaData) AS CdEser
,MgMov.CdMgAna
,MgMov.CdArMp
,ISNULL(MgMov.CdColori,'') AS CdColori
,ISNULL(MgMov.Misura,'') AS Misura
,ISNULL(SUM(MgMov.Qta * MgMov.Segno_CaricoScarico),0) AS Giacenza
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Segno_CaricoScarico),0) AS Valore
,SUM(MgMov.Qta * ISNULL(MgMov.Impegnato,0)) AS QtaImpegnato
,SUM(MgMov.ValoreTotale * ISNULL(MgMov.Impegnato,0)) AS ValImpegnato
-- if I Have max 4 or 5 SUM FUNCTION the result is fastest (1-2 seconds)
/*
-- if I add one line of the next, the result begin veri slow (15-20-30 seconds)
,SUM(MgMov.Qta * ISNULL(MgMov.Ordinato,0)) AS QtaOrdinato
,SUM(MgMov.ValoreTotale * ISNULL(MgMov.Ordinato,0)) AS ValOrdinato
,ISNULL(SUM(MgMov.Qta * MgMov.Segno_CaricoScarico *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS QtaIniziale
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Segno_CaricoScarico *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS ValIniziale
,ISNULL(SUM(MgMov.Qta * MgMov.Impegnato *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS ImpIniziale
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Impegnato *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS ValImpIniziale
,ISNULL(SUM(MgMov.Qta * MgMov.Ordinato *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS OrdIniziale
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Ordinato *
CASE WHEN MgMov.Data < @DataInizio THEN 1 ELSE 0 END ),0) AS ValOrdIniziale
,0 AS DispImmediata
,0 AS DispFutura
,ISNULL(SUM(MgMov.Qta * MgMov.Carico ),0) AS QtaCarico
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Carico ),0) AS ValCarico
,ISNULL(SUM(MgMov.Qta * MgMov.Scarico),0) AS QtaScarico
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Scarico),0) AS ValScarico
,ISNULL(SUM(MgMov.Qta * MgMov.Acquisto),0) AS QtaAcquisto
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Acquisto),0) AS ValAcquisto
,ISNULL(SUM(MgMov.Qta * MgMov.Vendita),0) AS QtaVendita
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Vendita),0) AS ValVendita
,ISNULL(SUM(MgMov.Qta * MgMov.Rettifica ),0) AS QtaRettifica
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Rettifica ),0) AS ValRettifica
,ISNULL(SUM(MgMov.Qta * MgMov.Trasferimento ),0) AS QtaTrasferimento
,ISNULL(SUM(MgMov.ValoreTotale * MgMov.Trasferimento ),0) AS ValTrasferimento
*/
FROM MgMov
WHERE
MgMov.Data <= @AllaData
AND (MgMov.CdMGAna = @CdMGana OR @CdMGana IS NULL)
GROUP BY
MgMov.CdMgAna
,MgMov.CdArMp
,ISNULL(MgMov.CdColori,'')
,ISNULL(MgMov.Misura,'')
September 2, 2017 at 7:02 am
If you could post an actual execution plan, it would make diagnosing the query much easier. Make it a plan that includes the aggregations because what you're seeing is changes to the execution plan by the inclusion of the aggregate functions.
The one thing that jumps out immediately for me is that you're filtering on MgMov.Data and MgMov.CdMGAna, but there's no index for MgMov.Data. While not all problems are solved by adding an index, this may be one of those situations. Without a useful index, you're likely to see scans on the table.
On a side note, I'd toss the first of these indexes:CREATE NONCLUSTERED INDEX [[IX_MgMov_CdMgAnaIX_MgMov_CdMgAna]] ON ON [[dbodbo].[].[MgMovMgMov]]
((
[[CdMgAnaCdMgAna]] ASC ASC
))
INCLUDE INCLUDE ((
[[CdArMpCdArMp],],
[[CdColoriCdColori],],
[[MisuraMisura],],
[[CdMgCauCdMgCau]]
)) WITH WITH ((PAD_INDEX PAD_INDEX == OFF OFF,, STATISTICS_NORECOMPUTE STATISTICS_NORECOMPUTE == OFF OFF,, SORT_IN_TEMPDB SORT_IN_TEMPDB == OFF OFF,, DROP_EXISTING DROP_EXISTING == OFF OFF,, ONLINE ONLINE == OFF OFF,, ALLOW_ROW_LOCKS ALLOW_ROW_LOCKS == ON ON,, ALLOW_PAGE_LOCKS ALLOW_PAGE_LOCKS == ON ON)) ON ON [[PRIMARYPRIMARY]]
GOGO
CREATE NONCLUSTERED INDEX CREATE NONCLUSTERED INDEX [[IX_MgMov_CdMGANA_Data_QtaIX_MgMov_CdMGANA_Data_Qta]] ON ON [[dbodbo].[].[MgMovMgMov]]
((
[[CdMgAnaCdMgAna]] ASC ASC,,
[[DataData]] ASC ASC,,
[[QtaQta]] ASC ASC
))
INCLUDE INCLUDE ((
[[CdMgCauCdMgCau],],
[[CdArMpCdArMp],],
[[CdColoriCdColori],],
[[MisuraMisura])]) WITH WITH ((PAD_INDEX PAD_INDEX == OFF OFF,, STATISTICS_NORECOMPUTE STATISTICS_NORECOMPUTE == OFF OFF,, SORT_IN_TEMPDB SORT_IN_TEMPDB == OFF OFF,, DROP_EXISTING DROP_EXISTING == OFF OFF,, ONLINE ONLINE == OFF OFF,, ALLOW_ROW_LOCKS ALLOW_ROW_LOCKS == ON ON,, ALLOW_PAGE_LOCKS ALLOW_PAGE_LOCKS == ON ON)) ON ON [[PRIMARYPRIMARY]]
GO
The reason is that the leading edge, CdMgAna, is the same. That column will be used to create the histogram and that's one of the biggest driving factors for picking execution plans. Adding the additional columns makes the density graph better for the second index. With an identical histogram and greater density, the second index will be chosen the vast majority of the time. The include columns are the same, so there's nothing gained by maintaining that first index. The one place you may see more use of it is in scans. Since it will be smaller, with more rows per page, if the index can satisfy the query and a scan is called for, the smaller sized index will be chosen. Other than that situation, it's just not needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 2, 2017 at 8:11 am
Thank's for you attention Sir,
i have added the missing index as suggested by you and the execution plan but the run time for the complete query goes from 35 to 50 seconds
I attach 2 execution plan:
Without index 35 sec.
WITH index 52 sec
September 2, 2017 at 9:08 am
A) There's a lot of problematic things going on in this plan:
1) full scan, with some estimation problems (see below)
2) a metric butt ton of cpu burning operations (case's, convert_implicit's, mathematical operations (direct and aggregate), isnull's, sort, repartition streams, etc)
3) repartition streams
4) sort
B) Did you do a wait stats and IO stalls analysis while the query was running?
C) Use sp_whoisactive to see waits in real time and use the differential feature to see waits over time.
D) What is the CPU capabilities of the server?
E) Your data types are DEFINITELY suboptimal. Storing those fat strings for who did what is a massive waste of space. They could/should be small identifiers from a Users and Hosts table. I see this type of "fat string" problem at clients all the time and it often wastes GIGABYTES of space per column compared to storing an integer pointer.
F) Regarding estimation: you have the dreaded IS NULL OR problem. Make two queries. For one, where @CdMGana IS NULL there need be NO filter on that particular field because it would be (anything OR TRUE), which boolean-wise evaluates to TRUE and can thus be removed from the query. The other code would actually have the filter but NOT the OR @CdMGana IS NULL. If you put this code in the same sproc you will need OPTION (RECOMPILE) on both of them.
G) Actually OPTION (RECOMPILE) is appropriate in any case because you are also exposed to the "widely-varying input problem". Suppose you put 1/1/1900 in for your @AllaData parameter for the first call. That query plan will be stored, likely estimating very few rows. It SHOULD be doing an index seek on an index on Data, but you don't have that (which is probably important unless it is always called with values that hit large fractions of the total rows). Then you call it with yesterday as the parameter value. Presumably you will hit all data, and those small estimates in the stored plan will KILL you on the memory grants (and if index seeking in logical IOs). Reverse this and you are still crushed: scan and huge memory grants would be reused when you hit small number of rows.
H) Speaking of indexing, note the missing index recommended. It unsurprisingly is on Data and includes I believe all columns in play in the query (leaving out all the bloat).
I) You can eliminate most if not all of the ISNULLs by removing the NULLs from the columns. It is silly to have them in there. 0 for those bits is an appropriate default, yes? I see this at clients ALL THE TIME, and it is an UNBELIEVABLY bad problem in total. Do yourself a HUGE favor: design NULLs out of your schemas!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply