Optimising request

[andoid95]

hello guys,
i want to optimise this request in order to improve the performance.
Do i create a query item for each substring or no ?

case
when substring([SEVRE];1;3) in ('701';'702';'703')
  or substring([SEVRE];1;4) in ('5547';'7092';'7093')
  or substring([SEVRE];1;6) in ('709010';'709014';'709015';'709016';'709017';'709018';'709019') then 10
when substring([SEVRE];1;3) in ('707')
  or substring([SEVRE];1;4) in ('7077')
  or substring([SEVRE];1;6) in ('709511';'709512';'709013') then 20
when substring([SEVRE];1;3) in ('708';'704';'705';'706')
  or substring([SEVRE];1;4) in ('7098';'7049';'7059';'7069') then 30
when [LOUVRE] in ('71') then 50
when [LOUVRE] in ('72') then 60
when substring([SEVRE];1;3) in ('607')
  or substring([SEVRE];1;4) in ('6087';'6097') then 80
when substring([SEVRE];1;4) in ('6037') then 90
when substring([SEVRE];1;3) in ('601')
  or substring([SEVRE];1;4) in ('6081';'6091') then 100
when substring([SEVRE];1;4) in ('6031') then 110
when substring([SEVRE];1;3) in ('602')
  or substring([SEVRE];1;4) in ('6082';'6092') then 120
when substring([SEVRE];1;4) in ('6032') then 130
when substring([SEVRE];1;3) in ('606')
  or substring([SEVRE];1;4) in ('6086';'6096') then 140
when [LOUVRE] in ('61';'62')
  or substring([SEVRE];1;3) in ('604';'605')
  or substring([SEVRE];1;4) in ('6084';'6085';'6094';'6095') then 150
when [LOUVRE] in ('63') then 180
when [LOUVRE] in ('64') then 190
when [LOUVRE] in ('74') then 195
when substring([SEVRE];1;3) in ('650';'651';'652';'653';'654';'656';'657';'658';'659') then 210
when substring([SEVRE];1;3) in ('750';'751';'752';'753';'754';'756';'757';'758';'759') then 220
when substring([SEVRE];1;4) in ('6811';'6813') then 240
when substring([SEVRE];1;4) in ('6816') then 245
when substring([SEVRE];1;4) in ('6817';'6818') then 250
when substring([SEVRE];1;4) in ('6815')
  or substring([SEVRE];1;6) in ('689900') then 260
when substring([SEVRE];1;3) in ('781';'791';'790') then 270
when substring([SEVRE];1;3) in ('755';'655') then 285
when substring([SEVRE];1;3) in ('761') then 300
when substring([SEVRE];1;3) in ('762') then 310
when substring([SEVRE];1;3) in ('763';'764';'765';'767';'768') then 320
when substring([SEVRE];1;3) in ('786';'796') then 330
when substring([SEVRE];1;3) in ('766') then 340
when substring([SEVRE];1;3) in ('686') then 370
when substring([SEVRE];1;3) in ('661';'662';'663';'664';'665';'667';'668') then 390
when substring([SEVRE];1;3) in ('666') then 400
when substring([SEVRE];1;3) in ('771';'772';'773';'774') then 440
when substring([SEVRE];1;3) in ('775';'776') then 460
when substring([SEVRE];1;3) in ('777') then 470
when substring([SEVRE];1;3) in ('778') then 480
when substring([SEVRE];1;3) in ('787';'797') then 490
when substring([SEVRE];1;3) in ('671';'672';'673';'674') then 520
when substring([SEVRE];1;3) in ('675';'676') then 540
when substring([SEVRE];1;3) in ('678') then 550
when substring([SEVRE];1;4) in ('6872';'6873';'6874')
  or substring([SEVRE];1;6) in ('689904') then 570
when substring([SEVRE];1;4) in ('6871';'6875';'6876') then 580
when substring([SEVRE];1;3) in ('691') then 600
when substring([SEVRE];1;3) in ('695';'696';'697';'698';'699') then 620
else 900
end

[MFGF]

hello guys,
i want to optimise this request in order to improve the performance.
Do i create a query item for each substring or no ?

case
when substring([SEVRE];1;3) in ('701';'702';'703')
  or substring([SEVRE];1;4) in ('5547';'7092';'7093')
  or substring([SEVRE];1;6) in ('709010';'709014';'709015';'709016';'709017';'709018';'709019') then 10
when substring([SEVRE];1;3) in ('707')
  or substring([SEVRE];1;4) in ('7077')
  or substring([SEVRE];1;6) in ('709511';'709512';'709013') then 20
when substring([SEVRE];1;3) in ('708';'704';'705';'706')
  or substring([SEVRE];1;4) in ('7098';'7049';'7059';'7069') then 30
when [LOUVRE] in ('71') then 50
when [LOUVRE] in ('72') then 60
when substring([SEVRE];1;3) in ('607')
  or substring([SEVRE];1;4) in ('6087';'6097') then 80
when substring([SEVRE];1;4) in ('6037') then 90
when substring([SEVRE];1;3) in ('601')
  or substring([SEVRE];1;4) in ('6081';'6091') then 100
when substring([SEVRE];1;4) in ('6031') then 110
when substring([SEVRE];1;3) in ('602')
  or substring([SEVRE];1;4) in ('6082';'6092') then 120
when substring([SEVRE];1;4) in ('6032') then 130
when substring([SEVRE];1;3) in ('606')
  or substring([SEVRE];1;4) in ('6086';'6096') then 140
when [LOUVRE] in ('61';'62')
  or substring([SEVRE];1;3) in ('604';'605')
  or substring([SEVRE];1;4) in ('6084';'6085';'6094';'6095') then 150
when [LOUVRE] in ('63') then 180
when [LOUVRE] in ('64') then 190
when [LOUVRE] in ('74') then 195
when substring([SEVRE];1;3) in ('650';'651';'652';'653';'654';'656';'657';'658';'659') then 210
when substring([SEVRE];1;3) in ('750';'751';'752';'753';'754';'756';'757';'758';'759') then 220
when substring([SEVRE];1;4) in ('6811';'6813') then 240
when substring([SEVRE];1;4) in ('6816') then 245
when substring([SEVRE];1;4) in ('6817';'6818') then 250
when substring([SEVRE];1;4) in ('6815')
  or substring([SEVRE];1;6) in ('689900') then 260
when substring([SEVRE];1;3) in ('781';'791';'790') then 270
when substring([SEVRE];1;3) in ('755';'655') then 285
when substring([SEVRE];1;3) in ('761') then 300
when substring([SEVRE];1;3) in ('762') then 310
when substring([SEVRE];1;3) in ('763';'764';'765';'767';'768') then 320
when substring([SEVRE];1;3) in ('786';'796') then 330
when substring([SEVRE];1;3) in ('766') then 340
when substring([SEVRE];1;3) in ('686') then 370
when substring([SEVRE];1;3) in ('661';'662';'663';'664';'665';'667';'668') then 390
when substring([SEVRE];1;3) in ('666') then 400
when substring([SEVRE];1;3) in ('771';'772';'773';'774') then 440
when substring([SEVRE];1;3) in ('775';'776') then 460
when substring([SEVRE];1;3) in ('777') then 470
when substring([SEVRE];1;3) in ('778') then 480
when substring([SEVRE];1;3) in ('787';'797') then 490
when substring([SEVRE];1;3) in ('671';'672';'673';'674') then 520
when substring([SEVRE];1;3) in ('675';'676') then 540
when substring([SEVRE];1;3) in ('678') then 550
when substring([SEVRE];1;4) in ('6872';'6873';'6874')
  or substring([SEVRE];1;6) in ('689904') then 570
when substring([SEVRE];1;4) in ('6871';'6875';'6876') then 580
when substring([SEVRE];1;3) in ('691') then 600
when substring([SEVRE];1;3) in ('695';'696';'697';'698';'699') then 620
else 900
end

Hi,

Yuk! That's a complex statement! I would optimise it by removing redundancies and by putting the tests most likely to be true at the beginning of the expression. Processing of the expression will only continue until a "True" result is met, at which point the rest of the statement will be ignored. If you can figure out the most common "True" results in your data, you can put these at the beginning of the expression and speed things up. I have also spotted a redundancy in there, too:

when substring([SEVRE];1;3) in ('707')
 
or substring([SEVRE];1;4) in ('7077')

If you have a string '7077' it will return True for the first test, since the first three characters are 707. There is no need for the second test - it is just increasing the size and complexity of the expression for no reason. Remove it and simplify the expression a little by doing so :)

Cheers!

MF.

[andoid95]

Thank you very much , to be honest I really did not think anything.
I try to do it and I 'll let you know  :)

[bdbits]

Just a thought especially performance-wise... Unless this is a one-time thing, this expression looks like a good candidate for an additional column in the data source populated by ETL (assuming this is a data warehouse). Or if that is not an option, create the expression in FM. At least that way you would not have to embed it every time you want to use it in a report and you only have one place to change it, if that becomes necessary in the future.