Query 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

Query Performance tuning

Author

Topic

disha
Starting Member

34 Posts

Posted - 2009-03-29 : 00:21:33

please help em increase the per4formance of the below query

Select
Record_Type,
SUBCATEGORY1,
Event_End1,
Event_End,
ISNULL(Count1,0) AS Count1
FROM (
Select TOP 12
'Incident Records' As Record_Type,
'CTS' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,0,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-0,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-1,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-1,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-2,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-2,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-3,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-3,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-4,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-4,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-5,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-5,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-6,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-6,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-7,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-7,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-8,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-8,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-9,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-9,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-10,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-10,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-11,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-11,getdate())), 112) Event_End) z
order by Event_End) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'CTS' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
THEN 'CTS'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
AND Event_End Between dateadd(mm,-12,getdate()) and getdate()
group by
(CASE WHEN APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
THEN 'CTS'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'NRG' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,0,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-0,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-1,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-1,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-2,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-2,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-3,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-3,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-4,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-4,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-5,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-5,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-6,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-6,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-7,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-7,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-8,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-8,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-9,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-9,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-10,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-10,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-11,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-11,getdate())), 112) Event_End) z
order by Event_End) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'NRG' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'Agency Portal'
THEN 'NRG'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'Agency Portal'
AND Event_End Between dateadd(mm,-12,getdate()) and getdate()
group by
(CASE WHEN APPSLIST = 'Agency Portal'
THEN 'NRG'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'TIBCO' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,0,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-0,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-1,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-1,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-2,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-2,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-3,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-3,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-4,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-4,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-5,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-5,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-6,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-6,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-7,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-7,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-8,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-8,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-9,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-9,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-10,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-10,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-11,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-11,getdate())), 112) Event_End) z
order by Event_End) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'TIBCO' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'ETS-TIBCO'
THEN 'TIBCO'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'ETS-TIBCO'
AND Event_End Between dateadd(mm,-12,getdate()) and getdate()
group by
(CASE WHEN APPSLIST = 'ETS-TIBCO'
THEN 'TIBCO'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'ALLIANCE' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,0,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-0,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-1,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-1,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-2,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-2,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-3,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-3,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-4,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-4,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-5,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-5,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-6,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-6,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-7,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-7,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-8,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-8,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-9,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-9,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-10,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-10,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-11,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-11,getdate())), 112) Event_End) z
order by Event_End) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'ALLIANCE' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
THEN 'ALLIANCE'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
AND Event_End Between dateadd(mm,-12,getdate()) and getdate()
group by
(CASE WHEN APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
THEN 'ALLIANCE'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'ETS-EDMS' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,0,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-0,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-1,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-1,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-2,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-2,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-3,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-3,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-4,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-4,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-5,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-5,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-6,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-6,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-7,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-7,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-8,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-8,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-9,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-9,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-10,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-10,getdate())), 112) Event_End
UNION ALL
select
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-11,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-11,getdate())), 112) Event_End) z
order by Event_End) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'ETS-EDMS' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'ETS-EDMS'
THEN 'ETS-EDMS'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'ETS-EDMS'
AND Event_End Between dateadd(mm,-12,getdate()) and getdate()
group by
(CASE WHEN APPSLIST = 'ETS-EDMS'
THEN 'ETS-EDMS'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
) Final

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-29 : 03:17:21

Please post table definitions, index definitions and, if this is SQL 2005, the execution plan saved as a .sqlplan file, zipped and attached.

--
Gail Shaw
SQL Server MVP

disha
Starting Member

34 Posts

Posted - 2009-03-30 : 04:48:01

for security reasons i cant do it .

but can u guide me on how can i improve performance of my query

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-30 : 05:05:01

Without seeing at least the exec plan, I really can't help you much. The query's massive and without seeing how it's running I'm guessing as to where the problems might be.

Maybe try breaking the subqueries out, inserting their results into temp tables, indexing those temp tables and joining then for the final query. It might help.

--
Gail Shaw
SQL Server MVP

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-30 : 05:20:04

You've given table names and column names - if you give a script to generate these empty tables and give script to insert dummy data we can replicate your situation and test any solutions. If you have security considerations presumably you have already changed the names of tables / columns already.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Can you apply the advice already given at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122599
This will clean up the query and then we can get to more tuning.

matty
Posting Yak Master

161 Posts

Posted - 2009-03-30 : 05:34:36

The UNION ALL portion of your code can be replaced with the following code.It might improve performance.

Select Event_End1, Event_End
from
(
SELECT
REPLACE(RIGHT(CONVERT(VARCHAR(11), (dateadd(mm,-number,getdate())), 106), 8), ' ', '-') Event_End1,
CONVERT(CHAR(6),(dateadd(mm,-number,getdate())), 112) Event_End
FROM master..spt_values m
WHERE type='P' AND number BETWEEN 0 AND 11
)t

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-30 : 06:12:06

quote:
Originally posted by GilaMonster

Without seeing at least the exec plan, I really can't help you much. The query's massive and without seeing how it's running I'm guessing as to where the problems might be.

Maybe try breaking the subqueries out, inserting their results into temp tables, indexing those temp tables and joining then for the final query. It might help.

--
Gail Shaw
SQL Server MVP

From personal experience that can either help amazingly or hurt horribly

If this code isn't executed very often and takes a while to run then the temp table idea will probably help a lot.

If the code gets executed a lot then the temp table approach will probably introduce a lot of query plan recompiles that can really hurt you.

You haven't told us the mean time it takes to run or how much data it is processing. Apart from the query itself you've really told us nothing at all about what your database is like -- how much data you are dealing with. Both of those are vitally important!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-30 : 06:53:21

I've put the current query cleaned up a little below. It is basically 5 main UNION ALLs which I've spit up with ------ lines. You can use UNPIVOT to combine these into a single query.

declare @DT datetime
set @DT = @DT

Select
Record_Type,
SUBCATEGORY1,
Event_End1,
Event_End,
ISNULL(Count1,0) AS Count1
FROM (
Select TOP 12
'Incident Records' As Record_Type,
'CTS' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--...see matty's post
) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'CTS' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
THEN 'CTS'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST IN ('CTS-NG-PROD','CTS-NG-PROD-ORACLE')
THEN 'CTS'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
UNION ALL
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
Select TOP 12
'Incident Records' As Record_Type,
'NRG' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--.... see matty's post
) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'NRG' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'Agency Portal'
THEN 'NRG'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'Agency Portal'
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST = 'Agency Portal'
THEN 'NRG'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1

-----------------------------------------------
-----------------------------------------------
-----------------------------------------------

UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'TIBCO' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--..
) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'TIBCO' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'ETS-TIBCO'
THEN 'TIBCO'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'ETS-TIBCO'
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST = 'ETS-TIBCO'
THEN 'TIBCO'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------

UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'ALLIANCE' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--...
) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'ALLIANCE' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
THEN 'ALLIANCE'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST IN ('Alliance Agency','Alliance CIC','Alliance Consumer','Alliance Education','Alliance IA')
THEN 'ALLIANCE'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
UNION ALL
Select TOP 12
'Incident Records' As Record_Type,
'ETS-EDMS' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--..
) Query1
Left outer join
(Select TOP 12
'Incident Records' As Record_Type,
'ETS-EDMS' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select 'Incident Records' As Record_Type,
(CASE WHEN APPSLIST = 'ETS-EDMS'
THEN 'ETS-EDMS'
ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'ETS-EDMS'
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST = 'ETS-EDMS'
THEN 'ETS-EDMS'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1
) Final

disha
Starting Member

34 Posts

Posted - 2009-03-30 : 06:56:06

Thanks a lot .

I will try this out and let you know.

miilion thanks i9na advance

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-30 : 09:54:12

Looking at individual UNION blocks - there seems to be quite a bit of superfluous code with constants put into Record_Type and SUBCATEGORY1 in lower-level subqueries which are not selected in upper query but again have the same constant put into it. I've included one section and commented out some repetition.

e.g.

-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
Select TOP 12
'Incident Records' As Record_Type,
'NRG' AS SUBCATEGORY1,
Query1.Event_End1,
Query1.Event_End,
Query2.Count1 from
(Select TOP 12 Event_End1, Event_End from (
--.... see matty's post
) Query1
Left outer join
(Select TOP 12
--'Incident Records' As Record_Type,
--'NRG' AS SUBCATEGORY1,
Event_End1,
Event_End,
Count1
From
(Select --'Incident Records' As Record_Type,
--(CASE WHEN APPSLIST = 'Agency Portal'
--THEN 'NRG'
--ELSE ' ' END) AS SUBCATEGORY1
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-') AS Event_End1
,CONVERT(CHAR(6),IR.Event_End, 112) AS Event_End
,Count(LOGENTRY_ID) As Count1
FROM ITSMDW.dbo.vmetric_ssr_ir_compliance IR
WHERE
Status ='Closed'
AND PARENT_LE_ID is NULL
AND APPSLIST = 'Agency Portal'
AND Event_End Between dateadd(mm,-12,@DT) and @DT
group by
(CASE WHEN APPSLIST = 'Agency Portal'
THEN 'NRG'
ELSE ' ' END)
,REPLACE(RIGHT(CONVERT(VARCHAR(11), IR.Event_End, 106), 8), ' ', '-')
,CONVERT(CHAR(6),IR.Event_End, 112)) a
Order by Event_End) Query2
on Query1.Event_End1 = Query2.Event_End1

-----------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources