Query Optimization - huge query,have to cut speed!

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Optimization - huge query,have to cut speed!

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-19 : 01:31:34

Hi, I need help please!
I have this huge query that needs to be optimized as it runs way to long - the query does the job but is very annoying as it runs for 30 seconds. SSRS Owner is not happy as the average report time is 5 seconds.

This is the only way i could do this query - it's amongs several tables - I have built a filter on keyword type of report - where user can insert anykeyword & will return the process that its in with a drill down of detail.

I wanted to create a table with a SSIS package that updates the table daily, the problem is that it needs to be real time.

So i first create a filter criteria & where it's like a query above that to return all processno & detail - There is a lot of replication of tables.

It is big - would anyone please be kind to have a look & see where i can optimize as this is my only way out:

SELECT ProcessNo,ProcessDescr,Description,rtrim(StartedBy) as StartedBy,ProcessStartDte,ProcessStatus,TaskStartDte,TaskEndDte,TaskStatus,TaskUser
,case when Task = '' then Outstanding else Task end as Task
,EventID,OutstandingID,DisplayName,FieldValue
FROM
(select distinct
ProcessNo
,ProcessDescr
,Description
,StartedBy
,ProcessStartDte
,ProcessStatus
,TaskStartDte
,TaskEndDte
,TaskStatus
,TaskUser
,case when EventID = 96 then 'CFG Initiate (Marketing)' else Task end as Task
,case when EventID = 96 then 95 else EventID end as EventID
,999 as OutstandingID
,'' as Outstanding
,DisplayName
,FieldValue

from
(select
Ext.Process as ProcessNo
,Ext.PDescr as ProcessDescr
,case when Ext.Description = Ext.PDescr then ltrim(rtrim(right(EvDescr.Description,len(EvDescr.Description)-charindex(': ',EvDescr.Description,1))))
else ltrim(Ext.Description) end as Description
,case when Ext.StartedBy is null then '' else CAST(Ext.StartedBy AS char(100)) end as StartedBy
,Ext.PrStDte as ProcessStartDte
,Ext.PrStatus as ProcessStatus
,Ext.TskStDate as TaskStartDte
,Ext.TaskEndDte as TaskEndDte
,Ext.TaskStatus as TaskStatus
,Ext.TaskUser as TaskUser
,Ext.Task as Task
,Ext.EventID as EventID
,Ext.DisplayName as DisplayName
,case when rtrim(Val.ControlValue) is null then '' else rtrim(Val.ControlValue) end as FieldValue

from
(select rtrim(EHist.ProcessID) as Process
,rtrim(PrDescr.Description) as PDescr
,rtrim(right(EHistTask.Description,len(EHistTask.Description)-charindex(': ',EHistTask.Description,1))) as Description
,rtrim(Init.FirstName) + ' ' + rtrim(Init.LastName) as StartedBy
,convert(nvarchar, cast(EHist.CreateDate as datetime), 6) as PrStDte
,'Open' as PrStatus
,convert(nvarchar, cast(EHistTask.CreateDate as datetime), 6) as TskStDate
,case when EHistTask.ResultDate <> 0 then convert(nvarchar, cast(EHistTask.ResultDate as datetime), 6) else '' end as TaskEndDte
,case when EHistTask.ResultDate <> 0 then 'Completed' else 'Open' end as TaskStatus
,case when EHistTask.Assigned = 'Unassigned' then rtrim(role.description) else rtrim(TaskID.FirstName) + ' ' + rtrim(TaskID.LastName) end as TaskUser
,case when Task.CreateType <> 'Dynamic Group' and EHistTask.EventID <> '95' then rtrim(Ev.Description) + ' (' + rtrim(TaskGrp.Description) + ')'
when Task.CreateType = 'Dynamic Group' and EHistTask.EventID in ('96','101') then rtrim(Ev.Description) + ' (Marketing)'
when EHistTask.EventID = '95' then 'CFG Initiate (Marketing)' end as Task
,rtrim(EHistTask.EventID) as EventID
,rtrim(max_pr.max_id) as ID
,rtrim(Ctrl.Displayname) as DisplayName
,rtrim(Ctrl.name) as FieldName

from dbo.fcEventCreate EvCr

left join dbo.fcEventHist EHist
on EHist.EventID = EvCr.ID
and EvCr.CreateType = 'First Activity'
and EHist.Description is not null

left join dbo.fcUser as Init
on EHist.CreateId = Init.RoleId

left join dbo.fcEventHist EHistTask
on EHistTask.ProcessID = EHist.ProcessID

join (select max(v5.ID) as max_id,v5.processid as Process, v5.EventID as Event from dbo.fcEventHist v5 group by v5.processid,v5.EventID) max_pr
on max_pr.max_id = EHistTask.ID
and max_pr.Process = EHistTask.ProcessID
and max_pr.Event = EHistTask.EventID

left join dbo.fcUser as TaskID
on TaskID.RoleId = EHistTask.ResultID

left join dbo.fcRoleGroup as Role
on Role.ID = EHistTask.ResultID

left join dbo.fcEvent Ev
on Ev.ID = EHistTask.EventID

left join dbo.fcEventCreate Task
on Task.EventID = EHistTask.EventID

left join dbo.fcRoleGroup TaskGrp
on TaskGrp.ID = Task.CreateID

left join dbo.fcEventControl Ctrl
on Ctrl.EventID = max_pr.Event
and Ctrl.Type <> 'Reference Label'
and Ctrl.Options not like '%<visible>False</visible>%'
and Ctrl.Displayname not in ('Save','Done')
and Ctrl.Displayname not like '%Reject%'

left join dbo.fcProcess PrDescr
on PrDescr.ID = Ev.ProcessID

where Ev.ID not between 59 and 94 and Ev.ID <> 107

) Ext

left join dbo.fcEventValue as Val
on Ext.ID = Val.EventHistID
and Ext.FieldName = Val.ControlID

left join dbo.fcEventHist EvDescr
on EvDescr.ProcessID = Ext.Process
and EvDescr.EventID = Ext.EventID+1

UNION ALL

select ExtArch.Process as ProcessNo
,ExtArch.PDescr as ProcessDescr
,case when ExtArch.Description = ExtArch.PDescr then ltrim(rtrim(right(EvDescr2.Description,len(EvDescr2.Description)-charindex(': ',EvDescr2.Description,1))))
else ltrim(ExtArch.Description) end as Description
,case when ExtArch.StartedBy is null then '' else CAST(ExtArch.StartedBy AS char(100)) end as StartedBy
,ExtArch.PrStDte as ProcessStartDte
,ExtArch.PrStatus as ProcessStatus
,ExtArch.TskStDate as TaskStartDte
,ExtArch.TaskEndDte as TaskEndDte
,ExtArch.TaskStatus as TaskStatus
,ExtArch.TaskUser as TaskUser
,ExtArch.TaskArch as Task
,ExtArch.EventID as EventID
,ExtArch.DisplayName as DisplayName
,case when rtrim(ValArch.ControlValue) is null then '' else rtrim(ValArch.ControlValue) end as FieldValue

from
(
select rtrim(EventIDArch.ProcessID) as Process
,rtrim(PrDescrArch.Description) as PDescr
,rtrim(right(EHistTaskArch.Description,len(EHistTaskArch.Description)-charindex(': ',EHistTaskArch.Description,1))) as Description
,rtrim(InitArch.FirstName) + ' ' + rtrim(InitArch.LastName) as StartedBy
,convert(nvarchar, cast(EventIDArch.CreateDate as datetime), 6) as PrStDte
,'Closed' as PrStatus
,convert(nvarchar, cast(EHistTaskArch.CreateDate as datetime), 6) as TskStDate
,case when EHistTaskArch.ResultDate <> 0 then convert(nvarchar, cast(EHistTaskArch.ResultDate as datetime), 6) else '' end as TaskEndDte
,case when EHistTaskArch.ResultDate <> 0 then 'Completed' else 'Open' end as TaskStatus
,case when EHistTaskArch.Assigned = 'Unassigned' then rtrim(RoleArch.description) else rtrim(TaskIDArch.FirstName) + ' ' + rtrim(TaskIDArch.LastName) end as TaskUser
,case when TaskArch.CreateType <> 'Dynamic Group' and EHistTaskArch.EventID <> '95' then rtrim(EvArch.Description) + ' (' + rtrim(TaskGrpArch.Description) + ')'
when TaskArch.CreateType = 'Dynamic Group' and EHistTaskArch.EventID in ('96','101') then rtrim(EvArch.Description) + ' (Marketing)'
when EHistTaskArch.EventID = '95' then 'CFG Initiate (Marketing)' end as TaskArch
,rtrim(EHistTaskArch.EventID) as EventID
,rtrim(max_prArch.max_idArch) as ID
,rtrim(CtrlArch.Displayname) as DisplayName
,rtrim(CtrlArch.name) as FieldName

from dbo.fcEventCreate EvCrArch

left join dbo.fcEventArchive EventIDArch
on EventIDArch.EventID = EvCrArch.ID
and EvCrArch.CreateType = 'First Activity'
and EventIDArch.Description is not null

left join dbo.fcUser as InitArch
on EventIDArch.CreateId = InitArch.RoleId

left join dbo.fcEventArchive EHistTaskArch
on EHistTaskArch.ProcessID = EventIDArch.ProcessID

join (select max(v5Arch.EventHistID) as max_idArch,v5Arch.processid as Process, v5Arch.EventID as Event from dbo.fcEventArchive v5Arch group by v5Arch.processid,v5Arch.EventID) max_prArch
on max_prArch.max_idArch = EHistTaskArch.EventHistID
and max_prArch.Process = EHistTaskArch.ProcessID
and max_prArch.Event = EHistTaskArch.EventID

left join dbo.fcUser as TaskIDArch
on TaskIDArch.RoleId = EHistTaskArch.ResultID

left join dbo.fcRoleGroup as RoleArch
on RoleArch.ID = EHistTaskArch.ResultID

left join dbo.fcEvent EvArch
on EvArch.ID = EHistTaskArch.EventID

left join dbo.fcEventCreate TaskArch
on TaskArch.EventID = EHistTaskArch.EventID

left join dbo.fcRoleGroup TaskGrpArch
on TaskGrpArch.ID = TaskArch.CreateID

left join dbo.fcEventControl CtrlArch
on CtrlArch.EventID = max_prArch.Event
and CtrlArch.Type <> 'Reference Label'
and CtrlArch.Options not like '%<visible>False</visible>%'
and CtrlArch.Displayname not in ('Save','Done')
and CtrlArch.Displayname not like '%Reject%'

left join dbo.fcProcess PrDescrArch
on PrDescrArch.ID = EvArch.ProcessID

where EvArch.ID not between 59 and 94 and EvArch.ID <> 107

) ExtArch

left join dbo.fcEventValueArchive as ValArch
on ExtArch.ID = ValArch.EventHistID
and ExtArch.FieldName = ValArch.ControlID

left join dbo.fcEventArchive EvDescr2
on EvDescr2.ProcessID = ExtArch.Process
and EvDescr2.EventID = ExtArch.EventID+1

) V99

UNION ALL

select distinct ProcessNo, ProcessDescr, Description, StartedBy, ProcessStartDte, ProcessStatus, TaskStartDte, TaskEndDte, TaskStatus, TaskUser, Task, EventID
,case when vout.OutstandingID is null or vout.OutstandingID = '' then 998 else vout.OutstandingID end as OutstandingID
,case when vout.Outstanding is null or vout.Outstanding = '' then 'No waiting tasks' else rtrim(vout.Outstanding) end as Oustanding
,DisplayName,FieldValue
from
(
select
EHistTask2.Processid as ProcessNo
,rtrim(PrDescr2.Description) as ProcessDescr
,'' as Description
,'' as StartedBy
,'' as ProcessStartDte
,'' as ProcessStatus
,'' as TaskStartDte
,'' as TaskEndDte
,'' as TaskStatus
,'' as TaskUser
,'' as Task
,999 as EventID
,rtrim(OTask.ID) as OutstandingID
,case when Task2.CreateType <> 'Dynamic Group' and OTask.ID <> '95' then rtrim(OTask.Description) + ' (' + rtrim(TaskGrp2.Description) + ')'
when Task2.CreateType = 'Dynamic Group' and OTask.ID in ('96','101') then rtrim(OTask.Description) + ' (Marketing)'
when OTask.ID = '95' then 'CFG Initiate (Marketing)' end as Outstanding
,'' as DisplayName
,'' as FieldValue

from dbo.fcEventHist EHistTask2

left join dbo.fcEvent Ev2
on Ev2.ID = EHistTask2.EventID

left join dbo.fcEvent OTask
on OTask.ProcessID = Ev2.ProcessID
and OTask.ID not in (select Hist2.EventID from dbo.fcEventHist Hist2 where Hist2.ProcessID = EHistTask2.ProcessID)

left join dbo.fcEventCreate Task2
on Task2.EventID = OTask.ID

left join dbo.fcRoleGroup TaskGrp2
on TaskGrp2.ID = Task2.CreateID

left join dbo.fcProcess PrDescr2
on PrDescr2.ID = Ev2.ProcessID

where Ev2.ID not between 59 and 94 and Ev2.ID <> 107

)vout

group by
ProcessNo
,ProcessDescr
,Description
,StartedBy
,ProcessStartDte
,ProcessStatus
,TaskStartDte
,TaskEndDte
,TaskStatus
,TaskUser
,Task
,EventID
,Outstanding
,OutstandingID
,DisplayName
,FieldValue

) v103

WHERE ProcessNo IN

(SELECT distinct ProcessFilter from
(SELECT distinct
rtrim(CAST(ProcessFilter AS char(100)))
+ ' ' + rtrim(CAST(DescriptionFilter AS char(100)))
+ ' ' + rtrim(CAST(StartedByFilter AS char(100)))
+ ' ' + rtrim(CAST(TaskUserFilter AS char(100)))
+ ' ' + rtrim(CAST(FieldValueFilter AS char(400))) as Filter
,rtrim(ProcessFilter) as ProcessFilter

FROM
(select ProcessID3 as ProcessFilter ,StartedBy3 as StartedByFilter, TaskUser3 as TaskUserFilter
,case when v3.Description3 = v3.PDescr3 then ltrim(rtrim(right(EvDescr3.Description,len(EvDescr3.Description)-charindex(': ',EvDescr3.Description,1)))) else ltrim(v3.Description3) end as DescriptionFilter
,case when rtrim(Val3.ControlValue) is null then '' else rtrim(Val3.ControlValue) end as FieldValueFilter
from
(select
rtrim(EHistTask3.ProcessID) as ProcessID3
,rtrim(PrDescr3.Description) as PDescr3
,LTRIM(rtrim(right(EHistTask3.Description,len(EHistTask3.Description)-charindex(': ',EHistTask3.Description,1)))) as Description3
,rtrim(Init3.FirstName) + ' ' + rtrim(Init3.LastName) as StartedBy3
,case when EHistTask3.Assigned = 'Unassigned' then rtrim(role3.description) else rtrim(TaskID3.FirstName) + ' ' + rtrim(TaskID3.LastName) end as TaskUser3
,rtrim(EHistTask3.EventID) as EventID3
,rtrim(max_pr3.max_id) as ID3
,rtrim(Ctrl3.name) as FieldName3

from dbo.fcEventCreate EvCr3

left join dbo.fcEventHist EHist3
on EHist3.EventID = EvCr3.ID
and EvCr3.CreateType = 'First Activity'
and EHist3.Description is not null

left join dbo.fcUser as Init3
on EHist3.CreateId = Init3.RoleId

left join dbo.fcEventHist EHistTask3
on EHistTask3.ProcessID = EHist3.ProcessID

join (select max(v5.ID) as max_id,v5.processid as Process, v5.EventID as Event from dbo.fcEventHist v5 group by v5.processid,v5.EventID) max_pr3
on max_pr3.max_id = EHistTask3.ID
and max_pr3.Process = EHistTask3.ProcessID
and max_pr3.Event = EHistTask3.EventID

left join dbo.fcUser as TaskID3
on TaskID3.RoleId = EHistTask3.ResultID

left join dbo.fcRoleGroup as Role3
on Role3.ID = EHistTask3.ResultID

left join dbo.fcEvent Ev3
on Ev3.ID = EHistTask3.EventID

left join dbo.fcEventControl Ctrl3
on Ctrl3.EventID = max_pr3.Event
and Ctrl3.Type <> 'Reference Label'
and Ctrl3.Options not like '%<visible>False</visible>%'
and Ctrl3.Displayname not in ('Save','Done')
and Ctrl3.Displayname not like '%Reject%'

left join dbo.fcProcess PrDescr3
on PrDescr3.ID = Ev3.ProcessID

where Ev3.ID not between 59 and 94 and Ev3.ID <> 107

) v3

left join dbo.fcEventValue as Val3
on v3.ID3 = Val3.EventHistID
and v3.FieldName3 = Val3.ControlID

left join dbo.fcEventHist EvDescr3
on EvDescr3.ProcessID = v3.ProcessID3
and EvDescr3.EventID = v3.EventID3+1

UNION ALL

select ProcessID4 as ProcessFilter,StartedBy4 as StartedByFilter, TaskUser4 as TaskUserFilter
,case when v4.Description4 = v4.PDescr4 then ltrim(rtrim(right(EvDescr4.Description,len(EvDescr4.Description)-charindex(': ',EvDescr4.Description,1)))) else ltrim(v4.Description4) end as DescriptionFilter
,case when rtrim(Val4.ControlValue) is null then '' else rtrim(Val4.ControlValue) end as FieldValueFilter
from
(
select
rtrim(EHist4.ProcessID) as ProcessID4
,rtrim(PrDescr4.Description) as PDescr4
,LTRIM(rtrim(right(EHistTask4.Description,len(EHistTask4.Description)-charindex(': ',EHistTask4.Description,1)))) as Description4
,rtrim(Init4.FirstName) + ' ' + rtrim(Init4.LastName) as StartedBy4
,case when EHistTask4.Assigned = 'Unassigned' then rtrim(role4.description) else rtrim(TaskID4.FirstName) + ' ' + rtrim(TaskID4.LastName) end as TaskUser4
,rtrim(EHistTask4.EventID) as EventID4
,rtrim(max_pr4.max_id6) as ID4
,rtrim(Ctrl4.name) as FieldName4

from dbo.fcEventCreate EvCr4

left join dbo.fcEventArchive EHist4
on EHist4.EventID = EvCr4.ID
and EvCr4.CreateType = 'First Activity'
and EHist4.Description is not null

left join dbo.fcUser as Init4
on EHist4.CreateId = Init4.RoleId

left join dbo.fcEventArchive EHistTask4
on EHistTask4.ProcessID = EHist4.ProcessID

join (select max(v6.EventHistID) as max_id6,v6.processid as Process, v6.EventID as Event from dbo.fcEventArchive v6 group by v6.processid,v6.EventID) max_pr4
on max_pr4.max_id6 = EHistTask4.EventHistID
and max_pr4.Process = EHistTask4.ProcessID
and max_pr4.Event = EHistTask4.EventID

left join dbo.fcUser as TaskID4
on TaskID4.RoleId = EHistTask4.ResultID

left join dbo.fcRoleGroup as Role4
on Role4.ID = EHistTask4.ResultID

left join dbo.fcEvent Ev4
on Ev4.ID = EHistTask4.EventID

left join dbo.fcEventControl Ctrl4
on Ctrl4.EventID = max_pr4.Event
and Ctrl4.Type <> 'Reference Label'
and Ctrl4.Options not like '%<visible>False</visible>%'
and Ctrl4.Displayname not in ('Save','Done')
and Ctrl4.Displayname not like '%Reject%'

left join dbo.fcProcess PrDescr4
on PrDescr4.ID = Ev4.ProcessID

where Ev4.ID not between 59 and 94 and Ev4.ID <> 107

) v4

left join dbo.fcEventValueArchive as Val4
on v4.ID4 = Val4.EventHistID
and v4.FieldName4 = Val4.ControlID

left join dbo.fcEventArchive EvDescr4
on EvDescr4.ProcessID = v4.ProcessID4
and EvDescr4.EventID = v4.EventID4+1

) VFILTER
) VFILTER2

where Filter like '%' + rtrim(@Key) + '%'

)

group by
ProcessNo
,ProcessDescr
,Description
,StartedBy
,ProcessStartDte
,ProcessStatus
,TaskStartDte
,TaskEndDte
,TaskStatus
,TaskUser
,Task
,EventID
,OutstandingID
,Outstanding
,DisplayName
,FieldValue

order by
ProcessNo
,cast(EventID as int)
,cast(OutstandingID as int)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 02:16:49

One way to start on this would be to analyse the execution plan for query and see costly steps and trying to optimize them. look for table scans and identify what indexes need to be introduced to avoid them.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-19 : 02:29:57

Database Engine Tuning Advisor is a best tool to tune your query specially when your r stuck

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-19 : 03:37:57

Thanks for the assistance!

Upon opening i have access to the database then i get the following error: TITLE: Database Engine Tuning Advisor

Failed to open a new connection.
In order to perform tuning you have to be a member of sysadmin fixed server role (DTAClient)

Any tips on how to use the tool as well?

Regards

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-19 : 04:36:41

yes user must have sysadmin server role, Go to Security in SSMS right click on your user name and select properties, select server roles and change check box value to true against sysadmin.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 04:43:52

All your unions are almost identical. Start there.

E 12°55'05.63"
N 56°04'39.26"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-19 : 06:15:32

You are all so kind - I tried setting the role to sysadmin but still permission denied as i have to be a member.
Spoke to the DBA and reluctantly not allowing me access as he says i would be able to see all the databases(HR).

All my unions are very identical but different tables.
Ones are archive tb and other lookups with live tables.

At the bottom in me filter criteria a build the entire query twice to build the Filter keyword criteria - and then above I do unions from archive and test and to see outsanding data belonging to the filtered keyword.

I'm kind of building a small tracking app. Where it tracks all keywords & drills down to the lowest level.

What is my next step?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-19 : 06:55:04

quote:
Originally posted by lionofdezert

yes user must have sysadmin server role, Go to Security in SSMS right click on your user name and select properties, select server roles and change check box value to true against sysadmin.

What!!! So you wish everybody can be Sysadmin in Box.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-19 : 06:55:59

If the query is running really slow, Give that query to DBA and that's his job to make faster.

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-20 : 14:01:02

Hi, no i don't want to make evryone sysadmin. I'm trying to a keyword filter drill down report. Where the keyword can match any value in a records and return header drilling down to detail.

No this DBA does not care full of moods, so i'm stuck!!!

Don't know what to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources