query optimization 1

[anandsatchin]

Hi...

I need to exectue all the 6 queries in a sequential order which are stored in stored procedure.
I am just wondering is there any way to make
general into one query with CASE statements or any other better statement I can use.

Cheers


DB: MySql 5.0

Table Name:
sorting_temp

Column Names:

status_update
status_code
date_of_change

product_category_id
doa_or_nff
current_status_of_job

date_into_sorting
date_out_sorting
date_passed_to_sales


1.
update sorting_temp
set status_update='Y', status_code = 'JJJJ', date_of_change = date_passed_to_sales
where (date_passed_to_sales is not null or date_passed_to_sales != '')

2.
update sorting_temp
set status_update='Y', status_code = 'GGGG', date_of_change = date_out_sorting
where current_status_of_job = 'BER-SCRAPPED' and status_update ='N';

3.
update sorting_temp
set status_update='Y', status_code = 'JJJJ', date_of_change = date_out_sorting
where (doa_or_nff = 'NFF' or doa_or_nff = 'CDNFF')
and (date_out_sorting is not null or date_out_sorting != '')
and status_update ='N';

4.
update sorting_temp
set status_update='Y', status_code = 'EEEE', date_of_change = date_out_sorting,
where doa_or_nff = 'BER' and status_update ='N';


5.
update sorting_temp
set status_update='Y', status_code = 'BBBB', date_of_change = date_into_sorting,
where product_category_id = 'NOT RECEIVED' and status_update ='N';

6.
update sorting_temp
set status_update='Y', status_code = 'DDDD', date_of_change = date_into_sorting,
where doa_or_nff = 'EMPTY BOX' and status_update ='N';

 

[rac2]

So you are thinking one update statement with a lot of computation might be faster than six update statements with no computation?

Possibly you could write it that way.
The expressions would be horribly complicated, each column would have a six level CASE expression. It would look a mess. No one could make sense of it, probably not even yourself six months from now.

In principle, the single table update would be faster, but how much faster can only be seen by testing. My guess is that you would have difficulty measuring the performance difference short of updating a million rows.

Just for fun

UPDATE sorting_temp SET 
  status_update = 
    CASE 
      WHEN date_passed_to_sales IS NOT NULL 
            OR 
            date_passed_to_sales != '' THEN 'Y'
      WHEN current_status_of_job = 'BER-SCRAPPED'
           AND status_update ='N' THEN 'Y'
      WHEN (doa_or_nff = 'NFF' 
            OR doa_or_nff = 'CDNFF'
           )
           AND
           (date_out_sorting IS NOT NULL
            OR date_out_sorting != ''
           )
           AND status_update ='N' THEN  'Y'
      WHEN THEN 
      WHEN THEN 
      WHEN THEN 
      ELSE 'N'
    END, 
  status_code = 
    CASE 
      WHEN (date_passed_to_sales IS NOT NULL 
            OR 
            date_passed_to_sales != '') THEN 'JJJJ'
      WHEN current_status_of_job = 'BER-SCRAPPED'
           AND status_update ='N' THEN 'GGGG'
      WHEN (doa_or_nff = 'NFF' 
            OR doa_or_nff = 'CDNFF'
           )
           AND
           (date_out_sorting IS NOT NULL
            OR date_out_sorting != ''
           )
           AND status_update ='N' THEN 'JJJJ'
      WHEN THEN 
      WHEN THEN 
      WHEN THEN 
      ELSE NULL
    END, 
  date_of_change = 
    CASE 
      WHEN (date_passed_to_sales IS NOT NULL 
            OR 
            date_passed_to_sales != '') THEN date_passed_to_sales
      WHEN current_status_of_job = 'BER-SCRAPPED'
           AND status_update ='N' THEN date_out_sorting
      WHEN (doa_or_nff = 'NFF' 
            OR doa_or_nff = 'CDNFF'
           )
           AND
           (date_out_sorting IS NOT NULL
            OR date_out_sorting != ''
           )
           AND status_update ='N' THEN date_out_sorting
      WHEN THEN 
      WHEN THEN 
      WHEN THEN 
      ELSE NULL
    END

 

[anandsatchin]

Hi...

Thank a lot for your respone.

I will have go with my test server. The table has around 5000 rows only.

The main concern was that.. I thought it is not professionaly to write 6 update statement and was sure that there must be a way to put into a single query using case statement(which was answered by your reply).

Cheers
Anand

 

[johnwm]

If you are doing a set of transactions that need to ALL succeed (or NONE succeed) look at START TRANSACTION, COMMIT, and ROLLBACK. They are documented at

http://dev.mysql.com/doc/refman/5.0/en/commit.html

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints