sql - How would you optimize a query with multiple joins and redundant criterias? -

i hope me one. current code , is, far, best code make.

 select       max(t2.pmskey) pmskey, max(cast(t1.recdate datetime)) recdate,       max(t2.mrtype) mrtype, max(cast(t1.mrdate datetime)) mrdate,       max(t2.caseno) caseno, max(t2.pmclin) pmclin,       max(cast(t1.nexteval datetime)) nexteval,       max(cast(t1.repdate datetime)) repdate,       max(t3.mrprocedure) mrprocedure, max(t2.med_stat) med_stat,       max(cast(t1.med_stateff datetime)) med_stateff,       max(t2.clincontact) clincontact,       max(cast(t1.datemodf datetime)) datemodf, max(t2.modfby) modfby,       max(cast(t1.inceptiondate datetime)) inceptiondate,       max(t2.createdby) createdby, max(cast(t1.date_ent datetime)) date_ent,       max(t2.ppihandler) ppihandler         tblpms t1   join       (       select * tblpms lower(caseno) '%tr13-011%'   ,       cast(mrdate datetime) in (select max(cast(mrdate datetime))       tblpms lower(caseno) '%tr13-011%')       ) t2        on t1.caseno collate database_default = t2.caseno collate database_default   join       (       select * tblpms lower(caseno) '%tr13-011%'   ,       lower(mrprocedure) not null , cast(nexteval datetime)       in (select max(cast(nexteval datetime))       tblpms lower(caseno) '%tr13-011%')       ) t3        on t1.caseno collate database_default = t2.caseno collate database_default        , lower(t2.caseno) '%tr13-011%' 

the criteria lower(caseno) replicated in joins , don't know how lessen those. believe there done. , if there other things optimized, please include in answer.

this current query based on answers below..

 select       max(t2.pmskey) pmskey, max(cast(t1.recdate datetime)) recdate,       max(t2.mrtype) mrtype, max(cast(t1.mrdate datetime)) mrdate,       max(t2.caseno) caseno, max(t2.pmclin) pmclin,       max(cast(t1.nexteval datetime)) nexteval,       max(cast(t1.repdate datetime)) repdate,       max(t3.mrprocedure) mrprocedure, max(t2.med_stat) med_stat,       max(cast(t1.med_stateff datetime)) med_stateff,       max(t2.clincontact) clincontact,       max(cast(t1.datemodf datetime)) datemodf, max(t2.modfby) modfby,       max(cast(t1.inceptiondate datetime)) inceptiondate,       max(t2.createdby) createdby, max(cast(t1.date_ent datetime)) date_ent,       max(t2.ppihandler) ppihandler         tblpms t1   join       (       select * tblpms caseno = 'tr13-011-crw'   ,       cast(mrdate datetime) in (select max(cast(mrdate datetime))       tblpms caseno = 'tr13-011-crw')       ) t2        on t1.caseno collate database_default = t2.caseno collate database_default   join       (       select * tblpms caseno = 'tr13-011-crw'   ,       lower(mrprocedure) not null , cast(nexteval datetime)       in (select max(cast(nexteval datetime))       tblpms caseno = 'tr13-011-crw')       ) t3        on t1.caseno collate database_default = t2.caseno collate database_default        , caseno = 'tr13-011-crw' 

usually lower() not required when matching like since case-insensitive default.

also, when search performed in middle of string (e.g. like '%whatever%), index not used. by way, have indexes @ all? have tried build query plan?

as can see, there few possible solutions problem:

1. use full text search

2. add indexed field , populate once before query:

   update tblpms set tr13_flag=1 caseno '%tr13-011%'

   then replace every where lower(caseno) '%tr13-011%' statement where tr13_flag=1

3. a variance of above: use indexed persisted computed column, avoid calculating flag manually.

4. grab valuable rows temporary table once:

   select * #tr13011 tblpms lower(caseno) '%tr13-011%'

and join on it, possibly adding indexes on interesting fields.

also, cast continuously: cast(nexteval datetime). use 4th version answer , cast #temp table @ same time.

this condition looks useless me: and lower(mrprocedure) not null , cast(nexteval datetime). why not and mrprocedure not null?

example of version 4

select     caseno,     mrprocedure,     cast(mrdate datetime) mrdate,     cast(nexteval datetime) nexteval     -- other required fields tblpms table #tr13 tblpms caseno = 'tr13-011-crw'  declare @max_mrdate datetime declare @max_nexteval datetime  select @max_mrdate=max(mrdate), @max_nexteval=max(nexteval) #tr13  select       max(t2.pmskey) pmskey, max(cast(t1.recdate datetime)) recdate,       max(t2.mrtype) mrtype, max(cast(t1.mrdate datetime)) mrdate,       max(t2.caseno) caseno, max(t2.pmclin) pmclin,       max(cast(t1.nexteval datetime)) nexteval,       max(cast(t1.repdate datetime)) repdate,       max(t3.mrprocedure) mrprocedure, max(t2.med_stat) med_stat,       max(cast(t1.med_stateff datetime)) med_stateff,       max(t2.clincontact) clincontact,       max(cast(t1.datemodf datetime)) datemodf, max(t2.modfby) modfby,       max(cast(t1.inceptiondate datetime)) inceptiondate,       max(t2.createdby) createdby, max(cast(t1.date_ent datetime)) date_ent,       max(t2.ppihandler) ppihandler         tblpms t1   join       (       select * #tr13 mrdate = @max_mrdate       ) t2       on t1.caseno collate database_default = t2.caseno collate database_default   join       (       select * #tr13         mrprocedure not null , nexteval = @max_nexteval       ) t3       on t1.caseno collate database_default = t2.caseno collate database_default  drop table #tr13 

i'm not sure why need collate database_default since same table. logic not clear me, seem produce same result while being more readable (and may perform better):

select     caseno,     mrprocedure,     cast(mrdate datetime) mrdate,     cast(nexteval datetime) nexteval,     pmskey,     mrtype,     med_stat,     clincontact,     modfby,     createdby,     ppihandler     -- other required fields tblpms table #tr13 tblpms caseno = 'tr13-011-crw'  declare @max_mrdate datetime declare @max_nexteval datetime  select @max_mrdate=max(mrdate), @max_nexteval=max(nexteval) #tr13  select       max(t2.pmskey) pmskey, max(cast(t1.recdate datetime)) recdate,       max(t2.mrtype) mrtype, max(cast(t1.mrdate datetime)) mrdate,       max(t2.caseno) caseno, max(t2.pmclin) pmclin,       max(cast(t1.nexteval datetime)) nexteval,       max(cast(t1.repdate datetime)) repdate,       max(t2.mrprocedure) mrprocedure, max(t2.med_stat) med_stat,       max(cast(t1.med_stateff datetime)) med_stateff,       max(t2.clincontact) clincontact,       max(cast(t1.datemodf datetime)) datemodf, max(t2.modfby) modfby,       max(cast(t1.inceptiondate datetime)) inceptiondate,       max(t2.createdby) createdby, max(cast(t1.date_ent datetime)) date_ent,       max(t2.ppihandler) ppihandler   tblpms t1  join #tr13 t2 on t1.caseno = t2.caseno     t2.mrdate = @max_mrdate     , t2.mrprocedure not null     , t2.nexteval = @max_nexteval  drop table #tr13 

update managed reduce following code:

declare @max_mrdate datetime declare @max_nexteval datetime  select     @max_mrdate=max(cast(mrdate datetime)),     @max_nexteval=max(cast(nexteval datetime)) tblpms caseno = 'tr13-011-crw'  select * tblpms caseno = 'tr13-011-crw' , (     cast(mrdate datetime) = @max_mrdate     or     (mrprocedure not null , cast(nexteval datetime) = @max_nexteval) )