Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

facing serious performance issues, please see the scenario below need urgent help - Please chk the code below Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 10:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
declare @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.bank
Post #1411818
Posted Friday, January 25, 2013 10:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411820
Posted Friday, January 25, 2013 10:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
PFA, Execution Plan

  Post Attachments 
Exec_Plan.zip (6 views, 24.02 KB)
Post #1411837
Posted Friday, January 25, 2013 11:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 39,977, Visits: 36,339
Table definitions. Index definitions.

Edit: and the actual plan please, not estimated.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1411857
Posted Friday, January 25, 2013 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,902, Visits: 32,141
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:
AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=60)
AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=30)

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.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1411865
Posted Friday, January 25, 2013 11:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,031, Visits: 6,730
Lowell (1/25/2013)
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:
AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=60)
AND (DATEDIFF(DD,AF.FCT_DT,AF.LST_MFS_EVNT_DT)>=30)

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.


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:

;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 table
FROM TMP
GROUP BY
TMP.[date],
tmp.bank,
TMP.[WEEK],
TMP.userType




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1411879
Posted Friday, January 25, 2013 12:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
PFA, Actual plan

  Post Attachments 
Actual_Execution_Plan.zip (7 views, 26.80 KB)
Post #1411883
Posted Friday, January 25, 2013 12:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:47 AM
Points: 1,031, Visits: 6,730
saxena200 (1/25/2013)
PFA, Actual plan


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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1411888
Posted Friday, January 25, 2013 12:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,902, Visits: 32,141
statsics look real bad in two of the queries (200K+ rows estimated, actual = 0
and 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.
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;



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1411893
Posted Saturday, January 26, 2013 5:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 2:11 PM
Points: 34, Visits: 100
Lowell, Thanks for this approach

But still query is taking 60 secs
earlier it was taking 3 mins

anything else we can do in order to tune it??

PFA the New execution PLan


  Post Attachments 
EP_NEW.zip (3 views, 52.37 KB)
Post #1412017
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse