Most efficient way of retrieving data

[DrSmyth]

Hi, My situation is as follows:

I have two tables LLPSC01U.PSC_PSC_ESTA_V (Estate)& LLPSC01U.PSC_PSC_DIRY_V (Diary). The Diary contains many occurances of many events for estates. I want to report on a single event including cases where the event hasn't occured. I was wondering on the most efficient way of doing it. I currently have a single report with the following SQL:

SELECT
  COALESCE(STRIP(LLPSC01U.PSC_PSC_ESTA_V.SRNM) || ', ' || STRIP(COALESCE(LLPSC01U.PSC_PSC_ESTA_V.FRNM,'')) , STRIP(LLPSC01U.PSC_PSC_ESTA_V.SRNM) ),
  date(DIRY_ALLOC.DIRY_TS)
FROM
  LLPSC01U.PSC_PSC_ESTA_V LEFT OUTER JOIN LLPSC01U.PSC_PSC_DIRY_V  DIRY_ALLOC ON LLPSC01U.PSC_PSC_ESTA_V.ESTA_COD=DIRY_ALLOC.ESTA_COD AND DIRY_ALLOC.EVNT_COD = 'CHFIRS'

however it's taking a long time to run and i think that this is bacause of the complex join. My other thought is to run it as two seperate queries:

SELECT
  COALESCE(STRIP(LLPSC01U.PSC_PSC_ESTA_V.SRNM) || ', ' || STRIP(COALESCE(LLPSC01U.PSC_PSC_ESTA_V.FRNM,'')) , STRIP(LLPSC01U.PSC_PSC_ESTA_V.SRNM) )
FROM
  LLPSC01U.PSC_PSC_ESTA_V

and

SELECT
  LLPSC01U.PSC_PSC_ESTA_V.ESTA_COD,
  date(LLPSC01U.PSC_PSC_DIRY_V.DIRY_TS)
FROM
  LLPSC01U.PSC_PSC_ESTA_V,
  LLPSC01U.PSC_PSC_DIRY_V
WHERE
  ( LLPSC01U.PSC_PSC_ESTA_V.ESTA_COD=LLPSC01U.PSC_PSC_DIRY_V.ESTA_COD  )
  AND  (
  LLPSC01U.PSC_PSC_DIRY_V.EVNT_COD  =  'CHFIRS'
  )

with them being linked in the report itself. Because of the volumetrics i run these queries at night so am not able to time them.

I was wondering if anybody had any ideas about the best solution for this situation, i'm thinking that there's probably a better way of getting the data that i haven't thought of yet. Any suggestions will be appreciated...

Thanks

 

[blom0344]

My first question would be, why is the original query taking so long to finish? Outer joins are often inherently slower, but this may also be a matter of tuning the database (enabling hash joins,re-organizing tables, keepings statistics up-to-date).

Secondly, is DIRY_ALLOC.EVNT_COD an indexed field?

Even if you run them after daytime, you should coerce the DBA to take some performance measures.

Splitting the query into 2 seperate one and linking them at reportlevel will have the effect of a full outer join (as you will probably know), but it can prove to be much faster (though formatting the report will probably be slower)

My advice would be to look into performance figures first, optimizing the database could enhance query performance in a way you would not believe initially!

Ties Blom
Information analyst

 

[DrSmyth]

Cheers Ties, I'll get in touch with our DBA to see if he can lend a hand with optimizing.