Union and Sub Query - 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

Union and Sub Query

Author

Topic

disha
Starting Member

34 Posts

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

Below is my Query whioch i want to tune please help me with the correct or midifed 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), 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
Go to Top of Page

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 05:59:43

i need to performnace tune the above query please help

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 06:53:42

if you have constant 'S11' as CATG, why have the case statement in the derived table query ? What indexes do you have ?? Whats the count of records in vsrdt_ttp_dt_comp?

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 06:59:54

can you point out the changes i need to make in the query .

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:19:46

[code]
SELECT
'icd_Rec' As Rec_Type,
'S11' 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
REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Date_End, 106), 8), ' ', '-'),
CONVERT(CHAR(6),IR.Date_End, 112)
Order by
CONVERT(CHAR(6),IR.Date_End, 112)[/code]

The changes above is just removing the unnecessary bit in your code. It'll pretty much take the same time as your original query.

You'll need to check for indexing for better performance.

disha
Starting Member

34 Posts

Posted - 2009-03-27 : 08:53:42

thanks so much for the reply il check and let you know .

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 11:19:00

Placing getdate() into a variable rather than running this function for each row might help, and doing the convert to varchar once rather than in select and in the order by.

EG. declare @DT datetime
set @DT = getdate()

SELECT
Rec_Type,
CATG
,REPLACE(RIGHT(CONVERT(VARCHAR(11), I_DateEnd, 106), 8), ' ', '-') AS Lastdate1
,CONVERT(CHAR(6),I_Date_End, 112) AS Date_End
,Count1
FROM
(SELECT
'icd_Rec' As Rec_Type,
'S11' AS CATG
,IR.Date_End AS I_DateEnd
,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,@DT) and @DT
GROUP BY
IR.Date_End
) as I

Order by
I_Date_End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources