Hi there,
Does anyone know any good optimisation techniques beyond 'make sure the QVD does an optimised load'?
I'm having trouble with my code, where we currently do a resident load of the main fact table. Then we perform a whole bunch of different conditional logic on it in order to derive flags which are used to speed up the front end.
The trouble is, that this conditional logic is very very slow.
We do:
if(Match(IF(Match(stuff), 'TRUE', IF([A Field]) = 'C'), 'TRUE', IF(Lookup(LookupStuff), [Some Field], IF(isnull(Lookup(TheSameLookupStuffAgain),'[Some Other Field]')) ))), 'FALSE') = 0), 'Yes', 'No') AS TC
Which is a complicated way of saying,
A)If some field contains some value that isn't FALSE
[ IF(Match(stuff) ]
AND
B)If this other field contains some value that isnt FALSE
[ IF([A Field]) = 'C') ]
AND
C)If this lookup gives a result, and that result isnt 'FALSE'
[ IF(Lookup(LookupStuff), [Some Field] ]
AND
D)If the lookup gives a null, then use a different field, if that result isn't 'FALSE'
[ IF(isnull(Lookup(TheSameLookupStuffAgain),'[Some Other Field]')]
Then set TC to Yes, otherwise No.
We then do a few other bits and peices, but its really just a repetition of various aspects of this, using different fields, so any solution to this would help fix the others.
I was thinking there might be some way to create a mapping table and load that, but I can't really see where I could use it? Would saving the Fact table off to a QVD first help in any way, maybe there's a 'WHERE EXISTS([Field)) that you could see would help the logic in some way?
any ideas would be massively appreciated!
