Hi All,



This monster got stuck in the optimizer overnight...at least 10 hours
before I killed it off. I have no idea if it works or not, I don't know
how the programmer built it up/tested it....BTW I have reformatted it
for readability.



CREATE TABLE dataset_mris AS

SELECT n.studyid, max(ep01.diag_ct_mri) nfep0001_mri,

max(ep03.diag_ct_mri) nfep0003_mri, max(ep06.diag_ct_mri)
nfep0006_mri,

max(ep07.diag_ct_mri) nfep0007_mri, max(ep08.diag_ct_mri)
nfep0008_mri,

max(ep09.diag_ct_mri) nfep0009_mri, max(ep10.diag_ct_mri)
nfep0010_mri,

max(ep11.diag_ct_mri) nfep0011_mri, max(ep12.diag_ct_mri)
nfep0012_mri,

max(ep14.diag_ct_mri) nfep0014_mri, max(ep16.diag_ct_mri)
nfep0016_mri,

max(ep17.diag_ct_mri) nfep0017_mri, max(ep18.diag_ct_mri)
nfep0018_mri,

max(ep19.diag_ct_mri) nfep0019_mri, max(ep21.diag_ct_mri)
nfep0021_mri,

max(ep33.diag_ct_mri) nfep0033_mri, max(ep42.diag_ct_mri)
nfep0042_mri

FROM dataset_nfep n LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep01 ON n.studyid = ep01.studyid AND n.nfep0001 = 1 AND
n.nfep0001_date = ep01.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep03 ON n.studyid = ep03.studyid AND n.nfep0003 = 1 AND
n.nfep0003_date = ep03.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep06 ON n.studyid = ep06.studyid AND n.nfep0006 = 1 AND
n.nfep0006_date = ep06.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep07 ON n.studyid = ep07.studyid AND n.nfep0007 = 1 AND
n.nfep0007_date = ep07.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep08 ON n.studyid = ep08.studyid AND n.nfep0008 = 1 AND
n.nfep0008_date = ep08.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep09 ON n.studyid = ep09.studyid AND n.nfep0009 = 1 AND
n.nfep0009_date = ep09.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep10 ON n.studyid = ep10.studyid AND n.nfep0010 = 1 AND
n.nfep0010_date = ep10.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep11 ON n.studyid = ep11.studyid AND n.nfep0011 = 1 AND
n.nfep0011_date = ep11.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep12 ON n.studyid = ep12.studyid AND n.nfep0012 = 1 AND
n.nfep0012_date = ep12.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep14 ON n.studyid = ep14.studyid AND n.nfep0014 = 1 AND
n.nfep0014_date = ep14.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep16 ON n.studyid = ep16.studyid AND n.nfep0016 = 1 AND
n.nfep0016_date = ep16.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep17 ON n.studyid = ep17.studyid AND n.nfep0017 = 1 AND
n.nfep0017_date = ep17.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep18 ON n.studyid = ep18.studyid AND n.nfep0018 = 1 AND
n.nfep0018_date = ep18.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep19 ON n.studyid = ep19.studyid AND n.nfep0019 = 1 AND
n.nfep0019_date = ep19.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep21 ON n.studyid = ep21.studyid AND n.nfep0021 = 1 AND
n.nfep0021_date = ep21.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep33 ON n.studyid = ep33.studyid AND n.nfep0033 = 1 AND
n.nfep0033_date = ep33.event_date

LEFT JOIN (

SELECT e.studyid, e.event_date, lde.diag_ct_mri

FROM lt_events e INNER JOIN lt_disease_events lde ON
e.lt_event_kuid = lde.lt_event_kuid

) ep42 ON n.studyid = ep42.studyid AND n.nfep0042 = 1 AND
n.nfep0042_date = ep42.event_date

GROUP BY n.studyid



You'll notice that each of the inline selects is identical.



I replaced them with a session temporary table and joined to that
instead...query now optimizes in <10seconds.



I still have no idea if its correct, but at least it's a step...Now to
strangle the programmer.



Martin Bowes

Can yuou use a CTE for EP stuff and then filter Dataset_Nfep before
you do that chain of LEFT OUTER JOINs? I have no idea if this will
help, but I had some time to play around ..

WITH EP (study_id, event_date, diag_ct_mri)
AS
(SELECT E.study_id, E.event_date, LDE.diag_ct_mri
FROM Lt_Events AS E, Lt_Disease_Events AS LDE
WHERE E.lt_event_kuid = LDE.lt_event_kuid)

SELECT N.study_id, MAX(EP01.diag_ct_mri),
MAX(EP03.diag_ct_mri), MAX(EP06.diag_ct_mri),
MAX(EP07.diag_ct_mri), MAX(EP08.diag_ct_mri),
MAX(EP09.diag_ct_mri), MAX(EP10.diag_ct_mri),
MAX(EP11.diag_ct_mri), MAX(EP12.diag_ct_mri),
MAX(EP14.diag_ct_mri), MAX(EP16.diag_ct_mri),
MAX(EP17.diag_ct_mri), MAX(EP18.diag_ct_mri),
MAX(EP19.diag_ct_mri), MAX(EP21.diag_ct_mri),
MAX(EP33.diag_ct_mri), MAX(EP42.diag_ct_mri)
FROM (SELECT study_id,
nfep0001_date, nfep0003_date, nfep0006_date,
nfep0007_date, nfep0008_date, nfep0009_date,
nfep0010_date, nfep0011_date, nfep0012_date,
nfep0014_date, nfep0016_date, nfep0017_date,
nfep0018_date, nfep0019_date, nfep0021_date,
nfep0033_date, nfep0042_date,
nfep0001, nfep0003, nfep0006, nfep0007,
nfep0008, nfep0009, nfep0010, nfep0011,
nfep0012, nfep0014, nfep0016, nfep0017,
nfep0018, nfep0019, nfep0021, nfep0033, nfep0042
FROM Dataset_Nfep)
AS N
LEFT OUTER JOIN
EP AS EP01
ON N.study_id = EP01.study_id
AND N.nfep0001_date = EP01.event_date
LEFT OUTER JOIN
EP AS EP03
ON N.study_id = EP03.study_id
AND N.nfep0003_date = EP03.event_date
LEFT OUTER JOIN
EP AS EP06
ON N.study_id = EP06.study_id
AND N.nfep0006_date = EP06.event_date
LEFT OUTER JOIN
EP AS EP07
ON N.study_id = EP07.study_id
AND N.nfep0007_date = EP07.event_date
LEFT OUTER JOIN
EP AS EP08
ON N.study_id = EP08.study_id
AND N.nfep0008_date = EP08.event_date
LEFT OUTER JOIN
EP AS EP09
ON N.study_id = EP09.study_id
AND N.nfep0009_date = EP09.event_date
LEFT OUTER JOIN
EP AS EP10
ON N.study_id = EP10.study_id
AND N.nfep0010_date = EP10.event_date
LEFT OUTER JOIN
EP AS EP11
ON N.study_id = EP11.study_id
AND N.nfep0011_date = EP11.event_date
LEFT OUTER JOIN
EP AS EP12
ON N.study_id = EP12.study_id
AND N.nfep0012_date = EP12.event_date
LEFT OUTER JOIN
EP AS EP14
ON N.study_id = EP14.study_id
AND N.nfep0014_date = EP14.event_date
LEFT OUTER JOIN
EP AS EP16
ON N.study_id = EP16.study_id
AND N.nfep0016_date = EP16.event_date
LEFT OUTER JOIN
EP AS EP17
ON N.study_id = EP17.study_id
AND N.nfep0017_date = EP17.event_date
LEFT OUTER JOIN
EP AS EP18
ON N.study_id = EP18.study_id
AND N.nfep0018_date = EP18.event_date
LEFT OUTER JOIN
EP AS EP19
ON N.study_id = EP19.study_id
AND N.nfep0019_date = EP19.event_date
LEFT OUTER JOIN
EP AS EP21
ON N.study_id = EP21.study_id
AND N.nfep0021_date = EP21.event_date
LEFT OUTER JOIN
EP AS EP33
ON N.study_id = EP33.study_id
AND N.nfep0033_date = EP33.event_date
LEFT OUTER JOIN
EP AS EP42
ON N.study_id = EP42.study_id
AND N.nfep0042_date = EP42.event_date
GROUP BY N.study_id;

Martin Bowes wrote:
The poor guy, expecting the query optimizer to pick an optimal query plan.
In 2010.

I can't wait to see what progress the next 40 years brings.

--jkl

James K. Lowden wrote:

I agree there is something to what you say. If I hold my nose and
concede that SQL is at least inspired by relational query languages,
then it should be entirely goal oriented. If the query correctly
states how to recognize the required results, no matter how tortured
and gnarly the statement is, the optimizer should come up with a
pretty good plan pretty quickly. An expert knowledge of how to tickle
the optimizer or the execution engine to get a correct answer quickly
should be unnecessary. SQL is supposed to lower the bar.

Having said that, code still has to be comprehensible to other
programmers and also the programmer who wrote it. One of the upsides
of SQL trying to be goal oriented is that you should be able to write
the query in a way that makes clear what you are doing (and how you
are thinking), without affecting performance. If the code is
necessarily horrid, so be it. But if the code could be easier to read
and comprehend, yet isn't, the programmer was probably struggling (or
the SQL was machine-generated).

Marty's point is that not that the SQL engine should be presented
with lovely elegant SQL, but he should be.

I think you both makes good points.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to https://www.regonline.co.uk/IUA2010 to register.