|
|
Posted by lsllcm on 11/27/07 01:55
Hi Gert-Jan/Erland Sommarskog,
Thanks for your input.
The whole sql is very long.
SELECT SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2,
B1_PER_ID3, B1_CHECKLIST_COMMENT
FROM BCHCKBOX P
WHERE SERV_PROV_CODE = 'SACCO' AND ((B1_PER_ID1 = '07COM' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '00628') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02386') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02385') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02384') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02383') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02382') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02381') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02380') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02379') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02378') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02377') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02376') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02375') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02374') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02373') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02372') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02371') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02370') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02369') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02368') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02367') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02366') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02365') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02364') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02363') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02362') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02361') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02360') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02359') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02358') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02357') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02356') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02355') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02354') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02353') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02352') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02351') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02350') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02349') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02348') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02347') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02346') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02345') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02344') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02343') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02342') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02341') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02340') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02339') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02338') OR (B1_PER_ID1 = '07RES' AND
B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02337') OR (B1_PER_ID1 =
'07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02336') OR
(B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 =
'02335') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND
B1_PER_ID3 = '02334') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 =
'00000' AND B1_PER_ID3 = '02333') )
yes, I have reduced the OR number to 62, the optimizer chooses
different index plan. When there are 63 OR operators, the optimizer
chooses the partial index scan
Seek predicts : Prefix: [sacco].[dbo].[BCHCKBOX].SERV_PROV_CODE =
'SACCO'
The method is one way to workaround, but it will make sql very long.
SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07RES'
AND C3 = '00000'
AND C4 IN ('02383','02382','02381','02380','02379', ...)
UNION ALL
SELECT C1, C2, C3, C4
FROM TT
WHERE C1 = 'TEST'
AND C2 = '07COM'
AND C3 = '00000'
AND C4 IN ('00618','00617','00616', ...)
I will change OR number to 50.
Where can I found the document of 63 is the value?
Because multiple client access the site, I don't know how to use
temporary table to do it, could you give me one example?
Thanks
Jacky
Navigation:
[Reply to this message]
|