January 10, 2013 at 12:57 am
Hi friends,
please see the below query.it returns 3122 rows and query is running 0.55.indexs also their and but its running on views(its basicallly views)
so how to altered the query optimization this below query..please suggest me.????
please suggest me and give me solution for below query.
DECLARE @Subsegment_Temp TABLE(SubsegmentID int IDENTITY(1,1),Subsegment NVARCHAR(MAX))
DECLARE @rc int
DECLARE @Subsegment NVARCHAR(MAX)
DECLARE @I int
DECLARE @MYMONTHCLOSED_ORG datetime
DECLARE @MYMONTHCLOSED datetime
SELECT DISTINCT
ID,
(Case when (State Is NULL ) then 'NULL' else State end) as State,
(Case when (
State in ('Duplicate','Reject','Obsolete') or
(CauseOfDefect IN ('Works As Designed (WAD)','Closed No Fix','Env. Supt.') or
Resolution in ('Functions as Designed', 'Invalid','Obsolete','As Designed', 'Duplicate','No Fix'))) then 0 else 1 end) as NETClosed,
count(distinct([CORP\DANIELAT].vw_Common_IOMDaniela.[ID] )) as GROSSClosed,
(Case when ( ReportingSourceDetail_2 IS NULL) then 'NULL'
else ReportingSourceDetail_2 end) as ReportingSourceDetail_2,
Subsegment,
( CASE when( ( Subsegment ='O&E')and
product not IN ('APACS-Direktor',
'DT Analyst','DTA Migrator',
'Equip Oper Mod',
'FactAutomation',
'Formula Managment Module',
'IA Batch NT',
'IA Batch Unix',
'InBatch',
'InBatch ArchestrA Objects',
'InBatchEagle'
,'InTrack',
'InTrack Report Templates',
'InTrackAC',
'MEM',
'PEM',
'ProdDB support for SQL 2005 cluster',
'QI Analyst',
'WW MES',
'DNC Professional') and (ReportingSourceDetail_2 like '%CSG%' or ReportingSourceDetail_2 like '%Custom%' or ReportingSourceDetail_2 Like '%CFD%'))
then 'INT' else ReportingSource end) AS REPORTING,
DATENAME(yy, [CORP\DANIELAT].vw_Common_IOMDaniela.ClosedDate) AS YClosed,
DATENAME(m, [CORP\DANIELAT].vw_Common_IOMDaniela.ClosedDate) AS MClosed,
CAST((datename(yyyy,ClosedDate)+'-' + datename(mm,ClosedDate))as datetime) as YMClosed
into #ADVtemp
FROM [CORP\DANIELAT].vw_Common_IOMDaniela
WHERE [Type] in ('Defect')
and ReportingSource ='User'
and State <>'Open'
and (Clone is null or Clone ='Not Mapped' )
and Segment in ('Advanced Apps.')
and DATEDIFF("m",[CORP\DANIELAT].vw_Common_IOMDaniela.[ClosedDate],GETDATE()) <= 24
and [CORP\DANIELAT].vw_Common_IOMDaniela.[ClosedDate] < GetDate()
and
(CASE when ([CORP\DANIELAT].vw_Common_IOMDaniela.Subsystem is not NULL) then
[CORP\DANIELAT].vw_Common_IOMDaniela.Subsystem
else 'NULL'
End) not in ('Development tools','NGIO Mfg Unit Tester','ChangeSynergy','CM Synergy','Test Tools', 'CMSynergy', 'Agile')
and(
(CASE when ([CORP\DANIELAT].vw_Common_IOMDaniela.product is not NULL) then
[CORP\DANIELAT].vw_Common_IOMDaniela.product
else 'NULL'
End)
not in ('WWFAST','ClearQuest', 'Customer Solutions' , 'Custom Work' ,'CFD - Sherwin Generics Integration', 'CFD - Platte River GEAC Integration' , 'CFD - Contractor Management','Bogus','Bogus.Test','Bogus component','Tools','Bogus CRs Only','CruiseControl.NET','FTP/TRM','Internal','<Undefined>', 'Tools.ClearQuest','Emulator','LagLog','TriLog','EnDM Tester','Meeting Minutes','QA / Process Issues / Defects','1201 - 876 Downloader (Windows App & Downloader Code)'))/* excludes WWFAST, CQ and Bogus records but also ensures records with Product is NULL are included*/
GROUP by
[Corp\Danielat].vw_Common_IOMDaniela.ClosedDate,
State,
CauseOfDefect,
Resolution,
ID,
[Corp\Danielat].vw_Common_IOMDaniela.SubSegment,
[Corp\Danielat].vw_Common_IOMDaniela.product,
ReportingSource,
ReportingSourceDetail_2
INSERT INTO @Subsegment_Temp
SELECT DISTINCT SubSegment FROM #ADVtemp
SET @rc=0
SET @I=1
SELECT @rc=COUNT(1) FROM @Subsegment_Temp
SELECT DISTINCT @MYMONTHCLOSED=MIN( YMClosed) FROM #ADVtemp
SET @MYMONTHCLOSED_ORG=@MYMONTHCLOSED
WHILE (@I <= @rc)
BEGIN
SET @Subsegment=''
SET @MYMONTHCLOSED=NULL
SET @MYMONTHCLOSED=@MYMONTHCLOSED_ORG
SELECT @Subsegment=Subsegment FROM @Subsegment_Temp WHERE SubsegmentID=@I
WHILE (@MYMONTHCLOSED<= GETDATE())
BEGIN
IF NOT EXISTS ( SELECT YMClosed FROM #ADVtemp WHERE YMClosed = @MYMONTHCLOSED AND SubSegment=@Subsegment)
INSERT INTO #ADVtemp(REPORTING,State,SubSegment,ID,MClosed,YClosed,YMClosed,NETClosed,GROSSClosed)
SELECT 'NULL2','NULL2',@Subsegment, 'NULL1', Datename(mm,@MYMONTHCLOSED) , Datename(yy,@MYMONTHCLOSED),@MYMONTHCLOSED,0,0
SET @MYMONTHCLOSED= DATEADD( MONTH, 1, @MYMONTHCLOSED)
END
SET @I=@I+1
END
select *,
CAST((datename(yyyy,GETDATE())+'-' + datename(mm,GETDATE()))as datetime) as maxMonth from #ADVtemp
where REPORTING <>'Int'
order by YMClosed
drop table #ADVtemp
Thanks
SQL server DBA
January 10, 2013 at 1:29 am
Database admin(DBA) (1/10/2013)
please see the below query.it returns 3122 rows and query is running 0.55.indexs also their and but its running on views(its basicallly views)so how to altered the query optimization this below query..please suggest me.????
My first question would be why you think that query is responding slow or behaving bad? because based on your query nobody can give you any suggestion or lead here. so please post table definition , index definition plus exec plan (and that is MUST).
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 10, 2013 at 1:43 am
Having two nested WHILE loops is likely to be a performance killer. See if you can rewrite them into a set-based operation. As Bhuvnesh mentions, please post table DDL and sample data if you need any further help.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply