July 30, 2008 at 11:48 am
I was wondering if there is a better, faster way to script the following.....
declare @begin_dt datetime, @end_dt datetime
set @begin_dt = '7/1/2008'
set @end_dt = '7/15/2008'
select distinct substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) as sub_category,
ARTS = (select count(*) from call_req cr
join prob_ctg pc on
pc.persid = cr.category
join ca_contact ca on
ca.contact_uuid = cr.group_id
where substring(pc.sym,11,charindex('.',pc.sym,11) - 11) = 'ARTS' -- category
and substring(pc.sym,charindex('.',pc.sym,11) + 1,len(rtrim(pc.sym)) - charindex('.',pc.sym,11)) = substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) -- sub-category
and substring(cr.ref_num,1,3) in ('INC','REQ')
and substring(pc.sym,1,9) = 'IT.FldSvc'
and cr.status <> 'CANCLD'
and dateadd(hh,-5,dateadd(s,cr.open_date,'1970')) between @begin_dt and @end_dt
and substring(ca.last_name,1,9) = 'IT.FldSvc'
and substring(ca.last_name,11,len(ca.last_name) - 10) = 'ARTS'), -- group
Atlanta = (select count(*) from call_req cr
join prob_ctg pc on
pc.persid = cr.category
join ca_contact ca on
ca.contact_uuid = cr.group_id
where substring(pc.sym,11,charindex('.',pc.sym,11) - 11) = 'Atlanta' -- category
and substring(pc.sym,charindex('.',pc.sym,11) + 1,len(rtrim(pc.sym)) - charindex('.',pc.sym,11)) = substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) -- sub-category
and substring(cr.ref_num,1,3) in ('INC','REQ')
and substring(pc.sym,1,9) = 'IT.FldSvc'
and cr.status <> 'CANCLD'
and dateadd(hh,-5,dateadd(s,cr.open_date,'1970')) between @begin_dt and @end_dt
and substring(ca.last_name,1,9) = 'IT.FldSvc'
and substring(ca.last_name,11,len(ca.last_name) - 10) = 'Atlanta'), -- group
Bing = (select count(*) from call_req cr
join prob_ctg pc on
pc.persid = cr.category
join ca_contact ca on
ca.contact_uuid = cr.group_id
where substring(pc.sym,11,charindex('.',pc.sym,11) - 11) = 'Bing' -- category
and substring(pc.sym,charindex('.',pc.sym,11) + 1,len(rtrim(pc.sym)) - charindex('.',pc.sym,11)) = substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) -- sub-category
and substring(cr.ref_num,1,3) in ('INC','REQ')
and substring(pc.sym,1,9) = 'IT.FldSvc'
and cr.status <> 'CANCLD'
and dateadd(hh,-5,dateadd(s,cr.open_date,'1970')) between @begin_dt and @end_dt
and substring(ca.last_name,1,9) = 'IT.FldSvc'
and substring(ca.last_name,11,len(ca.last_name) - 10) = 'Bing')
from prob_ctg pc1
where pc1.sym like 'it.fldsvc%'
order by substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11))
July 30, 2008 at 12:22 pm
Just based on the number of correlated subqueries in your code I would say yes there is a better and faster way. Probably the best way for someone to get you the better way is to post the table schemas, some test data, a desired outcome and an explanation of what the code is supposed to accomplish.
I spent about 2 minutes just trying to figure out the code and got to the 2nd subquery and gave up, which may be why you have not gotten any other answers. With the information I suggested I may have been able to give a possible solution in that time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 1:01 pm
Jack Corbett (7/30/2008)
Just based on the number of correlated subqueries in your code I would say yes there is a better and faster way. Probably the best way for someone to get you the better way is to post the table schemas, some test data, a desired outcome and an explanation of what the code is supposed to accomplish.I spent about 2 minutes just trying to figure out the code and got to the 2nd subquery and gave up, which may be why you have not gotten any other answers. With the information I suggested I may have been able to give a possible solution in that time.
sorry I should've shorten it down. would this help?
declare @begin_dt datetime, @end_dt datetime
set @begin_dt = '7/1/2008'
set @end_dt = '7/15/2008'
select distinct substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) as sub_category,
ARTS = (select count(*) from call_req cr
join prob_ctg pc on
pc.persid = cr.category
join ca_contact ca on
ca.contact_uuid = cr.group_id
where substring(pc.sym,11,charindex('.',pc.sym,11) - 11) = 'ARTS' -- category
and substring(pc.sym,charindex('.',pc.sym,11) + 1,len(rtrim(pc.sym)) - charindex('.',pc.sym,11)) = substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) -- sub-category
and substring(cr.ref_num,1,3) in ('INC','REQ')
and substring(pc.sym,1,9) = 'IT.FldSvc'
and cr.status <> 'CANCLD'
and dateadd(hh,-5,dateadd(s,cr.open_date,'1970')) between @begin_dt and @end_dt
and substring(ca.last_name,1,9) = 'IT.FldSvc'
and substring(ca.last_name,11,len(ca.last_name) - 10) = 'ARTS') -- group
from prob_ctg pc1
where pc1.sym like 'it.fldsvc%'
and pc1.del = 0 --active categories
order by sub_category
The WHERE clause if retuning all active sub-categories. In the SELECT, i am getting a count for "ARTS" where it matches to the sub-categories in the WHERE clause. I had to do alot of string manipulation to get "ARTS" and the sub-categories since its part of a longer string.
July 30, 2008 at 1:38 pm
if possible, get those embedded fields into seperate columns via a view or computed columns! that'll make the code so much easier to follow and depending on your db settings, you could even index the categories and sub_categories.
create table #test ( sym varchar(255) )
insert into #test values ( 'IT.FldSvc.Lower.Atlanta' )
insert into #test values ( 'IT.FldSvc.Greater.Chicago' )
insert into #test values ( 'IT.FldSvc.FAST.ARTS' )
insert into #test values ( 'IT.FldSvc.FASTER.STILL.ARTS' )
alter table #test add category as substring( sym, 11, charindex('.',sym,11) -11)
alter table #test add sub_category as substring( sym, charindex('.',sym,11) + 1,len(sym))
select * from #test
also, the dateadd()s with call_req.open_date are odd. is open_date an int and if so, why? a computed column to make a 'real' open_date would help readability and performance.
right off the bat, it looks like the ARTS=, Atlanta=, and Bing= subqueries are identical except for looking for 'ARTS', 'Atlanta', and 'Bing' so they can be combined into one.
something like this would be the result:
select sub_category,
sum( case when group_type = 'ARTS' then ct end ) as ARTS_ct,
sum( case when group_type = 'Atlanta' then ct end ) as Atlanta_ct,
sum( case when group_type = 'Bing' then ct end ) as Bing_ct
from
(
select pc1.sub_category, GT.group_type, count(*) as ct
from
(select 'ARTS' as group_type union select 'Atlanta' union select 'Bing') as GT
cross join call_req cr
join prob_ctg pc
on pc.persid = cr.category
join ca_contact ca
on ca.contact_uuid = cr.group_id
join prob_ctg pc1
on pc.category = GT.group_type -- category
and pc.sub_category = pc1.sub_category -- sub-category
where
left(cr.ref_num,3) in ('INC','REQ')
and pc.sym like 'IT.FldSvc%'
and cr.status <> 'CANCLD'
and dateadd(hh,-5,dateadd(s,cr.open_date,'1970')) between @begin_dt and @end_dt
and ca.last_name like 'IT.FldSvc'
and ca.last_name like '%'+ GT.group_type
and pc1.sym like 'IT.FldSvc'
group by pc1.sub_category, GT.group_type
) as X
group by sub_category
order by sub_category
July 30, 2008 at 2:01 pm
Wow old hand, i would have never thought of that. Then again i am still a newbie. Thanks i will try it.
btw, the date fields are in Unix format. I had to covert it to regular datetime.
July 30, 2008 at 2:11 pm
You can definitely eliminate the sub-queries if I understand what you need. I would probably do this in a couple of passes, just to make it easier to read and thus, easier to maintain.
Is opendate the # of seconds since 1/1/1970?
So for your end result you want a count of substring(pc.sym,11,charindex('.',pc.sym,11) - 11) (category) records with in each substring(pc1.sym,charindex('.',pc1.sym,11) + 1,len(rtrim(pc1.sym)) - charindex('.',pc1.sym,11)) (subcategory)?
I made major changes to the SQL and commented why, based on my understanding of the data and the desired outcome. Without some data I can't tell you for sure that this will do what you want, but I think it does:
[font="Courier New"]DECLARE @begin_dt DATETIME, @end_dt DATETIME, @start_date INT, @end_date INT
SET @begin_dt = '7/1/2008'
SET @end_dt = '7/15/2008'
/* this will set the start date and end date to the seconds as this is better than
converting on a column */
SET @start_date = DATEDIFF(second, '1/1/1970', DATEADD(hour, -5, @begin_dt))
SET @end_date = DATEDIFF(second, '1/1/1970', @end_dt)
/* The CTE gets all the data in one pass and does all the string manipulation */
;WITH cteGetCatsAndSubs AS
(
SELECT
SUBSTRING(pc.sym,11,CHARINDEX('.',pc.sym,11) - 11) AS category,
SUBSTRING(pc1.sym,CHARINDEX('.',pc1.sym,11) + 1,LEN(RTRIM(pc1.sym)) - CHARINDEX('.',pc1.sym,11)) AS sub_category,
persid,
del,
SUBSTRING(ca.last_name,11,LEN(ca.last_name) - 10) AS GROUP
FROM
prob_ctg PC JOIN
call_req CR ON
PC.persid = CR.category JOIN
ca_contact CC ON
CR.group_id = CC.contact_uuid
WHERE
PC.sym LIKE 'it.fldsvc%' AND
PC.del = 0 AND
-- I changed this to likes as I think it will scale better
(CR.ref_num LIKE 'INC%' OR CR.ref_num LIKE 'REQ%') AND
CR.status <> 'CANCLD' AND
-- I did the calculations on the variables so it should perform
CR.open_date BETWEEN @start_date AND @end_date AND
CA.last_name LIKE 'IT.FldSvc%'
)
SELECT DISTINCT
sub_category,
/* each of these will add 1 to the sum for each row that meets
the criteria thus giving you the count */
SUM(CASE
WHEN category = 'ARTS' AND GROUP = 'ARTS' THEN 1
ELSE 0
END) AS arts,
SUM(CASE
WHEN category = 'BING' AND GROUP = 'BING' THEN 1
ELSE 0
END) AS bing,
SUM(CASE
WHEN category = 'ATLANTA' AND GROUP = 'ATLANTA' THEN 1
ELSE 0
END) AS ATLANTA,
SUM(CASE
WHEN category = 'ATLANTA' AND GROUP = 'ATLANTA' THEN 1
ELSE 0
END) AS ATLANTA
FROM
cteGetCatsAndSubs
GROUP BY
sub_category[/font]
If I got it right this should be MUCH faster than your original code.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2008 at 2:13 pm
antonio's solution and my solution are very similar, either should be a major improvement. I like the CTE syntax because I find it cleaner and easier to read.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply