i want update table values generated out of same table.
my goal search rows proftxt _ns , _wp , same ao, sum them up,
divide value through number of _h,_g,_l-elements of ao , add value _h, _g , _l objects of ao.
it possible there _ns , _wp rows ao. routine should jump on ao.
example:
my data looks like:
an, ao, proftxt, value, year 101 , 1, 'e_nse', 5, 2006 102 , 1, 'e_ha', 1, 2006 103 , 1, 'w_nsr', 4, 2006 104 , 2, 'w_nsr', 2, 2006 105 , 2, 'x_h05r', 4, 2006 106 , 2, 'w_gr', 2, 2006 107 , 2, 'a_wpr', 4, 2006 108 , 3, 'a_wpr', 4, 2006 my data should like:
an, ao, proftxt, value, year 102 , 1, 'e_ha', 10 2006 103 , 2, 'x_h05r', 7, 2006 103 , 2, 'w_gr', 5, 2006 108 , 3, 'a_wpr', 4, 2006 my routine works small amount of testdata.
the update function ends, while working on real database, after 13 hours successful.
edited 5000 out of 210000 rows.
declare @endyear int declare @ao bigint declare @year int declare @elements int --parameter festlegen set @year = 2006 set @endyear = 2013 --endyear+1 set @ao = 2 while(@year<@endyear) begin while (@ao >1) --do long cursor inside table begin set @ao = (select top 1 ao tbl_slp -- search ao _wp _ns (proftxt '%[_]wp%' or proftxt '%[_]ns%') , year = @year , ao > @ao ); set @elements = (select count(proftxt) --count number of _h, _g, _l elements tbl_slp ao = @ao , year = @year , (proftxt '%[_]h%' or proftxt = null or proftxt '%[_]g%' or proftxt '%[_]l%')) if (@elements != 0) begin update tbl_slp --update _h, _g, _l rows set value = value + (select sum(convert(float, value)) tbl_slp (proftxt '%[_]wp%' or proftxt '%[_]ns%') , year = @year , ao = @ao) /@elements ao = @ao , year = @year delete tbl_slp --delete_wp _ns rows ao= @ao , year = @year , (proftxt '%[_]wp%' or proftxt '%[_]ns%') end set @ao = @ao +1 end set @year = @year +1 end i know routine super slow, can do?
sql designed set-based operations, not procedural flow-of-control style logic routine. here's set-based way of doing it, i'm guessing faster procedural way:
set xact_abort on set nocount on begin transaction -- create temp table each ao-year's sums , counts (sums of _ns , _wp record values , counts of _h, _g, , _l records) select t.ao, t.year, sum(t.value) sumvals, (select count(*) tbl_slp a.ao = t.ao , a.year = t.year , (a.proftxt = null or a.proftxt '%[_]h%' or a.proftxt '%[_]g%' or a.proftxt '%[_]l%')) countother #temp1 tbl_slp t (t.proftxt '%[_]wp%' or t.proftxt '%[_]ns%') group t.ao, t.year -- add "sum/count" each ao-year _h, _g, , _l records year update set value = value + convert(float, t.sumvals) / t.countother tbl_slp inner join #temp1 t on a.ao = t.ao , a.year = t.year (a.proftxt = null or a.proftxt '%[_]h%' or a.proftxt '%[_]g%' or a.proftxt '%[_]l%') -- we've distributed _wp , _ns values, delete records delete tbl_slp inner join #temp1 t on a.ao = t.ao , a.year = t.year (a.proftxt '%[_]wp%' or a.proftxt '%[_]ns%') , t.countother > 0 commit transaction for sample set gave, produces exact same results (except an column assume typo).
full disclosure, takes longer on sample set routine (17 ms compared 3 ms), should scale large data whole lot better. put in transaction correctness i'm not sure exact use case is, may disadvantage of way since lock pages (and may escalate whole table) entire time. routine didn't have transactions, though, lead bad data if keep way make sure put each update-delete pair in own transaction.
also, if don't have index on proftxt, add one! make huge difference both solutions.
good luck. here's the sql fiddle used.
Comments
Post a Comment