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

Procedure for T_SQL query optimization using Select and insert into on views Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 12:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:32 AM
Points: 170, Visits: 740
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
Post #1405240
Posted Thursday, January 10, 2013 1:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1405250
Posted Thursday, January 10, 2013 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1405258
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse