Thread: Reuse temporary calculation results in an SQL update query

Reuse temporary calculation results in an SQL update query

From
Matthias Nagel
Date:
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




Re: Reuse temporary calculation results in an SQL update query

From
Andreas Kretschmer
Date:

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



Re: Reuse temporary calculation results in an SQL update query

From
Matthias Nagel
Date:
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




Re: Reuse temporary calculation results in an SQL update query

From
David Johnston
Date:
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.




Re: Reuse temporary calculation results in an SQL update query

From
Thomas Kellerer
Date:
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





Re: Reuse temporary calculation results in an SQL update query

From
Andreas Kretschmer
Date:

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



Re: Reuse temporary calculation results in an SQL update query [SOLVDED]

From
Matthias Nagel
Date:
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




Re: Reuse temporary calculation results in an SQL update query [SOLVDED]

From
"David Johnston"
Date:
>
> 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.






Re: Reuse temporary calculation results in an SQL update query

From
Jasen Betts
Date:
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




Re: Reuse temporary calculation results in an SQL update query

From
Thomas Kellerer
Date:
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