Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » facing serious performance issues, please see...
Add to briefcase
12 posts, Page 1 of 2
12»»

facing serious performance issues, please see the scenario below need urgent help - Please chk the code below Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
saxena200
Posted Friday, January 25, 2013 10:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81
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
GilaMonster
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 @ 2:33 PM
Points: 37,741, Visits: 30,019
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
saxena200
Posted Friday, January 25, 2013 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81
PFA, Execution Plan

  Post Attachments 
Exec_Plan.zip (6 views, 24.02 KB)
Post #1411837
GilaMonster
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 @ 2:33 PM
Points: 37,741, Visits: 30,019
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
Lowell
Posted Friday, January 25, 2013 11:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 11,647, Visits: 27,754
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
ChrisM@home
Posted Friday, January 25, 2013 11:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 921, Visits: 3,748
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
saxena200
Posted Friday, January 25, 2013 12:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81
PFA, Actual plan

  Post Attachments 
Actual_Execution_Plan.zip (7 views, 26.80 KB)
Post #1411883
ChrisM@home
Posted Friday, January 25, 2013 12:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 921, Visits: 3,748
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
Lowell
Posted Friday, January 25, 2013 12:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 11,647, Visits: 27,754
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
saxena200
Posted Saturday, January 26, 2013 5:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 11, 2013 3:36 AM
Points: 24, Visits: 81
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 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.