SQLTeam.com | Weblogs | Forums

How to optimize this query


#1

I created the following query which works fine. I am trying to figure out how to optimize this query where I don't have to create a temp table and another join to pull the data I am trying to retrieve. Please provide same examples.

Thank you

create table #temp( **
** PatientID int,

** CollectionDateTime Datetime**
** )**
** use CLGMMCMARTPROD**
declare @NumOfYears int
insert #temp
select B.PatientId, min(a.CollectionDateTime) from dbo.Labs a
** inner join Patients b**
** on a.PatientID = b.PatientID**
** where a.LabtestID = 2 and a.CollectionDateTime >= '06/01/02' and a.CollectionDateTime < '06/01/03' and a.NumericLabResult >= 9.5 **
** and (CAST(CONVERT(char(8),a.CollectionDateTime, 112) AS int) - CAST(CONVERT(char(8), DOB, 112) AS int)) / 10000 >= 21 and (CAST(CONVERT(char(8),a.CollectionDateTime, 112) AS int) - CAST(CONVERT(char(8), DOB, 112) AS int)) / 10000 < 65 -- and min(a.CollectionDateTime)**
** group by b.PatientID**
** order by b.PatientID**
** select distinct a.PatientID from dbo.Labs a**
** inner join #temp b**
** on a.PatientID = b.PatientID **
** where a.LabtestID = 2 and a.CollectionDateTime >= 180 + b.CollectionDateTime and a.CollectionDateTime <= 365 + b.CollectionDateTime **
** order by a.PatientID**


#2

I'm seeing stars....literally!
How much of this query are you actually running?


#3

I tried to format the t-sql and it added stars on every line. Please ignore all the stars.

Thank you.


#4

The automatic "guess the format" tool here gets in the way most of the time. If you can re-edit your post and do the following it should format nicely:

    ```sql

    ... your code here ...

    ```

#5

I think this get close to what you want:

select a.patientid
  from patients as p
       inner join dbo.labs as l1
               on l1.patientid=p.patientid
              and l1.labtestid=2
              and l1.numericlabresult>=9.5
              and l1.collectiondatetime>=cast('2006-02-01' as datetime)
              and l1.collectiondatetime< cast('2006-03-01' as datetime)
              and datediff(day,p.dob,l1.collectiondatetime)/365.25>=21.0
              and datediff(day,p.dob,l1.collectiondatetime)/365.25< 65.0
 where exists (select 1
                 from dbo.labs as l2
                where l2.patientid=l1.patientid
                  and l2.labtestid=l1.labtestid
                  and l2.collectiondatetime>=dateadd(day,datediff(day,365,l1.collectiondatetime),0)
                  and l2.collectiondatetime< dateadd(day,datediff(day,182,l1.collectiondatetime),0)
              )
 group by p.patientid
 order by p.patientid
;

#6
select distinct a.PatientID 
from dbo.Labs a
inner join (
    select a.PatientId, min(a.CollectionDateTime) AS CollectionDateTime
    from dbo.Labs a
    inner join Patients b
    on a.PatientID = b.PatientID
    where a.LabtestID = 2 
    and a.CollectionDateTime >= '06/01/02' 
    and a.CollectionDateTime < '06/01/03' 
    and a.NumericLabResult >= 9.5 
    and YEAR(a.CollectionDateTime) - YEAR(b.DOB) >= 21 
    and YEAR(a.CollectionDateTime) - YEAR(b.DOB) < 65
    group by b.PatientID
) as b on b.PatientID = a.PatientID 
and a.CollectionDateTime >= 180 + b.CollectionDateTime
and a.CollectionDateTime <= 365 + b.CollectionDateTime 
where a.LabtestID = 2
order by a.PatientID