CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60811

how to combat horrific performance when joining on WITH(s)

I do not have permissions to set up indexes on these tables unfortunately.

The first two WITH's are causing this query to run for hours:

;with OneAccession as (
select client_id,COUNT(patient_id) PatientCount from
(
select client_id,patient_id
from F_ACCESSION_DAILY
group by CLIENT_ID,PATIENT_ID
having COUNT(ACCESSION_ID)=1
) a
group by CLIENT_ID
)
,

TwoAccessions as (
select client_id,COUNT(patient_id) PatientCount from
(
select client_id,patient_id
from F_ACCESSION_DAILY
group by CLIENT_ID,PATIENT_ID
having COUNT(ACCESSION_ID)=2
) a
group by client_id
)



select
f.client_id
, 12*(year(getdate())-year(min(f.received_date))) + MONTH(GETDATE())-MONTH(min(f.received_date))
, COUNT(distinct f.patient_id) TotalPatients
, COUNT(f.ACCESSION_ID) TotalSpecimens
, o.PatientCount  TotalPatientsWOneSpec
, t.PatientCount TotalPatientsWTwoSpec
from F_ACCESSION_DAILY f
join
OneAccession o
on o.CLIENT_ID=f.CLIENT_ID
join
TwoAccessions t
on t.client_id=f.CLIENT_ID
where f.CLIENT_ID not in (select clientid from SalesDWH..TestPractices)
and f.SPECIMEN_SOURCE in ('Oral Fluid','Urine')
group by f.CLIENT_ID,o.PatientCount,t.PatientCount

If I remove OneAccession and TwoAccessions, I can get results in just a few minutes!

I would be very grateful for any guidance on improving my query syntax in order to speed this up!

Thanks so much.

Upvotes: 0

Views: 126

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11813

You can avoid all the joins if you just do two group-bys. First count the specimens per client,patient Then combine those counts per client:

WITH SpecimenCountPerPatient AS(
  SELECT client_id, patient_id, COUNT(1) SpecimenCount, MIN(received_date) FirstSpecimenDate
  FROM dbo.F_ACCESSION_DAILY
  GROUP BY client_id, patient_id
)
SELECT client_id,
       DATEDIFF(Month,MIN(FirstSpecimenDate),GETDATE()) FirstSpecimenAgeInMonthForClient,
       COUNT(1) TotalPatients,
       SUM(SpecimenCount) TotalSpecimens,
       SUM(CASE WHEN SpecimenCount = 1 THEN 1 ELSE 0 END) TotalPatientsWOneSpec,
       SUM(CASE WHEN SpecimenCount = 2 THEN 1 ELSE 0 END) TotalPatientsWTwoSpec
FROM SpecimenCountPerPatient
GROUP BY client_id;

Here is a SQLFiddle to see it in action: http://sqlfiddle.com/#!3/72e40/2

Now, I know you said you cant add indexes, but an index on client_id ASC, patient_id ASC INCLUDE(received_date) would speed things up even more.

Upvotes: 4

Niladri Biswas
Niladri Biswas

Reputation: 4171

Try this and let me know if it improves the performance

SELECT client_id,patient_id ,Accession_Id_Count = COUNT(ACCESSION_ID)
INTO #temp
from F_ACCESSION_DAILY 
group by CLIENT_ID,PATIENT_ID 

select 
f.client_id 
, 12*(year(getdate())-year(min(f.received_date))) + MONTH(GETDATE())-MONTH(min(f.received_date)) 
, COUNT(distinct f.patient_id) TotalPatients 
, COUNT(f.ACCESSION_ID) TotalSpecimens 
, o.PatientCount  TotalPatientsWOneSpec 
, t.PatientCount TotalPatientsWTwoSpec 
from F_ACCESSION_DAILY f 
join (select client_id,COUNT(patient_id) PatientCount from #temp where Accession_Id_Count = 1 group by CLIENT_ID ) o  on o.CLIENT_ID=f.CLIENT_ID 
join (select client_id,COUNT(patient_id) PatientCount from #temp where Accession_Id_Count = 2 group by CLIENT_ID ) t  on t.client_id=f.CLIENT_ID 
where f.CLIENT_ID not in (select clientid from SalesDWH..TestPractices) 
and f.SPECIMEN_SOURCE in ('Oral Fluid','Urine') 
group by f.CLIENT_ID,o.PatientCount,t.PatientCount 

drop table #temp

Upvotes: 2

Related Questions