Dominic, did you update statistics? If not, please do so and try again. What's the performance now?
robert > Hi, > > I have problems with a complex view ( and join ) statement. The > performance is > extremely poor. > > Info: OS: MaxDB 7.5.00.15 64 bit (Sun Solaris 9) > > The View: > > >>> > CREATE VIEW V_LAB_VALUES AS ( > SELECT distinct l.id, l.doc_id, l.doc_timestamp, l.ds_date AS LAB_DATE, > l.status, l.lab_id AS L_LAB_ID, l.lab_value, l.lab_unit AS LAB_LAB_UNIT, > p.patient_id, p.sex as PATIENT_SEX, p.dob_y, > n.lab_id AS LAB_ID, n.lower_norm, n.upper_norm, n.unit AS > NORMAL_LAB_UNI, > n.sex , n.lower_age, n.upper_age, > v.visit_date AS DS_DATE, (NUM(SUBSTR(v.visit_date,1,4))-p.dob_y) as > PATIENT_AGE, > ll.label AS LAB_UNIT, > pl.center as center_id > >FROM T_PATIENT p , T_VISIT_DATES v , T_PATIENT_LIST pl , T_LAB_VALUES l > LEFT JOIN T_NORMAL_RANGES n ON ( n.lab_id = l.lab_id ) > LEFT JOIN T_LABELS ll ON ( ll.id = n.unit ) > WHERE v.patient_id = p.patient_id > AND pl.patient_id = p.patient_id > AND l.patient_id = p.patient_id AND l.ds_date = v.visit_date > AND n.center_id = pl.center > AND n.sex = p.sex > AND n.lower_age <= (NUM(SUBSTR(v.visit_date,1,4))-p.dob_y) > AND n.upper_age >= (NUM(SUBSTR(v.visit_date,1,4))-p.dob_y) > AND n.start_date < v.visit_date > AND NOT EXISTS ( > select R.start_date from T_NORMAL_RANGES R where > R.lab_id = n.lab_id AND R.sex = p.sex AND R.lower_age <= > (NUM(SUBSTR(v.visit_date,1,4))-p.dob_y) AND R.upper_age >= > (NUM(SUBSTR(v.visit_date,1,4))-p.dob_y) AND > R.start_date < n.start_date AND R.start_date < > v.visit_date > ) > ); > <<< > > I tried the following indexes: > > >>> > create index v_pat_id on t_visit_dates ( patient_id ) ; > > create index pl_pat_id on t_patient_list ( patient_id ) ; > > create index l_comp01 on T_LAB_VALUES ( patient_id , ds_date ) ; > > create index n_comp01 on t_normal_ranges ( lab_id , sex , lower_age , > upper_age , start_date ) ; > <<< > > The statement that calls the view is: > > >>> > SELECT doc_id, upper_norm, lab_unit, status, lower_norm, lab_id, > doc_timestamp, id, lab_value FROM V_LAB_VALUES WHERE patient_id = > 'CSJXNg/vv70577+9Qkzvv71QZVYvHe+/vT8=' and ds_date = '2004-04-16' ORDER > BY > ds_date DESC > <<< > > The explain statement shows: > > >>> > | OWNER | TABLENAME | > COLUMN_OR_INDEX | STRATEGY > | > PAGECO > UNT | > | -------------------------------- | -------------------------------- | > -------------------------------- | > ---------------------------------------- | > ------ > ---- | > | | P | > > | TABLE SCAN | > 3 | > | | V | > V_PAT_ID | JOIN VIA INDEXED COLUMN > | > 27 | > | | PL | > PATIENT_LIST_UNIQUE | JOIN VIA INDEXED COLUMN > | > 2 | > | | L | > > | TABLE SCAN | > 1356 | > | | N | > > | TABLE SCAN | > 10 | > | | LL | ID > > | JOIN VIA KEY COLUMN | > 143 | > | INTERNAL | TEMPORARY RESULT | > > | EQUAL CONDITION FOR KEY COLUMN | > 500 | > | | | ID > > | (USED KEY COLUMN) | > | > | INTERNAL | TEMPORARY RESULT | > > | TABLE SCAN | > 500 | > | INTERNAL | TEMPORARY RESULT | > > | TABLE SCAN | > 500 | > | | R | > > | NO STRATEGY NOW (ONLY AT EXECUTION TIME) | > | > | INTERNAL | TEMPORARY RESULT | > > | TABLE SCAN | > 500 | > | | SQLCURS_2 | > > | RESULT IS COPIED , COSTVALUE IS | > 2 > E10 | > 13 rows selected (7 msec) > <<< > > However, the explain result looks very similar to non-indexed columns. > > Some of the indexes are not used for executing the statement (Explain > shows > TABLE SCAN Search strategy). Very important is that the table T_LAB_VALUES > (l > in the view and the explain result) also shows the TABLE SCAN strategy. > Even > though the index is created so that it should sacrifice the statement the > PAGECOUNT for l (T_LAB_VALUES) remains 1356. It does not change when > creating > or dropping the indexes. > > I even tried to put everything in join-statements and for testing I also > tried > to put everything in non-joint-statements (where clause - however, I need > left joins in this statement - I would even need a left join for the NOT > EXIST (select ...) part). > > For the MaxDB there where some hints that the optimizer has join-problems > sometimes. However both (join and where clause) did not work. > > Next thing was that I changed the DB-Parameter JOIN_SEARCH_LEVEL to 9 but > nothing happened (I'm not sure if I understood the concept at this point > correctly, but I tried). > > Is there somebody who knows what to do with such an awfull complex join > (apart > >from deleting it ;-) ). Is there anybody who sees some mistakes? I hope > that > I did some very crucial! > > Regards and thanx in advance, > > Dominic > > -- > Dominic Veit > CwebRD, University Hospital Freiburg > Breisacherstr. 60 > 79106 Freiburg > Tel.: 0761 270 37 84 > Fax: 0761 270 18 83 > E-mail: [EMAIL PROTECTED] > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- +++ Sparen Sie mit GMX DSL +++ http://www.gmx.net/de/go/dsl AKTION f�r Wechsler: DSL-Tarife ab 3,99 EUR/Monat + Startguthaben -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
