Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to understand Cross Apply Expand / Collapse
Author
Message
Posted Friday, August 30, 2013 5:58 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 425, Visits: 1,679
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

Post #1490336
Posted Friday, August 30, 2013 8:29 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 12:13 AM
Points: 745, Visits: 4,776
Did you read Paul White's articles on the subject?
http://www.sqlservercentral.com/articles/APPLY/69953/
and
http://www.sqlservercentral.com/articles/APPLY/69954/
Post #1490341
Posted Saturday, August 31, 2013 7:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 4,351, Visits: 6,165
pietlinden (8/30/2013)
Did you read Paul White's articles on the subject?
http://www.sqlservercentral.com/articles/APPLY/69953/
and
http://www.sqlservercentral.com/articles/APPLY/69954/


+100


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1490383
Posted Saturday, August 31, 2013 8:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 425, Visits: 1,679
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.

Post #1490393
Posted Thursday, February 27, 2014 2:38 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 425, Visits: 1,679
In case anyone stumbles upon this old thread, these two Itzik Ben-Gan videos are awesome:

Video 1

Video 2

They cover a number of ways to use the APPLY operators, and some indexing tips for improving queries using them.

I like what he starts doing right around the 13 minute mark in video 2.
Post #1546139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse