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

Question

I really hope you could help me with this one. This is my current code and it is, so far, the best code I could make.

 SELECT
      max(t2.pmskey) as pmskey, max(cast(t1.recdate as datetime)) as recdate,
      max(t2.mrtype) as mrtype, max(cast(t1.mrdate as datetime)) as mrdate,
      max(t2.CaseNo) as CaseNo, max(t2.pmclin) as pmclin,
      max(cast(t1.nexteval as datetime)) as nexteval,
      max(cast(t1.repdate as datetime)) as repdate,
      max(t3.mrprocedure) as mrprocedure, max(t2.med_stat) as med_stat,
      max(cast(t1.med_stateff as datetime)) as med_stateff,
      max(t2.clincontact) as clincontact,
      max(cast(t1.datemodf as datetime)) as datemodf, max(t2.modfby) as modfby,
      max(cast(t1.inceptiondate as datetime)) as inceptiondate,
      max(t2.createdby) as createdby, max(cast(t1.date_ent as datetime)) as date_ent,
      max(t2.ppihandler) as ppihandler

 FROM
      tblpms as t1

 JOIN
      (
      select * from tblpms where lower(CaseNo) like '%tr13-011%'

 AND
      cast(mrdate as datetime) IN (select max(cast(mrdate as datetime))
      from tblpms where lower(CaseNo) like '%tr13-011%')
      ) as t2

      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

 JOIN
      (
      select * from tblpms where lower(CaseNo) like '%tr13-011%'

 AND
      lower(mrprocedure) is not null and cast(nexteval as datetime)
      in (select max(cast(nexteval as datetime)) from
      tblpms where lower(CaseNo) like '%tr13-011%')
      ) as t3

      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

      and lower(t2.CaseNo) like '%tr13-011%'

the criteria for lower(CaseNo) is replicated in all the joins and I don't know how to lessen those. I believe there is something that could be done. And if there are any other things that could be optimized, please include it in the answer.

this is my current query based on the answers below..

 SELECT
      max(t2.pmskey) as pmskey, max(cast(t1.recdate as datetime)) as recdate,
      max(t2.mrtype) as mrtype, max(cast(t1.mrdate as datetime)) as mrdate,
      max(t2.CaseNo) as CaseNo, max(t2.pmclin) as pmclin,
      max(cast(t1.nexteval as datetime)) as nexteval,
      max(cast(t1.repdate as datetime)) as repdate,
      max(t3.mrprocedure) as mrprocedure, max(t2.med_stat) as med_stat,
      max(cast(t1.med_stateff as datetime)) as med_stateff,
      max(t2.clincontact) as clincontact,
      max(cast(t1.datemodf as datetime)) as datemodf, max(t2.modfby) as modfby,
      max(cast(t1.inceptiondate as datetime)) as inceptiondate,
      max(t2.createdby) as createdby, max(cast(t1.date_ent as datetime)) as date_ent,
      max(t2.ppihandler) as ppihandler

 FROM
      tblpms as t1

 JOIN
      (
      select * from tblpms where CaseNo = 'TR13-011-CRW'

 AND
      cast(mrdate as datetime) IN (select max(cast(mrdate as datetime))
      from tblpms where CaseNo = 'TR13-011-CRW')
      ) as t2

      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

 JOIN
      (
      select * from tblpms where CaseNo = 'TR13-011-CRW'

 AND
      lower(mrprocedure) is not null and cast(nexteval as datetime)
      in (select max(cast(nexteval as datetime)) from
      tblpms where CaseNo = 'TR13-011-CRW')
      ) as t3

      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

      and CaseNo = 'TR13-011-CRW'

score 3 · Accepted Answer · edited May 23 '17 at 11:49

Usually lower() is not required when matching with LIKE since it case-insensitive by default.

Also, when the search performed in the middle of the string (e.g. LIKE '%whatever%), the index is not used. By the way, do you have any indexes at all? Have you tried to build query plan?

As I can see, there are few possible solutions to this problem:

Use full text search
Add another indexed field and populate it once before the query:

update tblpms set tr13_flag=1 where CaseNo like '%tr13-011%'

then replace every where lower(CaseNo) like '%tr13-011%' statement with where tr13_flag=1
A variance of the above: use indexed PERSISTED computed column, thus you avoid calculating the flag manually.
Grab all valuable rows into temporary table once:

select * into #tr13011 from tblpms where lower(CaseNo) like '%tr13-011%'

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

Also, you cast continuously: cast(nexteval as datetime). Use 4th version from the answer and cast into #temp table at the same time.

This condition looks useless to me: and lower(mrprocedure) is not null and cast(nexteval as datetime). Why not simply and mrprocedure is not null?

Example of the version 4

select
    CaseNo,
    mrprocedure,
    cast(mrdate as datetime) as mrdate,
    cast(nexteval as datetime) as nexteval
    -- other required fields from tblpms table
into #tr13
from tblpms where CaseNo = 'TR13-011-CRW'

declare @max_mrdate datetime
declare @max_nexteval datetime

select @max_mrdate=max(mrdate), @max_nexteval=max(nexteval) from #tr13

SELECT
      max(t2.pmskey) as pmskey, max(cast(t1.recdate as datetime)) as recdate,
      max(t2.mrtype) as mrtype, max(cast(t1.mrdate as datetime)) as mrdate,
      max(t2.CaseNo) as CaseNo, max(t2.pmclin) as pmclin,
      max(cast(t1.nexteval as datetime)) as nexteval,
      max(cast(t1.repdate as datetime)) as repdate,
      max(t3.mrprocedure) as mrprocedure, max(t2.med_stat) as med_stat,
      max(cast(t1.med_stateff as datetime)) as med_stateff,
      max(t2.clincontact) as clincontact,
      max(cast(t1.datemodf as datetime)) as datemodf, max(t2.modfby) as modfby,
      max(cast(t1.inceptiondate as datetime)) as inceptiondate,
      max(t2.createdby) as createdby, max(cast(t1.date_ent as datetime)) as date_ent,
      max(t2.ppihandler) as ppihandler

 FROM
      tblpms as t1

 JOIN
      (
      select * from #tr13 where mrdate = @max_mrdate
      ) as t2
      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

 JOIN
      (
      select * from #tr13
        where mrprocedure is not null and nexteval = @max_nexteval
      ) as t3
      on t1.CaseNo COLLATE DATABASE_DEFAULT = t2.CaseNo COLLATE DATABASE_DEFAULT

drop table #tr13

I'm not sure why you need COLLATE DATABASE_DEFAULT since it is always the same table. The logic is not very clear to me, but is seem to produce the same result while being more readable (and may perform better):

select
    CaseNo,
    mrprocedure,
    cast(mrdate as datetime) as mrdate,
    cast(nexteval as datetime) as nexteval,
    pmskey,
    mrtype,
    med_stat,
    clincontact,
    modfby,
    createdby,
    ppihandler
    -- other required fields from tblpms table
into #tr13
from tblpms where CaseNo = 'TR13-011-CRW'

declare @max_mrdate datetime
declare @max_nexteval datetime

select @max_mrdate=max(mrdate), @max_nexteval=max(nexteval) from #tr13

SELECT
      max(t2.pmskey) as pmskey, max(cast(t1.recdate as datetime)) as recdate,
      max(t2.mrtype) as mrtype, max(cast(t1.mrdate as datetime)) as mrdate,
      max(t2.CaseNo) as CaseNo, max(t2.pmclin) as pmclin,
      max(cast(t1.nexteval as datetime)) as nexteval,
      max(cast(t1.repdate as datetime)) as repdate,
      max(t2.mrprocedure) as mrprocedure, max(t2.med_stat) as med_stat,
      max(cast(t1.med_stateff as datetime)) as med_stateff,
      max(t2.clincontact) as clincontact,
      max(cast(t1.datemodf as datetime)) as datemodf, max(t2.modfby) as modfby,
      max(cast(t1.inceptiondate as datetime)) as inceptiondate,
      max(t2.createdby) as createdby, max(cast(t1.date_ent as datetime)) as date_ent,
      max(t2.ppihandler) as ppihandler

 FROM tblpms as t1
 JOIN #tr13 t2 on t1.CaseNo = t2.CaseNo
where
    t2.mrdate = @max_mrdate
    and t2.mrprocedure is not null
    and t2.nexteval = @max_nexteval

drop table #tr13

UPDATE We managed to reduce it to the following code:

declare @max_mrdate datetime
declare @max_nexteval datetime

select
    @max_mrdate=max(cast(mrdate as datetime)),
    @max_nexteval=max(cast(nexteval as datetime))
from tblpms
where CaseNo = 'TR13-011-CRW'

select *
from tblpms where CaseNo = 'TR13-011-CRW'
and (
    cast(mrdate as datetime) = @max_mrdate
    or
    (mrprocedure is not null and cast(nexteval as datetime) = @max_nexteval)
)

this answer is good.. it increased the speed of my query from 0.09+ to 0.07+.. but what about the JOIN and OTHERs? — E-r Gabriel Doronila, Mar 27 '13 at 07:27
It will help if you include the actual query plan to the question. — Artem Koshelev, Mar 27 '13 at 07:36
ill just edit my post to add my current query to see how it will differ — E-r Gabriel Doronila, Mar 27 '13 at 07:41
How can I do number 2? Should I create a new column in the database server? or Should I just create it in the select query? Also, can you give me an example (from my query) of using number 3 and 4? thanks — E-r Gabriel Doronila, Mar 27 '13 at 08:05
Number 2 is not a good solution. Check the update and stick to version 4 since you are not using `LIKE %` any more. — Artem Koshelev, Mar 27 '13 at 08:32
im not sure why.. im trying to edit it but it does not show any column.. how is that? ive used your example.. — E-r Gabriel Doronila, Mar 27 '13 at 08:43
check the latest example. Also, add every required field in the first `select` statement. — Artem Koshelev, Mar 27 '13 at 08:45
could I just use the * mark? since all the required fields are existing in the tblpms. — E-r Gabriel Doronila, Mar 27 '13 at 08:49
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26997/discussion-between-artem-koshelev-and-e-r-gabriel-doronila) — Artem Koshelev, Mar 27 '13 at 08:50

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

1 Answers1