December 15, 2007 at 11:41 am
thabiso_masina (12/14/2007)
Having analyzed your query, see what happens when you run it like this (sorry had to trim it down a bit for readability 🙂 )SELECT D1.SID_0COMPANY AS SID_0COMPANY
FROM FZFIGL_C02 F JOIN DZFIGL_C02T DT ON F.KEY_ZFIGL_C02T = DT.DIMID AND DT.SID_0FISCPER3 = 12
JOIN SFISCYEAR S2 ON DT.SID_0FISCYEAR = S2.SID AND S2.FISCYEAR = N'2007'
JOIN DZFIGL_C022 D2 ON F.KEY_ZFIGL_C022 = D2.DIMID
JOIN DZFIGL_C023 D3 ON F.KEY_ZFIGL_C023 = D3.DIMID
JOIN DZFIGL_C02U DU ON F.KEY_ZFIGL_C02U = DU.DIMID
JOIN DZFIGL_C024 D4 ON F.KEY_ZFIGL_C024 = D4.DIMID
JOIN DZFIGL_C02P DP ON F.KEY_ZFIGL_C02P = DP.DIMID AND DP.SID_0CHNGID = 0 AND DP.SID_0RECORDTP = 0 AND DP.SID_0REQUID <= 2000000250
JOIN DZFIGL_C021 D1 ON F.KEY_ZFIGL_C021 = D1.DIMID AND D1.SID_0COMPANY IN ( 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121 )
2. The optimizer would have to only look at these fields for indexing...
SFISCYEAR.SID
DZFIGL_C02T.DIMID
DZFIGL_C02T.SID_0FISCYEAR
FZFIGL_C02.KEY_ZFIGL_C02T
FZFIGL_C02.KEY_ZFIGL_C022
FZFIGL_C02.KEY_ZFIGL_C023
FZFIGL_C02.KEY_ZFIGL_C02U
FZFIGL_C02.KEY_ZFIGL_C024
FZFIGL_C02.KEY_ZFIGL_C02P
FZFIGL_C02.KEY_ZFIGL_C021
DZFIGL_C021.SID_0COMPANY
DZFIGL_C021.SID_0FISCPER3
SFISCYEAR.FISCYEAR
DZFIGL_C02P.SID_0RECORDTP
DZFIGL_C02P.SID_0REQUID
so, I would recomment unbundling some of your indexes from compound to simple or single field indexes.
keen to know what happens?
Regards,
Tbs.
Sorry I have finished this assignment and do not have access to the system so I cannot test it.
But thanks for posting the suggestion anyway.
http://90.212.51.111 domain
Viewing post 31 (of 30 total)
You must be logged in to reply to this topic. Login to reply