Performance tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Performance tuning

Author

Topic

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 02:41:31

I want to tune a SQL server query that has around 5 UNIONALL . Within every union all there are 3subqueries.Query is very big and gets data for 12 months for 5different Applications. I want to know apart from changing anything in query what can be done to improve perfoemnace.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 02:43:09

You'd need to post the query.

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 04:57:59

For security probs cant send query but one thing i have identified is query take stime only in group by clause. If i exclude measure and run without group by it takes only 1second else with group by it takes 19seconds

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 05:24:28

Select TOP 12
'icd_Rec' As Rec_Type,
'S11' AS CATG,
Lastdate1,
Date_End,
Count1
From
(Select 'icd_Rec' As Rec_Type,
(CASE WHEN TKNAME IN ('S11-TD-TWD','S11-TD-TWD-sfw')
THEN 'S11'
ELSE ' ' END) AS CATG
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Date_End, 106), 8), ' ', '-') AS Lastdate1
,CONVERT(CHAR(6),IR.Date_End, 112) AS Date_End
,Count(Reg_ID) As Count1
FROM dbo.vsrdt_ttp_dt_comp IR
WHERE
Status ='Closed'
AND PRT_ID is NULL
AND TKNAME IN ('S11-TD-TWD','S11-TD-TWD-sfw')
AND Date_End Between dateadd(mm,-12,getdate()) and getdate()

group by
(cASE WHEN TKNAME IN ('S11-TD','S11-sfw')
THEN 'S11'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Date_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Date_End, 112)) a
Order by Date_End

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 06:09:31

One thing i feel is there can be analytical functions in SQL server something like over partition by etc that can replace group by and mebbe that can be helpful

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 06:24:07

I had a similar issue and improved performance (about 10 times faster) using UNPIVOT and CASE.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120247

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 06:27:25

Thanks for the reply .
can you please guide me as to where the changes hould be made as i have spent two days in tuning this query but no luck .
can you point out the replacemnet changes in my query

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 07:42:31

Can you show your full query including the 5 UNION ALL's you mentioned? RE: security just change the names of columns to a, b, c etc and tables to Table1, Table2 etc. Also some create table scripts and sample data will help (again mock data in similar format will do.).

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 07:50:37

thnaks il surely post the query here .

also in the meantime can you guide me if any changes have to eb done in the above query.
for eg : any new functions which are to be used etc

please guide me on it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources