| Performance tuning [message #394424] |
Fri, 27 March 2009 00:47  |
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 #394462 is a reply to message #394424] |
Fri, 27 March 2009 04:44  |
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),  , ' ', '-') 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), , ' ', '-')
,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 |
cookiemonster
Messages: 13925
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
