Home » RDBMS Server » Performance Tuning » Performance tuning
Performance tuning [message #394424] Fri, 27 March 2009 00:47 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
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
Re: Performance tuning [message #394425 is a reply to message #394424] Fri, 27 March 2009 00:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I want to tune a SQL server query
so why are you posting to an Oracle forum?
Re: Performance tuning [message #394462 is a reply to message #394424] Fri, 27 March 2009 04:44 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
can iu get some inputs on how to tune this query so that i can make modifications using sql server syntax .

request you all to pint out teh mistakes so that i can increase teh performance of this query





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), Cool, ' ', '-') 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), Cool, ' ', '-')
,CONVERT(CHAR(6),IR.Date_End, 112)) a
Order by Date_End
Go to Top of Page
Re: Performance tuning [message #394464 is a reply to message #394462] Fri, 27 March 2009 04:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
avrillavinge wrote on Fri, 27 March 2009 09:44
can iu get some inputs on how to tune this query so that i can make modifications using sql server syntax .

request you all to pint out teh mistakes so that i can increase teh performance of this query




While I can see several mistakes in those two sentences (proof read your messages before posting please) I can't see any in the query, but then I don't know what to look for.

Oracle works differently to sql server, especially top n queries.
Changes that would make an oracle version of this query faster could make the sqlserver version slower.
You need to post this in a sqlserver forum.
Re: Performance tuning [message #394465 is a reply to message #394424] Fri, 27 March 2009 04:57 Go to previous message
avrillavinge
Messages: 98
Registered: July 2007
Member
Thanks .

I have already posted in sql server forum .

I have worked on oracle hence i was looking for oracle related performance tuning but as u said it varies from DB to Db is agreed.

Alo when i remove teh group by clause teh query runs in 1 sdec and with the group by clause it takes 19 sec.\\

Any thoughts on this !! if any please post
Previous Topic: Tkprof trace file meaning
Next Topic: AWR report analysis
Goto Forum:
  


Current Time: Sat Dec 10 08:48:54 CST 2016

Total time taken to generate the page: 0.04369 seconds