Re: SQL: incrementare valore

[Federico Di Gregorio]

On 30/04/17 08:41, Paolo Redaelli wrote:

Il 29/04/2017 22:49, Alessandro Pellizzari ha scritto:

On 29/04/17 14:10, Giuliano Curti wrote:


UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE
r_id =

la funzione UPDATE itera su tutti i record, la funzione SELECT
nidificata, che anch'essa itera su tutti i record, non risulta
penalizzante? si avrebbe un algoritmo di complessità N**2;

Questo vale solo se non hai indici sul DB.

Perdonatemi se mi intrometto, ma una bella EXPLAIN non risolverebbe
tutti i nostri dubbi?



Non ch'io abbia dei dubbi, almeno su di PostgreSQL ma per curiosità ho 
creato una tabella come segue:


create table test (id serial, a integer, b integer);

e dopo aver inserito qualche riga ho ottenuto:

explain verbose update test set a = (select max(a) from test) - b where 
id = 1;


 Update on public.test  (cost=35.51..71.03 rows=10 width=18)
   InitPlan 1 (returns $0)
 ->  Aggregate  (cost=35.50..35.51 rows=1 width=4)
   Output: max(test_1.a)
   ->  Seq Scan on public.test test_1  (cost=0.00..30.40 
rows=2040 width=4)

 Output: test_1.id, test_1.a, test_1.b
   ->  Seq Scan on public.test  (cost=0.00..35.52 rows=10 width=18)
 Output: test.id, ($0 - test.b), test.b, test.ctid
 Filter: (test.id = 1)

dove si vede chiaramente che la subquery viene eseguita solo una volta 
ed il risultato memorizzato in $0.


Ciao,

federico

--
Federico Di Gregorio federico.digrego...@dndg.it
DNDG srl  http://dndg.it
  Qu'est ce que la folie? Juste un sentiment de liberté si
   fort qu'on en oublie ce qui nous rattache au monde... -- J. de Loctra

Re: SQL: incrementare valore

[Leonardo Boselli]

Informazione aggiuntiva: si lavora sempre su una sola riga, anche perché 
r_to è sempre >= r_fr, quindi dopo ogni aggiornamento max(r_co) di 
solito cambia. (dico di solito perché in casi molto particolari r_fr e 
r_to potrebbero essere uguali). se per caso dovessi aggiornare più di una 
riga la subselect dovrebbe comunque essere fatta ogni volta.

Il database è maria .

On Sat, 29 Apr 2017, Gian Uberto Lauri wrote:


On 29/04/2017 10:17, Federico Di Gregorio wrote:

Puoi fare:

UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE r_id 
= ...


Questa soluzione è sicuramente corretta solo se l'update va fatta su una 
singola riga.


Se invece si devono aggiornare in blocco N righe (i.e. 'where id in ...), 
allora AFAIK il risultato
dipende da come è stato implementato l'ottimizzatore di query: se decide di 
calcolare la max
una volta per tutte all'inizio si avrà un risultato, altrimenti se ne avrà un 
altro e tempi di

esecuzione più lunghi.

[E può anche esserci uno standard che sancisce uno o l'altro comportamento, 
bisogna

comunque vedere se il DBMS lo rispetta.]

In ogni caso, in dipendenza dalle esigenze della logica di business, si 
possono avere
due situazioni a seconda o meno che serva che venga ricalcolato il max per 
ogni riga, e
comunque affidarsi alla buona grazia dell'implementazione dell'ottimizzatore 
non è una
soluzione accettabile oggi. Lavorare come Mel andava bene negli anni '50/60 
del secolo

scorso.

La stored procedure rimane una soluzione affidabile ed elegante.
--
Gian, stavolta non su uno smartphone.





--
Leonardo Boselli
Dipartimento Ingegneria Civile e Ambientale UNIFI
tel +39 0552758808  +39 3488605348

Re: SQL: incrementare valore

[Paolo Redaelli]

Il 29/04/2017 22:49, Alessandro Pellizzari ha scritto:
> On 29/04/17 14:10, Giuliano Curti wrote:
>
>>> UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE
>>> r_id =
>> la funzione UPDATE itera su tutti i record, la funzione SELECT
>> nidificata, che anch'essa itera su tutti i record, non risulta
>> penalizzante? si avrebbe un algoritmo di complessità N**2;
> Questo vale solo se non hai indici sul DB.
Perdonatemi se mi intrometto, ma una bella EXPLAIN non risolverebbe
tutti i nostri dubbi?

Re: SQL: incrementare valore

[Giuliano Curti]

On 4/29/17, Federico Di Gregorio  wrote:
> ...

On 4/29/17, Alessandro Pellizzari  wrote:
> On 29/04/17 14:10, Giuliano Curti wrote:
> 

Federico e Alessandro, grazie;

ciao,
giuliano

Re: SQL: incrementare valore

[Alessandro Pellizzari]

On 29/04/17 14:10, Giuliano Curti wrote:

>> UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE
>> r_id =

> la funzione UPDATE itera su tutti i record, la funzione SELECT
> nidificata, che anch'essa itera su tutti i record, non risulta
> penalizzante? si avrebbe un algoritmo di complessità N**2;

Questo vale solo se non hai indici sul DB.

La sub-select viene eseguita per prima.

Se hai un indice su r_co lo usa, ed essendo gia` ordinato puo`
risolversi in una singola lettura o poco piu`.

Se non hai l'indice su r_co fa un table scan.

Alla fine prende il risultato e lo sostituisce nella query principale.

A questo punto se hai un indice su r_id (e visto il nome dovresti
averlo) fa un singolo accesso anche nella update, perche` prima prende
la riga puntandola direttamente, prende r_to e r_fr, fa il calcolo, la
aggiorna e la scrive.

> non è possibile memorizzare MAX(r_co) in una variabile temporanea e
> poi usarla nel ciclo UPDATE in modo da avere un algoritmo di
> complessita N? la domanda deriva dal fatto che non conosco se in SQL è
> possibile farlo (in uno script python non avrei dubbi);

Se lo fai in python devi lockare la tabella, fare la prima query,
calcolare il valore, fare l'update e togliere il lock.

Puoi usare una stored procedure che fa lo stesso, ma evita il traffico
di rete (e probabilmente la query e` precompilata) eseguendo tutto sul DB.

Ma se usi la update con subquery (se il DB le supporta. Non e` scontato)
e` automaticamente atomica e non devi preoccuparti di fare il lock a mano.

Bye.

Re: SQL: incrementare valore

[Federico Di Gregorio]

Il motore del database e in grado di capire quando una subquery non dipende 
dalla riga dell'update ed eseguirla una volta sola anche in casi molto ma molto 
più complessi di questo.

Ovviamente puoi cercare di sostituirti all'ottimizzatore scrivendo delle stored 
procedure ogni volta che la query si complica ma raramente è una buona idea. 
Nel 99% dei casi l'ottimizzatore produce i risultati più velocemente. (Sempre 
che il database sotto non sia una caccola tipo MySQL.)

federico


Il 29 aprile 2017 15:02:14 CEST, Giuliano Curti  ha 
scritto:
>On 4/29/17, Federico Di Gregorio  wrote:
>> Puoi fare:
>>
>> UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE
>r_id =
>> ...
>
>domanda x capire, non per obiettare :-)
>la funzione UPDATE itera su tutti i record, la funzione SELECT
>nidificata, che anch'essa itera su tutti i record, non risulta
>penalizzante? si avrebbe un algoritmo di complessità N**2;
>
>non è possibile memorizzare MAX(r_co) in una variabile temporanea e
>poi usarla nel ciclo UPDATE in modo da avere un algoritmo di
>complessita N? la domanda deriva dal fatto che non conosco se in SQL è
>possibile farlo (in uno script python non avrei dubbi);
>
>
>> Ciao,
>> Federico
>
>grazie, ciao,
>giuliano

-- 
Inviato dal mio dispositivo Android con K-9 Mail. Perdonate la brevità.

Re: SQL: incrementare valore

[Giuliano Curti]

On 4/29/17, Federico Di Gregorio  wrote:
> Puoi fare:
>
> UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE r_id =
> ...

domanda x capire, non per obiettare :-)
la funzione UPDATE itera su tutti i record, la funzione SELECT
nidificata, che anch'essa itera su tutti i record, non risulta
penalizzante? si avrebbe un algoritmo di complessità N**2;

non è possibile memorizzare MAX(r_co) in una variabile temporanea e
poi usarla nel ciclo UPDATE in modo da avere un algoritmo di
complessita N? la domanda deriva dal fatto che non conosco se in SQL è
possibile farlo (in uno script python non avrei dubbi);


> Ciao,
> Federico

grazie, ciao,
giuliano

Re: SQL: incrementare valore

[Gian Uberto Lauri]

On 29/04/2017 10:17, Federico Di Gregorio wrote:

Puoi fare:

UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE 
r_id = ...


Questa soluzione è sicuramente corretta solo se l'update va fatta su una 
singola riga.


Se invece si devono aggiornare in blocco N righe (i.e. 'where id in 
...), allora AFAIK il risultato
dipende da come è stato implementato l'ottimizzatore di query: se decide 
di calcolare la max
una volta per tutte all'inizio si avrà un risultato, altrimenti se ne 
avrà un altro e tempi di

esecuzione più lunghi.

[E può anche esserci uno standard che sancisce uno o l'altro 
comportamento, bisogna

comunque vedere se il DBMS lo rispetta.]

In ogni caso, in dipendenza dalle esigenze della logica di business, si 
possono avere
due situazioni a seconda o meno che serva che venga ricalcolato il max 
per ogni riga, e
comunque affidarsi alla buona grazia dell'implementazione 
dell'ottimizzatore non è una
soluzione accettabile oggi. Lavorare come Mel andava bene negli anni 
'50/60 del secolo

scorso.

La stored procedure rimane una soluzione affidabile ed elegante.
--
Gian, stavolta non su uno smartphone.

Re: SQL: incrementare valore

[Federico Di Gregorio]

Puoi fare:

UPDATE mee SET r_co = (SELECT max(r_co) FROM mee) + r_to - r_fr WHERE r_id = ...

Ciao,
Federico


Il 28 aprile 2017 20:53:07 CEST, Leonardo Boselli  ha 
scritto:
>Un po'ot ma chiedere non costa nulla:
>ho un file SQL che contiene la tabella 'mee' con le colonne r_id r_fr
>r_to 
>e r_co dove r_id è un intero(5) e r_co un decimale(8,2)
>esiste un comando che mi faccia:
>UPDATE mee SET r_co= *non_lo_so*  WHERE r_id = *qualcosa*;
>
>*qualcosa* è l'id unico del record, e ovviamente lo conosco
>
>il *non_lo_so* è la cosa per cui chiedo aiuto: si tratta del valore più
>
>alto presente nella colonna r_co prima dell'aggiornamento più la 
>differenza tra r_to e r_fr della corrispondente riga.
>C'è un modo elegante di fare questa operazione ?
>
>
>--
>Leonardo Boselli
>Dipartimento Ingegneria Civile e Ambientale UNIFI
>tel +39 0552758808  +39 3488605348

-- 
Inviato dal mio dispositivo Android con K-9 Mail. Perdonate la brevità.

Re: SQL: incrementare valore

[Federico Di Gregorio]

Aveva chiesto una soluzione elegante! :)

federico


Il 28 aprile 2017 21:10:53 CEST, Gian Uberto Lauri  ha scritto:
>Store procedure
>
>Sent from my smartphone 
>
>> On 28 Apr 2017, at 20:53, Leonardo Boselli 
>wrote:
>> 
>> Un po'ot ma chiedere non costa nulla:
>> ho un file SQL che contiene la tabella 'mee' con le colonne r_id r_fr
>r_to e r_co dove r_id è un intero(5) e r_co un decimale(8,2)
>> esiste un comando che mi faccia:
>> UPDATE mee SET r_co= *non_lo_so*  WHERE r_id = *qualcosa*;
>> 
>> *qualcosa* è l'id unico del record, e ovviamente lo conosco
>> 
>> il *non_lo_so* è la cosa per cui chiedo aiuto: si tratta del valore
>più alto presente nella colonna r_co prima dell'aggiornamento più la
>differenza tra r_to e r_fr della corrispondente riga.
>> C'è un modo elegante di fare questa operazione ?
>> 
>> 
>> --
>> Leonardo Boselli
>> Dipartimento Ingegneria Civile e Ambientale UNIFI
>> tel +39 0552758808  +39 3488605348
>> 
>> 

-- 
Inviato dal mio dispositivo Android con K-9 Mail. Perdonate la brevità.

Re: SQL: incrementare valore

[Gian Uberto Lauri]

Store procedure

Sent from my smartphone 

> On 28 Apr 2017, at 20:53, Leonardo Boselli  wrote:
> 
> Un po'ot ma chiedere non costa nulla:
> ho un file SQL che contiene la tabella 'mee' con le colonne r_id r_fr r_to e 
> r_co dove r_id è un intero(5) e r_co un decimale(8,2)
> esiste un comando che mi faccia:
> UPDATE mee SET r_co= *non_lo_so*  WHERE r_id = *qualcosa*;
> 
> *qualcosa* è l'id unico del record, e ovviamente lo conosco
> 
> il *non_lo_so* è la cosa per cui chiedo aiuto: si tratta del valore più alto 
> presente nella colonna r_co prima dell'aggiornamento più la differenza tra 
> r_to e r_fr della corrispondente riga.
> C'è un modo elegante di fare questa operazione ?
> 
> 
> --
> Leonardo Boselli
> Dipartimento Ingegneria Civile e Ambientale UNIFI
> tel +39 0552758808  +39 3488605348
> 
> 

SQL: incrementare valore

[Leonardo Boselli]

Un po'ot ma chiedere non costa nulla:
ho un file SQL che contiene la tabella 'mee' con le colonne r_id r_fr r_to 
e r_co dove r_id è un intero(5) e r_co un decimale(8,2)

esiste un comando che mi faccia:
UPDATE mee SET r_co= *non_lo_so*  WHERE r_id = *qualcosa*;

*qualcosa* è l'id unico del record, e ovviamente lo conosco

il *non_lo_so* è la cosa per cui chiedo aiuto: si tratta del valore più 
alto presente nella colonna r_co prima dell'aggiornamento più la 
differenza tra r_to e r_fr della corrispondente riga.

C'è un modo elegante di fare questa operazione ?


--
Leonardo Boselli
Dipartimento Ingegneria Civile e Ambientale UNIFI
tel +39 0552758808  +39 3488605348