Procedure for T_SQL query optimization using Select and insert into on views

  • 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

  • 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;-)

  • 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