• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1530
  • Last Modified:

Make SQL Query Faster

Is there as faster way to accomplish this query ..when there are some many joins...using sql 2000

Select s.region,g.title,o.perf_by,g.x_group, count(distinct c.id_number) as Cases
from clarify..table_case c
inner join clarify..table_gbst_elm g
on c.calltype2gbst_elm = g.objid
inner join clarify..table_onsite_log o
on c.objid = o.case_onsite2case
inner join clarify..table_user u
on o.perf_by = u.login_name
inner join clarify..table_close_case cc
on c.objid = cc.last_close2case
inner join clarify..table_site s
on c.case_reporter2site = s.objid
where cc.close_date between '2006-7-01' and '2006-9-30'
and s.region like 'NorthEast'
and c.x_network = 'ch1'
and g.x_group in('1','2','3','4','5','6','7','8','9','10')
group by  s.region, o.perf_by, g.title,g.x_group
order by s.region,o.perf_by, g.title,g.x_group
0
Robb Hill
Asked:
Robb Hill
  • 6
  • 2
  • 2
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
giving us the number of records on the tables involved, the indexes in place, and the time it takes to run, and finally the execution plan graph, would be a perfect start to help us telling you where the problem could be...
0
 
OtanaCommented:
Place indexes on the columns in your WHERE clause.
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
I have indexes on the columns in the where clause..but it seems to still be using the clustered index....Im thinking becuase of the joins....is there a better query structure....
as you can see from the tables......the "table_case" is the largest table.  and is aggregating before any joins are made
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
edmicmanCommented:
Is there supposed to be a wildcard in "s.region like 'NorthEast'"?  Otherwise, I'd change that to s.region='NorthEast'.  Otherwise, as mentioned above the execution plan would help....where is the bulk of the execution cost?
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
I am getting an 83% cost on the following:

Clustered index scan
object(  clarify...table_case.pk_table_case_1_13 as c
where x_network = 'ch1' (ordered forward)


0
 
Robb HillSenior .Net DeveloperAuthor Commented:
I do not know how to put the entire execution plan in here...its rather large...and to my knowlege only exports to a graphic file
0
 
edmicmanCommented:
You said everything in the WHERE clause was indexed (I assume x_network, then?), but what about the joined columns.  I found what looks like some good info here, too:

http://www.sql-server-performance.com/tips/tuning_joins_p1.aspx
0
 
mastooCommented:
How many records are in table_case and how many have x_network = 'ch1'?
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
1051148 records in table_case
628608 records have ch1
0
 
mastooCommented:
You need a fairly high specificity before Sql will seek instead of scan so I see why you'd be getting a scan on that table.  You can try the index wizard, but otherwise you need some insightful way to steer the plan towards one of the joins that could be done first and would allow something to cut down the record count first.  For instance, if close_date had a small enough window it might result in a seek for the corresponding table_case but this is difficult.  How long does it take to run now?
0
 
Robb HillSenior .Net DeveloperAuthor Commented:
ok....I have researched this issue further..and have drawn some new conclusions...which really have me confused now....tell me what you think of this scenario....
First I have revamped my query as follows:  IN this format I am forcing indexing and cutting some rows back by limiting the big table.  This is causing a seek vs a scan.  So actually the query is running in my opinion at an optimal speed.  But when I run this same query as a stored procedure..locks are created in the database....these locks do not happen when just in query analyzer...only as a stored procedure...is there something I can do within the stored procedure to prevent this...or am I missing something fundamental here.  I will paste the stored procedure in my next post.

Select s.region,g.title,o.perf_by,count(distinct t1.id_number) as Cases
from

(select c.objid, c.id_number,c.x_network,c.calltype2gbst_elm,c.case_reporter2site  from clarify..table_case c WITH (INDEX(ind_x_network))
inner join clarify..table_close_case cc WITH (INDEX(close2caseByDate))
on c.objid = cc.last_close2case
where c.x_network='ch1' and
cc.close_date between '2006-4-01' and '2006-9-30')t1

inner join clarify..table_gbst_elm g
on t1.calltype2gbst_elm = g.objid
inner join clarify..table_onsite_log o
on t1.objid = o.case_onsite2case
inner join clarify..table_user u
on o.perf_by = u.login_name
inner join clarify..table_site s
on t1.case_reporter2site = s.objid

and s.region = 'NorthEast'
and g.x_group in('1','2','3','4','5','6','7','8','9','10')
group by  s.region, g.title,o.perf_by

0
 
Robb HillSenior .Net DeveloperAuthor Commented:
CREATE Procedure Get_Completed_CasesByFSR (@FromDate datetime,
@Todate datetime, @Region varchar(30))

AS


begin
 
if upper (@Region)='ALL'  set @Region='%'
 
Select s.region,g.title,o.perf_by,count(distinct t1.id_number) as Cases
from

(select c.objid, c.id_number,c.x_network,c.calltype2gbst_elm,c.case_reporter2site  from clarify..table_case c WITH (INDEX(ind_x_network))
inner join clarify..table_close_case cc WITH (INDEX(ind_close_date))
on c.objid = cc.last_close2case
where c.x_network='ch1' and
cc.close_date between @FromDate and @ToDate)t1

inner join clarify..table_gbst_elm g
on t1.calltype2gbst_elm = g.objid
inner join clarify..table_onsite_log o
on t1.objid = o.case_onsite2case
inner join clarify..table_user u
on o.perf_by = u.login_name
inner join clarify..table_site s
on t1.case_reporter2site = s.objid

and s.region  like @Region
and g.x_group in('1','2','3','4','5','6','7','8','9','10')
group by  s.region, g.title,o.perf_by

 
end
GO
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now