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


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


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

Author
Message
Database admin(DBA)
Database admin(DBA)
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1372 Visits: 1482
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12652 Visits: 4077
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;-)
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33099 Visits: 16637
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
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