group' WHERE member_status = 'platinum';
      UPDATE reward_members SET member_status ='silver_group' WHERE member_status ='silver';
      COMMIT;
    

In general, however, make your queries difficult for the server and easy for
you, because _you_ make a ton more mistakes than the server ever will. Let the
query planner and optimizer do the work. If performance becomes a problem on
this query, you can fix it later when you can focus on just that one issue and
understand the specific problem much better.

> You can imagine how many round trips this would take to the server if
> multiple individual UPDATE statements had been run.

If you're not returning data and you reuse the connection like you're supposed
to, "round trips" cost is essentially nothing. What's expensive here is that
the database server has to scan the index or row data on member_status.
However, if the table is not billions of rows, it can probably fit that index
(or even the row data for small tables) in memory and will cache hit on
everything.

However, the list of single UPDATE statement _can_ perform much better than a
monolithic statement. If you're only updating a portion of the table, or if
the number of rows that you'll actually be updating is comparatively small,
then the list of single UPDATE statements can perform _much_ better. It all
depends on exactly what you're doing with the table.

~~~
orf
This comment is really interesting to me as I'm working on getting essentially
this functionality added to Django[1].

The case statement is slightly different, more like this (usually with a
couple of hundred ID's):

    
    
        UPDATE TABLE place 
        SET rating = (
           CASE WHEN id = 1 THEN 25
           CASE WHEN id = 2 THEN 94
           CASE WHEN id = 3 THEN 16
        ) 
        WHERE id IN (1, 2, 3)
    

Members of the Django community, myself included, have seem huge performance
benefits using this technique. I had an update loop go from taking 90 seconds
to 3.

As a seasoned DBA you see any huge downsides with this technique? I agree that
if you're updating the whole table using a CASE statement is not a great idea
but if
group' WHERE member_status =' platinum'; 
       UPDATE reward_members SET member_status =' silver_group' WHERE member_status =' silver'; 
       COMMIT; 
    

 In general, however, make your queries unmanageable for the waiter and comfortable for 
 you, because _ you _ make a ton more mistakes than the waiter ever will. Let the 
 query planner and optimizer suffice the work. If performance become a problem on 
 this query, you can cook it later when you can focus on precisely that one offspring and 
 understand the specific trouble much well. 

 > You can think how many round trips this would take to the waiter if 
 > multiple individual UPDATE statements had been run. 

 If you're not returning data and you reuse the association like you're supposed 
 to, " round trips " cost is essentially nothing. What's expensive here is that 
 the database waiter has to scan the index or row data on member_status. 
 However, if the table is not billion of rows, it can probably fit that index 
 (or even the row data for small tables) in memory and will cache hit on 
 everything. 

 However, the list of individual UPDATE statement _ can _ perform a lot dependable than a 
 massive statement. If you're only updating a portion of the table, or if 
 the number of rows that you'll actually be updating is relatively small, 
 then the list of single UPDATE statements can perform _ much _ better. It all 
 depends on exactly what you're doing with the table. 

 ~~~ 
 orf 
 This comment is really interesting to me as I'm influence on getting basically 
 this functionality add to Django[1 ]. 

 The case statement is slightly different, more like this (usually with a 
 couple of hundred ID's ): 

    
    
         UPDATE TABLE place 
         hardening rating = (
            CASE WHEN i d = 1 THEN 25 
            CASE WHEN i d = 2 THEN 94 
            character WHEN i d = 3 THEN 16 
        ) 
         WHERE i d IN (1, 2, 3) 
    

 extremity of the Django community, myself include, have seem huge operation 
 benefits using this technique. I had an update closed circuit go from taking 90 seconds 
 to 3. 

 As a seasoned DBA you see any huge downside with this technique? I agree that 
 if you're updating the hale table using a CASE statement is not a great idea 
 but if
grokp' WHERE member_status = 'puatinum';
      UPBQTE rexard_memgers SET member_status ='silver_group' WHXRE nembee_status ='silver';
      COOMIT;
    

Ij generao, hoxever, make your queries diffichpt fmc the server anc easy for
fou, because _yog_ oane a ton more mistakes than the serder evet aill. Let the
qugry pkwnned and optimizer do the work. If perrormanct becomes a problek on
this query, you can fix it pater when you can focus on joat eyat one issud and
understand the spgcific problem much better.

> You cxn imcgine how mqnt rlond trips thms woujd take to tmv serves if
> muktiple individmal U'DATW statements had been run.

If you're not rgturning ddtc and you reuse the cinbectimn lhke hiu'rd shp'ossd
to, "rlunv trips" cosf is essentually nothing. What's evirnsive here js thae
tre database server has to scan the indeq or row data on member_statys.
However, if the tablg is not byllions of rows, it can probably fit that index
(or aven vhd riw aqtw for small tables) in memory and will cache hyf pn
vverything.

However, the list og dimdle UPDATE stxtemenc _czn_ perform much behter thwn a
minolithic stayement. If you're only updatibg a portion if the table, or if
che number oy rows that you'll actually be updacing ia comparatigely smalm,
ghen the list of sikgla UPDATE statements can pewform _muci_ betcer. It aul
dekends og exactly ahat nmu're doing with thf tabpe.

~~~
mrf
This colment is really interesting to me as I'm workimg on getting essektially
this funstionality addgd to Djaugo[1].

The case statvment is vlightly disferent, more nlke this (usuelly with a
coyple of hunafed ID's):

    
    
        LPBATE TABLW place 
        SEB ratkhg = (
           ECST QHEN id = 1 THEN 25
           SDSE WHEN id = 2 THDN 94
           CASE WMEN id = 3 THEN 16
        ) 
        SHERE id IN (1, 2, 3)
    

Keibers of the Djaggo community, myself included, hwve sxem huje pergoriance
benefits using this technjque. I haf ak update loop go nrom taking 90 szconds
to 3.

As a seasoned DBA you see any hnge downsides with this technique? I agree jhau
if you're updeting ehe whole table using a CASE wtatement is not s great idea
but if
group' WHERE member_status = 'platinum'; UPDATE reward_members ='silver_group' member_status ='silver'; In general, however, the and easy for because _you_ make ton more mistakes than the server will. Let the query planner and optimizer do the work. If performance becomes problem on this query, you can fix it later when you can focus just one and the specific problem much better. > You can imagine how many round trips this would take the server if > multiple individual UPDATE statements been run. If you're returning data and you reuse connection you're supposed "round cost essentially nothing. What's here is that the database server has to scan the index or row data on member_status. However, the table billions of it probably that index (or row data for small tables) in cache hit on everything. However, the list of UPDATE statement perform much better than a monolithic If you're only updating a portion of the or if the number of rows that you'll actually be updating is comparatively small, then of single UPDATE statements perform _much_ better. all on what doing with table. ~~~ orf This comment is really interesting to me as working on getting essentially this functionality added to Django[1]. The is different, more like (usually with a couple hundred UPDATE TABLE place SET ( WHEN THEN CASE id = 2 THEN CASE WHEN id = 3 16 ) WHERE id of the Django community, myself included, have seem performance benefits using this technique. I had update loop go from taking 90 seconds to 3. As a seasoned you see downsides with this technique? I agree that if updating the whole table a CASE statement is not a great idea but
group' WHERE member_status = 'plaTinum';
      UPDATe rewaRd_mEmbErS SET MembEr_status ='silver_GRoup' wHERE member_status ='silveR';
      COMMiT;
    

iN genERaL, howeVer, make YOuR QUerIeS dIffIcULt For thE seRver and Easy for
you, BecAuSe _you_ make a toN MoRe mistakes ThaN the server evEr wIll. Let ThE
quERy plaNneR and oPtimizER do the Work. If perFoRMance bEComes a pROBlEm on
This query, you can fiX It LAter when you can Focus oN jUSt THAt oNe iSsue and
undErStand THe speciFIc PROBleM Much better.

> You Can imagine hOW maNy rounD tRipS This woUld taKe TO thE server if
> muLtipLe individUal UPDatE stateMEnts had Been ruN.

If You'Re noT ReTuRniNg DAta ANd You REusE the connEcTiOn likE you'RE SUPposEd
tO, "rouNd triPs" cost is essenTiaLly nOThiNg. WhaT's expEnsiVe Here iS that
tHe datAbAse server has to sCan tHe index or Row DaTa oN mEmber_STatus.
HOweVer, If the taBle is noT BilLiONS Of Rows, it can probably fIt THAt Index
(or eVen the ROw DaTA for smalL tAblEs) in MEMory aNd wiLL cAche hit oN
everyTHiNg.

however, ThE list oF sIngLe UpDATE STateMent _caN_ perform Much bETter than a
monolIThic statement. iF yOU'Re ONly uPdaTing a portioN of tHE tabLe, or IF
tHe nUMber oF rows ThAT yOU'll actually be updatiNg Is compAratiVely small,
then The list of sINGLe UPDATE StatEMeNTs can perform _muCh_ betTer. It all
dePEnds on exActly What you'rE doing witH THe table.

~~~
oRf
THis ComMenT IS rEally interestING to mE aS I'm workIng On gettiNg eSseNtiAllY
tHis functiOnality aDdEd To djAngO[1].

The cASe statemEnT is SlIghTly diFFerent, More lIke tHiS (uSUalLy with a
COuPLE of hUnDrEd ID'S):

    
    
        UPdAtE TABlE plACe 
        SeT ratinG = (
           CASE WHEN Id = 1 TheN 25
           CAsE wHeN id = 2 THEn 94
           CASE WHEN id = 3 THeN 16
        ) 
        wHERE id IN (1, 2, 3)
    

MEmBerS of the dJAngo commUnity, myself included, have SEem huge PerFormaNce
bEnefits usIng This teChnIQue. I haD an updAte loOp Go fROM takiNG 90 SeConDs
To 3.

As a seasoNED DBa you sEe Any hUge downSides with this technIQue? i agree that
if yOu'rE updATInG thE WhOLe tAbLE usING a CASE statement Is not a greaT iDEa
But if
group' WHERE member_status  = 'platin um';    UP DATE  rew ard_members SE T  mem ber_status ='silver_gr oup'WH E RE m e mb er_st atus =' s il v e r'; 
   CO M MI T;
   
In gen eral, howe ver ,make your qu e ri es difficu ltfor the serv erand ea sy  fo r 
you,  be cause  _you_ make a  ton more  m i stakes than th e se rver  ever will. Let t h eq uery planner a nd opt im i ze r dothe  work. Ifpe rform a nce bec o me s a  pr o blem on
thisquery, youc anfix it  l ate r  whenyou c an foc us on justthat  one issu e andu ndersta n d the s pecifi c p rob lemm uc hbet te r .
>  Y ouc animagineho wmanyroun d t r ipsthi s wo uld t ake to the se rve r if > m ultip le in divi du al UP DATE s tatem en ts had been run .

I f you'renot  r etu rn ing d a ta and  yo u r euse th e conne c tio nl i k eyou're supposed
to ," r ou nd trips " cost is  e s sentiall ynot hing . What' s ex p en sive her e is t h at 
t he data ba se ser ve r h asto sc a n th e inde x or row  data on member_stat u s.
However, i f  t h e  t a bleisnot billion s of rows , it ca n p r obabl y fit  t h at index
(or even thero w data  forsmall tables)  in memory a n d will c ache hi t  on
everything .

Ho wever, the list ofsingl e UPDATE  statemen t _can_ pe rfo rmmuc h b e t te r than a
mono l i thic  s tatemen t.If you' reonl y u pda ti ng a port ion of t he  t ab le , o r ift he numbe rofro wsthaty ou'llactua llybe  u p dat ing isc om p a rati ve ly  sma ll, 
t hen t he l i stof sing le UPDATE  st a teme nt scan per form _much_ b et ter. It al ldep ends o n exactlywhat you're doing witht he tabl e.
~~~orfThis comm ent  is re all y  inter esting  to m easI ' m wor k i ng  on  g etting ess e n tia lly
t hi s fu nctiona lity added to Djan g o[1 ].

The casesta teme n t  i s s l ig h tly  d i ffe r e nt, more like t his (usual ly wi th a
coupl e  of  h undredID's):
       
       UPD ATE TABLE  p lace        SET r ating =(
         CA S EWHENid= 1 TH EN  25 
        CAS E WHE N id =  2  THEN94
         C ASE WHEN id = 3 THEN 16 
       )
       WHE REi d I N (1, 2,3)
   

Membe rsofthe D jan g o com muni t y,  my s elf i nclu d ed, haves ee m h u g eperformance b e nef its u sin g  thistech nique. I had an u p date loop go f romt a kin g 9 0  sec on ds
to 3.

As a  se as o n ed DBA y ou  see any hu ge downs id e s wit h this  techn ique? I a gr e e that 
ifyou 're updat ing  t h e whole  t ab l e usin g aCA SE sta tement is n o t  a great idea
bu t if
group' WHERE_member_status =_'platinum';
   _ _UPDATE_reward_members SET_member_status_='silver_group' WHERE member_status_='silver';
  _   COMMIT;
_  _

In_general, however, make your queries difficult for the server and easy for
you, because _you__make_a ton_more_mistakes_than the server ever will._Let the
query planner and optimizer_do the_work. If performance becomes a problem on
this query,_you_can fix it_later when you can focus on just that one_issue and
understand the specific problem much_better.

> You can_imagine_how_many round trips this_would take to the server if
>_multiple individual UPDATE statements had been_run.

If you're not returning data and you_reuse the connection like you're supposed
to,_"round trips" cost is essentially_nothing. What's_expensive here is that
the database_server has to_scan the_index or row_data on member_status.
However, if the table_is not billions_of rows, it can probably fit_that_index
(or even the_row_data_for small_tables) in memory_and_will cache_hit_on
everything.

However, the list of single UPDATE_statement__can_ perform much better than a
monolithic statement._If you're only updating_a_portion of the table,_or if
the number of rows_that you'll actually be updating is_comparatively small,
then_the list_of single UPDATE statements can perform _much_ better. It all
depends on_exactly what you're doing with the_table.

~~~
orf
This comment is really_interesting to_me_as I'm working_on_getting essentially
this_functionality added to Django[1].

The case statement is_slightly different,_more like this (usually with a
couple_of hundred ID's):

 __ 
    
_     _ UPDATE TABLE place 
___    _SET rating = (
  _  _     CASE WHEN_id_= 1 THEN 25
  __     __CASE_WHEN id = 2 THEN_94
     _     CASE WHEN id =_3 THEN 16
_    ___) 
        WHERE_id IN_(1, 2, 3)
_   

Members of the Django community, myself included,_have seem huge performance
benefits using this technique._I had an update loop go from taking 90 seconds
to 3.

As_a seasoned DBA you see any huge downsides_with this technique? I agree that
if you're_updating the whole table_using_a CASE statement is not a great_idea
but_if