I have a requirement where I'm using the following If statement in the load script. I'd like to know if anyone has any suggestions to be more efficient. Or is this the best way to do this? Note that I have over 8 million records in this table. Thanks in advance.
If( (practice = 'A' or practice = 'B') AND (Payer='1') AND ServiceDate < '01/01/2000', 'code1', If( v75_svcSortName = 'C', 'code2',
If(( practice ='A' or practice = 'B' or practice =’D’) AND (Payer ='2') AND ServiceDate < '01/01/2005', 'code3', ‘other’ ))) AS BillingCode
Do you need to run the if and compare to all 8M records to calculate the BillingCode for each row everytime you load the data?
Why not look at an incremental load, where you run this script just against new or modified data and store the results to a QVD, then you are only calculating the Billing Code for new data not on all 8M records each time.
Search for "Incremental Load" and you will find many resources with example scripts.
[Edit: Removed useless example of non-existent optimization]
But as Massimo and Anil have already stated, you will make an effort without really knowing what your gain could possibly be. To get an idea of how much time can be save ideally, run the script without the IF()'s and then with the IF()'s and compare those delays. Contrary to what you are thinking, the load script doesn't mind that it has to run a few minutes longer. On the other hand, using nested IF()'s in the User Interface should be avoided as much as possible.