Hi Robert, thanx for the reply. I tried the update statistics statement for the table T_LAB_VALUES (l) by executing:
update statistics T_LAB_VALUES this gave me the same explain result than before. Then I executed: update statistics COLUMN (*) FOR T_LAB_VALUES which also gave me the same explain result. The pagecount for T_LAB_VALUES is still 1356. Any further suggestions? Did I execute the update statistics stuff correctly? I didn't try to understand the ESTIMATE and SAMPLE_DEFINITION stuff. Is this necessary for me? Regards, Dominic Am Freitag, 14. Januar 2005 10:04 schrieben Sie: > 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] -- 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]
