SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


facing serious performance issues, please see the scenario below need urgent help - Please chk the...


facing serious performance issues, please see the scenario below need urgent help - Please chk the code below

Author
Message
saxena200
saxena200
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 168
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222527 Visits: 46291
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, MVP, M.Sc (Comp Sci)
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


saxena200
saxena200
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 168
PFA, Execution Plan
Attachments
Exec_Plan.zip (12 views, 24.00 KB)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222527 Visits: 46291
Table definitions. Index definitions.

Edit: and the actual plan please, not estimated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71084 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 10605
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
saxena200
saxena200
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 168
PFA, Actual plan
Attachments
Actual_Execution_Plan.zip (13 views, 26.00 KB)
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 10605
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
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71084 Visits: 40924
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
saxena200
saxena200
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 168
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
Attachments
EP_NEW.zip (9 views, 52.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search