0

Test DDL

with cte as (
    select '0001' ID,1 InputValue,'Qty*2;Qty*5' Formulas union all
    select '0002' ID,2 InputValue,'Qty+4;Qty/5' Formulas 
)
select * into T from cte

--Query Data
ID      ,InputValue ,Formulas       
0001    ,1          ,Qty*2;Qty*5    
0002    ,2          ,Qty+4;Qty/5

Expected

call somefunction or sqlquery get below result

ID      ,InputValue ,Formulas       ,Qty1   ,Qty2
0001    ,1          ,Qty*2;Qty*5    ,2      ,5
0002    ,2          ,Qty+4;Qty/5    ,6      ,0.4

Logic

The formula is cut by ;
The inputvalue uses the first formula to calculate the qty1 field
The inputvalue uses the second formula to calculate the qty2 field

My Test

Currently my solution is to use C# programming to solve the problem

    using (var cn = Connection)
    {
        cn.Open();

        var datas = cn.Query(@"
            with cte as (
                select '0001' ID,1 InputValue,'Qty*2;Qty*5' Formulas union all
                select '0002' ID,2 InputValue,'Qty+4;Qty/5' Formulas 
            )
            select * into #T from cte;
            select * from #T;
        ");

        var result = datas.Select(s => {
            var arr = (s.Formulas as string).Split(';');
            var qtyFor = arr[0].Replace("Qty",  Convert.ToString(s.InputValue));
            var qty1For = arr[1].Replace("Qty",  Convert.ToString(s.InputValue));
            var qty = new DataTable().Compute(qtyFor, string.Empty);
            var qty1 = new DataTable().Compute(qty1For, string.Empty);
            return new {s.ID,s.InputValue,s.Formulas,Qty=qty,Qty1=qty1};
        });

        Console.WriteLine(result);
        /*
            Result:
            ID      ,InputValue ,Formulas       ,Qty1   ,Qty2
            0001    ,1          ,Qty*2;Qty*5    ,2      ,5
            0002    ,2          ,Qty+4;Qty/5    ,6      ,0.4
        */
    }

Online Test Link

I know this table structure is weird, but this is an old system with no source code,so i can't fix the table structure

Wei Lin
  • 3,591
  • 2
  • 20
  • 52
  • What kind of performance are you talking about for 100K records? What's the runtime for your C# application? – zedfoxus Apr 24 '19 at 01:45
  • actual the formulas column contain 20 formula,if i read data to system memory by C# , it'll spend over 10 min,But the company wants it done in 30 seconds.so i try to solve it by sql sp or function – Wei Lin Apr 24 '19 at 01:51
  • 1
    Can you use `Stopwatch` like in the example here https://stackoverflow.com/a/14019738? Do Console.WriteLine milliseconds taken for each operation: cn.Open(), var data, var result and after the last Console.WriteLine. It'll tell you where C# is taking most time. Can you share where it's taking the most time? – zedfoxus Apr 24 '19 at 02:03
  • @zedfoxus thanks, I got C# slow query problem point, the old sp query all columns and it contian blob... but it looks like there is no way to do this with native SQL server sql query – Wei Lin Apr 24 '19 at 02:20
  • So, `cn.Query` is taking the longest? – zedfoxus Apr 24 '19 at 02:26
  • @zedfoxus yes cn.Query is longest – Wei Lin Apr 24 '19 at 02:46
  • Since you have separate columns for qty, why not have separate formula columns for each qty? ie Why are you shoe-horning 2 (complex) values into one field? – Bohemian Apr 24 '19 at 02:49
  • @Bohemian Yes, I have same doubts, but I can't change it. It was ten years ago and nobody maintained old system... – Wei Lin Apr 24 '19 at 02:56
  • how complex is the formula ? can you share some example ? – Squirrel Apr 24 '19 at 03:10
  • e.g : '0001' ID,1 InputValue,'Qty*2;Qty*5;Qty+15/20;Qty%20*5;...' Formulas, it can contain 20 Formula – Wei Lin Apr 24 '19 at 03:13
  • Can you share the original query and the structure of the table? – zedfoxus Apr 24 '19 at 04:06
  • it's like the test ddl ,only different it contatin blob column , so i can't share it . – Wei Lin Apr 24 '19 at 04:15

0 Answers0