Hi,
Still playing around with performance etc. with my queries, and ran into an
interesting scenario which surprised me...
I have a query that joins three tables, a metrics table that holds all the
reporting info - upto 100,000 records - along with a forms table and kiosks
table that have approx 1000 and 100 records respectively.
When I did an explain on my original join:
explain SELECT Forms.Title, Kiosks.Name, Metrics.Copies FROM Metrics left
JOIN Kiosks ON Metrics.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON
Metrics.Form_Id = Forms.Id WHERE ExecTime IS NOT NULL AND Copies IS NOT
NULL ORDER BY Forms.UpperTitle, Kiosks.UpperName
KIOSK METRICS TABLE SCAN
1021
KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE INDEXED COL.
3
ID (USED INDEX COLUMN)
KIOSK RESULT IS COPIED , COSTVALUE IS
3452
Although I have everything indexed I can think of on the metrics table, I
cannot prevent the table scan. Currently I have an index of kiosk_id,
form_id, ExecTime and Copies.
It appears that the optimizer is not performing the WHERE clause on the
Metrics table before it does the join, which doesn't make sense to me.
When I force this with the following query:
explain SELECT Forms.Title, Kiosks.Name, t1.Copies FROM (select kiosk_id,
form_id, Copies from Metrics WHERE ExecTime IS NOT NULL AND Copies IS NOT
NULL ) t1 left JOIN Kiosks ON t1.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON
t1.Form_Id = Forms.Id ORDER BY Forms.UpperTitle, Kiosks.UpperName
I get a better cost value, but I still can't get rid of the table scan:
KIOSK METRICS TABLE SCAN
947
INTERNAL TEMPORARY RESULT TABLE SCAN
500
KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE
INDEXED COL. 3
ID (USED INDEX COLUMN)
KIOSK RESULT IS COPIED ,
COSTVALUE IS 1152
Like I've said before, I feel like I am shooting somewhat in the dark, and
just playing with various combinations. I never thought a subselect would
be better than a regular join!
Please can anyone point me in the right direction to get rid of the metrics
table scan? Or point out any other ways to optimize this better?
Many thanks,
David
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
