Обсуждение: Reuse temporary calculation results in an SQL update query
Hello, is there any way how one can store the result of a time-consuming calculation if this result is needed more than once inan SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. Here is an example of whatI want: UPDATE table1 SET StartTime = 'time consuming calculation 1', StopTime = 'time consuming calculation 2', Duration = 'timeconsuming calculation 2' - 'time consuming calculation 1' WHERE foo; It would be nice, if I could use the "new" start and stop time to calculate the duration time. First of all it would makethe SQL statement faster and secondly much more cleaner and easily to understand. Best regards, Matthias ---------------------------------------------------------------------- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.nagel@gmail.com ICQ: 499797758 Skype: nagmat84
Matthias Nagel <matthias.h.nagel@gmail.com> hat am 29. September 2012 um 12:49 geschrieben: > Hello, > > is there any way how one can store the result of a time-consuming calculation > if this result is needed more than once in an SQL update query? This solution > might be PostgreSQL specific and not standard SQL compliant. Here is an > example of what I want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; The Duration - field is superfluous ... As far as i know there is no way to re-use the result. Regards, Andreas
Hello, > Matthias Nagel <matthias.h.nagel@gmail.com> hat am 29. September 2012 um 12:49 > geschrieben: > > Hello, > > > > is there any way how one can store the result of a time-consuming calculation > > if this result is needed more than once in an SQL update query? This solution > > might be PostgreSQL specific and not standard SQL compliant. Here is an > > example of what I want: > > > > UPDATE table1 SET > > StartTime = 'time consuming calculation 1', > > StopTime = 'time consuming calculation 2', > > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > > WHERE foo; > > The Duration - field is superfluous ... > I expected the answer ;-), but no, it is not superfluous. In this small example it might appear as if it is, but there arecases in that the start time and the duration time have values and the stop time equals null to indicate a running session.And for reasons that are beyond the orginal question, there are also cases where duration does not equal the differencebetween start and stop time. > As far as i know there is no way to re-use the result. Too bad. > Regards, Andreas Thanks, Matthias ---------------------------------------------------------------------- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.nagel@gmail.com ICQ: 499797758 Skype: nagmat84
On Sep 29, 2012, at 6:49, Matthias Nagel <matthias.h.nagel@gmail.com> wrote: > Hello, > > is there any way how one can store the result of a time-consuming calculation if this result is needed more than once inan SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. Here is an example of whatI want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate the duration time. First of all it would makethe SQL statement faster and secondly much more cleaner and easily to understand. > > Best regards, Matthias > > You are allowed to use a FROM clause with UPDATE so if you can figure out how to write a SELECT query, including a CTE ifneeded, you can use that as your cache. An immutable function should also be optimized in theory though I've never tried it. David J.
Matthias Nagel wrote on 29.09.2012 12:49: > Hello, > > is there any way how one can store the result of a time-consuming calculation if this result is needed more >than once in an SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. > Here is an example of what I want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate the duration time. >First of all it would make the SQL statement faster and secondly much more cleaner and easily to understand. Something like: with my_calc as ( select pk, time_consuming_calculation_1 as calc1, time_consuming_calculation_2 ascalc2 from foo ) update foo set startTime = my_calc.calc1, stopTime = my_calc.calc2, duration = my_calc.calc2 - calc1 where foo.pk = my_calc.pk; http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING
Thomas Kellerer <spam_eater@gmx.net> hat am 29. September 2012 um 16:13 geschrieben: > Matthias Nagel wrote on 29.09.2012 12:49: > > Hello, > > > > is there any way how one can store the result of a time-consuming > > calculation if this result is needed more > >than once in an SQL update query? This solution might be PostgreSQL specific > >and not standard SQL compliant. > > Here is an example of what I want: > > > > UPDATE table1 SET > > StartTime = 'time consuming calculation 1', > > StopTime = 'time consuming calculation 2', > > Duration = 'time consuming calculation 2' - 'time consuming calculation > > 1' > > WHERE foo; > > > > It would be nice, if I could use the "new" start and stop time to calculate > > the duration time. > >First of all it would make the SQL statement faster and secondly much more > >cleaner and easily to understand. > > > Something like: > > with my_calc as ( > select pk, > time_consuming_calculation_1 as calc1, > time_consuming_calculation_2 as calc2 > from foo > ) > update foo > set startTime = my_calc.calc1, > stopTime = my_calc.calc2, > duration = my_calc.calc2 - calc1 > where foo.pk = my_calc.pk; > > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING Yeah, with a WITH - CTE, cool ;-) Andreas
Hello, thank you. The "WITH" clause did the trick. I did not even know that such a thing exists. But as it turns out it makes thestatement more readable and elegant but not faster. The reason for the latter is that both the CTE and the UPDATE statement have the same "FROM ... WHERE ..." part, becausethe tempory calculation needs some input values from the same table. Hence the table is looked up twice instead once. Matthias Am Samstag 29 September 2012, 17:13:18 schrieb Andreas Kretschmer: > > Thomas Kellerer <spam_eater@gmx.net> hat am 29. September 2012 um 16:13 > geschrieben: > > Matthias Nagel wrote on 29.09.2012 12:49: > > > Hello, > > > > > > is there any way how one can store the result of a time-consuming > > > calculation if this result is needed more > > >than once in an SQL update query? This solution might be PostgreSQL specific > > >and not standard SQL compliant. > > > Here is an example of what I want: > > > > > > UPDATE table1 SET > > > StartTime = 'time consuming calculation 1', > > > StopTime = 'time consuming calculation 2', > > > Duration = 'time consuming calculation 2' - 'time consuming calculation > > > 1' > > > WHERE foo; > > > > > > It would be nice, if I could use the "new" start and stop time to calculate > > > the duration time. > > >First of all it would make the SQL statement faster and secondly much more > > >cleaner and easily to understand. > > > > > > Something like: > > > > with my_calc as ( > > select pk, > > time_consuming_calculation_1 as calc1, > > time_consuming_calculation_2 as calc2 > > from foo > > ) > > update foo > > set startTime = my_calc.calc1, > > stopTime = my_calc.calc2, > > duration = my_calc.calc2 - calc1 > > where foo.pk = my_calc.pk; > > > > http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING > > > > Yeah, with a WITH - CTE, cool ;-) > > Andreas > > > ---------------------------------------------------------------------- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.nagel@gmail.com ICQ: 499797758 Skype: nagmat84
> > thank you. The "WITH" clause did the trick. I did not even know that such a > thing exists. But as it turns out it makes the statement more readable and > elegant but not faster. > > The reason for the latter is that both the CTE and the UPDATE statement > have the same "FROM ... WHERE ..." part, because the tempory calculation > needs some input values from the same table. Hence the table is looked up > twice instead once. This is unusual; the only WHERE clause you should require is some kind of key matching... Like: UPDATE tbl SET .... FROM ( WITH final_result AS (SELECT pkid, ....FROM tblWHERE ... ) -- /WITH SELECT pkid, .... FROM final_result ) src -- /FROM WHERE src.pkid = tbl.pkid ; If you provide an actual query better help may be provided. David J.
On 2012-09-29, Matthias Nagel <matthias.h.nagel@gmail.com> wrote: > Hello, > > is there any way how one can store the result of a time-consuming calculation if this result is needed more than once inan SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. Here is an example of whatI want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate the duration time. First of all it would makethe SQL statement faster and secondly much more cleaner and easily to understand. > > Best regards, Matthias use a CTE. http://www.postgresql.org/docs/9.1/static/queries-with.html with a as ( select 'time consuming calculation 1' as tcc1 , 'time consuming calculation 2' as tcc2 ) update table1 SET StartTime = a.tcc1 StopTime = a.tcc2 Duration = a.tcc2 - a.tcc1 WHERE foo; you man need to move foo into the CTE too. -- ⚂⚃ 100% natural
Matthias Nagel wrote on 29.09.2012 12:49: > Hello, > > is there any way how one can store the result of a time-consuming calculation if this result is needed more >than once in an SQL update query? This solution might be PostgreSQL specific and not standard SQL compliant. > Here is an example of what I want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate the duration time. >First of all it would make the SQL statement faster and secondly much more cleaner and easily to understand. Something like: with my_calc as ( select pk, time_consuming_calculation_1 as calc1, time_consuming_calculation_2 ascalc2 from foo ) update foo set startTime = my_calc.calc1, stopTime = my_calc.calc2, duration = my_calc.calc2 - calc1 where foo.pk = my_calc.pk; http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING