I must work with Bank-Statements which means I have to work with a big String and cut it to small Parts to be able to order every Line to which Bills it belongs.
So far so good. The problem is that PATINDEX, CHARINDEX and substring take a lot of time when it has to work with "big data".
For Example, I have 100 (String) lines in a Bank statement which I have to be able to order every Line to the right Bill.
From the 100 Lines, there are 90% very Simple Cases that do not really take Time because it is a simple substring work. But for the rest of the 10%, there is a lot of work which takes really Time.
My Problem is that even if I select case when everything the SQL-Code takes always the same time in case I have 100% of Lines that are really complicated or even 5% of them. That means for each Line the whole Code will be evaluated and then the right decision will be taken which is exactly my Problem.
How can I avoid this?
Select case
when @var = 'bla' then do-something that takes 10 min to be evaluated
when @var = 'john' then do-something that takes 5 min to be evaluated
when @var = 'Sarah' then do-something that takes very short time
else do-something that takes 10 min to be evaluated.
now when my data is 4000 lines with 'Sarah' in the most of them it takes the same time as if my Data only 'bla' which takes a lot of time. That means all cases are every time evaluated no matter what kind of them and then it will be decided which case is the right one. Or did I miss something here?