InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:53:50 GMT20http://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxjust my 2ct, to add to previous responders replies:In your sproc, chances are filter arguments aren't sniffed to your advantage at query plan generation time.Reason for that is you are parsing and completing the filter arguments within the sproc itself.Try handing there parameters over as needed by the sproc itself.i.s. try writing your sproc so it accepts the needed filter data as input paramters and see how that influences the query plan.It may even be enough to add 'with recompile' to the detail query ( please implement the grouping and union all advice )Sun, 27 Jan 2013 13:29:53 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxchris@home gave you teh best advice so far: if you can rewrite teh query, redo it so that it is 5 unions, using the agregate example he already put to gether for you;you are looking at at least an order of magnitude improvement doing it that way(ie 6 seconds instead of 60)Sun, 27 Jan 2013 12:19:48 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxLowell, Thanks for this approachBut still query is taking 60 secsearlier it was taking 3 minsanything else we can do in order to tune it??PFA the New execution PLanSat, 26 Jan 2013 05:49:27 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxstatsics look real bad in two of the queries (200K+ rows estimated, actual = 0and pretty bad for the rest;running this, with no other changes, will at least help a little bit: the otehr items should be addresses ASAP.[code]USE [MICGHBI_UAT_EDW];UPDATE STATISTICS [dbo].[AGNT_MFS_CL_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AGNT_MFS_CL_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AGNT_MFS_PRFL_DIM] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AGNT_MFS_SVC_ACCM_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AR_MFS_CL_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AR_MFS_CL_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AR_MFS_PRFL_DIM] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AR_MFS_SVC_ACCM_FCT] WITH FULLSCAN; UPDATE STATISTICS [dbo].[AR_ST_DIM] WITH FULLSCAN; UPDATE STATISTICS [dbo].[DT_DIM] WITH FULLSCAN; UPDATE STATISTICS [dbo].[DT_DIM] WITH FULLSCAN; UPDATE STATISTICS [dbo].[PYMNT_ENTTY_DIM] WITH FULLSCAN; [/code]Fri, 25 Jan 2013 12:15:04 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspx[quote][b]saxena200 (1/25/2013)[/b][hr]PFA, Actual plan[/quote]Sorted - it's running those results unaggregated into the table variable which is costing so much. I'd guess you might get a 20-fold lift by aggregating each query.Fri, 25 Jan 2013 12:09:44 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxPFA, Actual planFri, 25 Jan 2013 12:02:05 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspx[quote][b]Lowell (1/25/2013)[/b][hr]also, my rule of thumb is that any @TableVariable with more than a few hundred rows should be a temp table instead, to allow the system to automatically do things like generate statistics;From the estimated # of rows from the estimated execution plans i see 200K+ rows for every query in there.i see a missing index suggestions for every query in the plan as well.Since this was not an actual execution plan, i'm concerned that the high number of estimated rows might be due to bad statistics.I see Non-SARG-able items that force a table scan, like:[i] AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=60)[/i] [i] AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=30)[/i]since they are all going into the same table with the same structure, i'd change this to be a CTE with UNION ALL isntead of a @TableVariable.[/quote]Also, the single highest cost of the highest-costed queries is table insert - into the table variable. So why not perform the aggregate - the final query - on each individual query, like this:[code="sql"];WITH TMP AS ( SELECT [userType] = 'Total Registered Subscriber Base', -- 31% [status] = '', [bank] = PED.PYMNT_ENTTY_NM, [date] = AF.FCT_DT, [WEEK] = dt.MIC_WK_OF_MO_NM FROM DBO.AR_MFS_CL_FCT AF INNER JOIN DBO.AR_MFS_PRFL_DIM PD ON AF.AR_MFS_PRFL_KEY = PD.AR_MFS_PRFL_KEY INNER JOIN DBO.PYMNT_ENTTY_DIM PED ON PED.PYMNT_ENTTY_KEY = PD.PYMNT_ENTTY_KEY INNER JOIN DBO.DT_DIM dt ON dt.DT_KEY = AF.FCT_DT_KEY WHERE (dt.YR = @MLLCMBI_GH_YR) AND (dt.MO_OF_YR_NM = @MLLCMBI_GH_MNTH)) SELECT [BankName] = TMP.bank, [YearName] = @MLLCMBI_GH_YR, [MonthName] = @MLLCMBI_GH_MNTH, TMP.[WEEK], TMP.[date], 'Total Registered Subscriber Base' = COUNT(*) INTO @TMPVW -- preferably a #temp tableFROM TMP GROUP BY TMP.[date], tmp.bank, TMP.[WEEK], TMP.userType[/code]Fri, 25 Jan 2013 11:52:11 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxalso, my rule of thumb is that any @TableVariable with more than a few hundred rows should be a temp table instead, to allow the system to automatically do things like generate statistics;From the estimated # of rows from the estimated execution plans i see 200K+ rows for every query in there.i see a missing index suggestions for every query in the plan as well.Since this was not an actual execution plan, i'm concerned that the high number of estimated rows might be due to bad statistics.I see Non-SARG-able items that force a table scan, like:[i] AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=60)[/i] [i] AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=30)[/i]since they are all going into the same table with the same structure, i'd change this to be a CTE with UNION ALL isntead of a @TableVariable.Fri, 25 Jan 2013 11:24:42 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxTable definitions. Index definitions.Edit: and the actual plan please, not estimated.Fri, 25 Jan 2013 11:11:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxPFA, Execution PlanFri, 25 Jan 2013 10:36:34 GMTsaxena200http://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxPlease post table definitions, index definitions and execution plan, as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Fri, 25 Jan 2013 10:18:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1411818-391-1.aspxdeclare @MLLCMBI_GH_YR varchar(50)='2012'declare @MLLCMBI_GH_MNTH varchar(50)='March'--declare @MLLCMBI_GH_WK varchar(50)='W2'--declare @MLLCMBI_GH_BNK varchar(50)='ADB'declare @TMPVW table ( userType varchar(50), status varchar(100), bank varchar(50), date datetime, WEEK VARCHAR(50) )--WHILE @chrind > 0-- BEGIN-- SELECT @chrind = CHARINDEX(@Delim,@RepParam)-- IF @chrind > 0-- SELECT @Piece = LEFT(@RepParam,@chrind - 1)-- ELSE-- SELECT @Piece = @RepParam-- INSERT @Values(Param) VALUES (CAST(@Piece AS VARCHAR (max))) -- SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)-- IF LEN(@RepParam) = 0 BREAK-- END insert into @TMPVW (userType, status, bank, date, WEEK ) (-- declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='March'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)='Ecobank' select 'Active Subscribers By 1 or More' AS 'USERTYPE', '', PED.PYMNT_ENTTY_NM,AF.FCT_DT, DT_DIM.MIC_WK_OF_MO_NM FROM AR_MFS_SVC_ACCM_FCT AF WITH (NOLOCK) INNER JOIN dbo.AR_MFS_PRFL_DIM PD WITH (NOLOCK) ON AF.AR_MFS_PRFL_KEY = PD.AR_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) and (AF.SNT_TRNCT_CNT_LST_60_DAYS>1) --GROUP BY PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank, date,WEEK) ( --declare @MLLCMBI_GH_YR varchar(50)='2012' --declare @MLLCMBI_GH_MNTH varchar(50)='May' --declare @MLLCMBI_GH_WK varchar(50)='W1' --declare @MLLCMBI_GH_BNK varchar(50)='Ecobank' select 'Disconnected Subscribers' AS 'USERTYPE', '', PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM dbo.AR_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN dbo.AR_MFS_PRFL_DIM PD WITH (NOLOCK) ON AF.AR_MFS_PRFL_KEY=PD.AR_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=60) -- GROUP BY PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank, date,WEEK) (-- declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)='Ecobank' select 'Total Registered Subscriber Base' AS 'USERTYPE', '', PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM DBO.AR_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN DBO.AR_MFS_PRFL_DIM PD WITH (NOLOCK) ON AF.AR_MFS_PRFL_KEY=PD.AR_MFS_PRFL_KEY INNER JOIN DBO.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN DBO.DT_DIM WITH (NOLOCK) ON DBO.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) -- GROUP BY PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank ,date,WEEK) ( --declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)='Ecobank' select 'New Tigo Cash Subscribers' AS 'USERTYPE', dbo.AR_ST_DIM.AR_ST_NM, PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM dbo.AR_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN dbo.AR_MFS_PRFL_DIM PD WITH (NOLOCK) ON AF.AR_MFS_PRFL_KEY=PD.AR_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.AR_ST_DIM WITH (NOLOCK) ON AF.MFS_ST_KEY=dbo.AR_ST_DIM.AR_ST_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) AND (dbo.AR_ST_DIM.AR_ST_grp_NM='active') -- GROUP BY PED.PYMNT_ENTTY_NM,dbo.AR_ST_DIM.AR_ST_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) ---------------------------------------------------------------------------------- insert into @TMPVW (userType, status, bank, date,WEEK) ( -- declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W5'--declare @MLLCMBI_GH_BNK varchar(50)=' Ecobank' select 'Active Agents By 1 or More' as USERTYPE, '', PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM AGNT_MFS_SVC_ACCM_FCT AF WITH (NOLOCK) INNER JOIN dbo.AGNT_MFS_PRFL_DIM PD WITH (NOLOCK) ON PD.AGNT_MFS_PRFL_KEY=AF.AGNT_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) AND (af.SNT_TRNCT_CNT_LST_MNT>1) -- GROUP BY PED.PYMNT_ENTTY_NM ,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank, date,WEEK) ( -- declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)=' Ecobank' select 'Disconnected Agents' as USERTYPE, '', PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM dbo.AGNT_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN dbo.AGNT_MFS_PRFL_DIM PD WITH (NOLOCK) ON PD.AGNT_MFS_PRFL_KEY=AF.AGNT_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=30) -- GROUP BY PED.PYMNT_ENTTY_NM ,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank, date,WEEK) ( --declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)=' Ecobank' select 'Total Registered Agent Base' AS 'USERTYPE', '', PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM dbo.AGNT_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN dbo.AGNT_MFS_PRFL_DIM PD WITH (NOLOCK) ON PD.AGNT_MFS_PRFL_KEY=AF.AGNT_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) -- GROUP BY PED.PYMNT_ENTTY_NM ,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM ) insert into @TMPVW (userType, status, bank, date,WEEK) ( --declare @MLLCMBI_GH_YR varchar(50)='2012'--declare @MLLCMBI_GH_MNTH varchar(50)='May'--declare @MLLCMBI_GH_WK varchar(50)='W1'--declare @MLLCMBI_GH_BNK varchar(50)=' Ecobank' select 'New Agents Base' as USERTYPE, dbo.AR_ST_DIM.AR_ST_NM, PED.PYMNT_ENTTY_NM,AF.FCT_DT,DT_DIM.MIC_WK_OF_MO_NM FROM dbo.AGNT_MFS_CL_FCT AF WITH (NOLOCK) INNER JOIN dbo.AGNT_MFS_PRFL_DIM PD WITH (NOLOCK) ON PD.AGNT_MFS_PRFL_KEY=AF.AGNT_MFS_PRFL_KEY INNER JOIN dbo.PYMNT_ENTTY_DIM PED WITH (NOLOCK) ON PED.PYMNT_ENTTY_KEY=PD.PYMNT_ENTTY_KEY INNER JOIN dbo.DT_DIM WITH (NOLOCK) ON dbo.DT_DIM.DT_KEY=AF.FCT_DT_KEY INNER JOIN dbo.AR_ST_DIM WITH (NOLOCK) ON dbo.AR_ST_DIM.AR_ST_KEY=AF.AR_ST_KEY WHERE (dbo.DT_DIM.YR = @MLLCMBI_GH_YR) AND (DT_DIM.MO_OF_YR_NM = @MLLCMBI_GH_MNTH) --AND (dbo.DT_DIM.MIC_WK_OF_MO_NM in (@MLLCMBI_GH_WK)) --AND (PED.PYMNT_ENTTY_NM IN (@MLLCMBI_GH_BNK)) AND dbo.AR_ST_DIM.AR_ST_grp_NM='active' -- GROUP BY PED.PYMNT_ENTTY_NM ,AF.FCT_DT,dbo.AR_ST_DIM.AR_ST_NM,DT_DIM.MIC_WK_OF_MO_NM ) --==================================================================================== select TMP.bank as BankName, @MLLCMBI_GH_YR as YearName, @MLLCMBI_GH_MNTH as MonthName, TMP.WEEK 'WEEK', TMP.date, --TMP.userType, --COUNT(userType) AS C_UserType ( CASE WHEN (userType='Active Subscribers By 1 or More') THEN COUNT(1) ELSE 0 END ) AS 'Active sub by one or more', ( CASE WHEN(userType='Disconnected Subscribers') THEN COUNT(1) ELSE 0 END )AS 'Disconnected sub', ( CASE WHEN (userType='Total Registered Subscriber Base') THEN COUNT(1) ELSE 0 END ) AS 'Total Registered Subscriber Base', ( CASE WHEN (userType='New Tigo Cash Subscribers') THEN COUNT(1) ELSE 0 END )as 'New Tigo Cash subs', (CASE WHEN (userType='Active Agents By 1 or More') THEN COUNT(1) ELSE 0 END )AS 'Active agents by 1 or more', ( CASE WHEN (userType='Disconnected Agents') THEN COUNT(1) ELSE 0 END )AS 'Disconnected Agents', ( CASE WHEN (userType='Total Registered Agent Base') THEN COUNT(1) ELSE 0 END )AS 'Total Agents registered base', (CASE WHEN(userType='New Agents Base') THEN COUNT(userType) ELSE 0 END )AS 'New Agents base', (COUNT(*)) AS 'Total registered sub base' from @TMPVW TMP group by TMP.date, tmp.bank, TMP.WEEK, TMP.userType --order by tmp.bankFri, 25 Jan 2013 10:16:25 GMTsaxena200