How to increase the speed of the following query

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

How to increase the speed of the following query

Author

Topic

muthu76
Starting Member

6 Posts

Posted - 2009-09-24 : 03:30:00

When I try to execute the following query, Its takes 6 seconds.
I need to reduce the query to zero seconds.
What I will do to reduce the query to zero seconds.

SELECT d_case.c12_user_case_num, d_task.dt_original_open, d_task.dt_request_close, d_task.si_assignment_rec_type, d_task.c1_case_status,
d_task.si_case_disposition, d_task.si_task_type, d_emp.c30_emp_last_nm, d_involved.c30_last_nm, d_involved.c15_first_nm,
l_case_disp_status.c50_case_disp_status_descr, l_task_assign_type.c20_task_assign_descr
FROM d_task INNER JOIN
d_case ON d_task.i_case_id = d_case.i_case_id INNER JOIN
d_involved ON d_task.i_case_id = d_involved.i_case_id INNER JOIN
l_case_disp_status ON d_task.si_case_disposition = l_case_disp_status.i_case_disp_status_id INNER JOIN
l_task_assign_type ON d_task.si_assignment_rec_type = l_task_assign_type.i_task_assign_type_id INNER JOIN
d_emp ON d_task.i_emp_sys_id = d_emp.i_emp_sys_id
WHERE (d_task.si_task_type IN (860, 4230)) AND (d_task.c1_case_status = 'C') AND (d_task.dt_request_close >= '1/1/1885') AND
(d_task.dt_request_close <= '1/2/2009') AND (d_case.c1_ref_bus_grp_id = 'I') AND (d_task.si_case_disposition IN (34, 35, 37, 47, 51, 66, 67, 108, 45,
116, 117)) AND (l_task_assign_type.i_task_assign_type_id = 2) AND (d_involved.si_involved_type_id = 60)
ORDER BY d_emp.c30_emp_last_nm

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-24 : 10:35:03

This answer, as well as an explanation of query optimization in general, can be long and involved. So Here are some general question that could lead you to discover the problem:

Do all the tables have a primary key?
Do the tables have a clustered index?
Are other columns involved in JOIN and WHERE criteria indexed? (not that they all should be but some will likely be needed)
Are index statistics up to date?
Have you looked at the estimated and/or actual execution plan?

You should add some formatting, table aliases, and go easy on the parentheses just to make your queries easier to read - at least for our sake if not yours

. Here's what I mean:


SELECT c.c12_user_case_num
       ,t.dt_original_open
       ,t.dt_request_close
       ,t.si_assignment_rec_type
       ,t.c1_case_status
       ,t.si_case_disposition
       ,t.si_task_type
       ,e.c30_emp_last_nm
       ,i.c30_last_nm
       ,i.c15_first_nm
       ,cs.c50_case_disp_status_descr
       ,at.c20_task_assign_descr

FROM   d_task t
INNER JOIN d_case c
       ON t.i_case_id = c.i_case_id 
INNER JOIN d_involved i
       ON t.i_case_id = i.i_case_id 
INNER JOIN l_case_disp_status  cs
       ON t.si_case_disposition = cs.i_case_disp_status_id 
INNER JOIN l_task_assign_type at
       ON t.si_assignment_rec_type = at.i_task_assign_type_id 
INNER JOIN d_emp e
       ON t.i_emp_sys_id = e.i_emp_sys_id

WHERE  t.si_task_type IN (860, 4230)
AND    t.c1_case_status = 'C'
AND    t.dt_request_close >= '1/1/1885'
AND    t.dt_request_close <= '1/2/2009'
AND    c.c1_ref_bus_grp_id = 'I'
AND    t.si_case_disposition IN (34, 35, 37, 47, 51, 66, 67, 108, 45, 116, 117)
AND    at.i_task_assign_type_id = 2
AND    i.si_involved_type_id = 60

ORDER BY e.c30_emp_last_nm

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources