0

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?

memo_star
  • 109
  • 2
  • 8
  • 2
    Some sample data would be helpful. – P.Salmon Sep 10 '17 at 13:09
  • Please review [How to post a tsql question on a public forum](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) and [How-to-Ask](https://stackoverflow.com/help/how-to-ask) and edit your question accordingly. -- Without more information as to your table schema and the operations you are performing our ability to offer useful solutions to your scenario is limited. – SqlZim Sep 10 '17 at 13:17
  • What is "something" here? Did you actually benchmark the subqueries (?) in each branch of the `CASE` expression? – Tim Biegeleisen Sep 10 '17 at 13:18
  • what is benchmark the subqueries? – memo_star Sep 10 '17 at 13:21
  • I cannot imagine what you are doing that 4000 lines takes a long time to be evaluated. Please post your code, and we may be able to assist. – cddt Sep 11 '17 at 01:32

0 Answers0