August 30, 2013 at 5:58 pm
I wrote this query, which runs nicely (under 1 second) and returns 4490 rows:
select distinct t1.id, t1.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t1.orc_cell1 = 7 then 'Customer'
when t1.orc_cell1 = 9 then 'Non-Customer'
else null end as [Designation]
from j3689141 as t1 left join J3689141_IDNCHK as u on t1.phonenumber = u.phone
left join J3689141_IDNCHK2 as s on t1.phonenumber = s.phone
left join J3689141_IDNCHK3 as p on t1.phonenumber = p.phone
where (u.phone is not null or s.phone is not null or p.phone is not null)
and t1.orc_cell1 in (7, 9)
and t1.statusflag = 0
order by t1.id
After that I was a little bored, and since I still don't totally understand cross apply, I tried writing it like this:
select distinct t1.id, t1.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t1.orc_cell1 = 7 then 'Customer'
when t1.orc_cell1 = 9 then 'Non-Customer'
else null end
as [Designation]
from j3689141 t1 cross apply (
select u.phone
from j3689141_IDNCHK u
where t1.phonenumber = u.phone
) as u
cross apply (
select s.phone
from j3689141_IDNCHK2 s
where t1.phonenumber = s.phone
) as s
cross apply (
select p.phone
from j3689141_IDNCHK3 p
where t1.phonenumber = p.phone
) as p
where (u.phone is not null or s.phone is not null or p.phone is not null)
and t1.orc_cell1 in (7, 9)
and t1.statusflag = 0
order by t1.id
Which runs, but only returns one row, though not incorrectly (just 4489 rows short). Where did I go wrong? Am I
using cross apply in the wrong place?
I feel like I've read dozens of articles about it, but something isn't clicking.
Thanks
August 30, 2013 at 8:29 pm
Did you read Paul White's articles on the subject?
http://www.sqlservercentral.com/articles/APPLY/69953/
and
August 31, 2013 at 7:31 am
pietlinden (8/30/2013)
http://www.sqlservercentral.com/articles/APPLY/69953/
and
http://www.sqlservercentral.com/articles/APPLY/69954/%5B/quote%5D
+100
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2013 at 8:39 am
Right idea, wrong apply! I was so intent on finally coming up with a good use for cross apply that I didn't realize I needed an outer apply. So here's what I ended up with for both:
declare @startdt datetime
PRINT 'Left Joins';
SELECT @StartDT = GETDATE();
SET STATISTICS TIME ON;
select distinct t1.id, t1.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t1.orc_cell1 = 7 then 'Customer'
when t1.orc_cell1 = 9 then 'Non-Customer'
else null end as [Designation]
from j3689141 as t1 left join J3689141_IDNCHK as u
on t1.phonenumber = u.phone
left join J3689141_IDNCHK2 as s on t1.phonenumber = s.phone
left join J3689141_IDNCHK3 as p on t1.phonenumber = p.phone
where (u.phone is not null or s.phone is not null or p.phone is not null)
and t1.orc_cell1 in (7, 9)
and t1.statusflag = 0
order by t1.id
SET STATISTICS TIME OFF;
SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());
PRINT 'Outer Apply';
SELECT @StartDT = GETDATE();
SET STATISTICS TIME ON;
select distinct t.id, t.phonenumber, u.phone as [Universe Match]
, s.phone as [Specialty Practice Match]
, p.phone as [Primary Care Match]
, case when t.orc_cell1 = 7 then 'Customer'
when t.orc_cell1 = 9 then 'Non-Customer'
else null end as [Designation]
from j3689141 as t
outer apply (
select phone
from J3689141_IDNCHK
where t.phonenumber = phone
) as u
outer apply (
select phone
from J3689141_IDNCHK2
where t.phonenumber = phone
) as s
outer apply (
select phone
from J3689141_IDNCHK3
where t.phonenumber = phone
) as p
where(u.phone is not null or s.phone is not null or p.phone is not null)
and t.orc_cell1 in (7,9)
and t.statusflag = 0
order by t.id
SET STATISTICS TIME OFF;
SELECT ElapsedMS=DATEDIFF(millisecond, @StartDT, GETDATE());
Left Joins
SQL Server Execution Times:
CPU time = 936 ms, elapsed time = 569 ms.
Outer Apply
SQL Server Execution Times:
CPU time = 1672 ms, elapsed time = 681 ms.
The left joins always won.
February 27, 2014 at 2:38 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply