optimizing the mysql plsql block

[Yakgna Kumar]

hello all,

need help in understanding the better way of optimizing the below plsql block. I am new to MySQL, appreciate pointers/ suggestions to improve the plsql block. currently clients says its slow on millions of records. I am also new to plsql. kindly provide your support to help me understand in which way I can rewrite/optimize the below block

		drop table temp_table;

		create global temporary table temp_table like rts_model without index;

		drop procedure build_rts_model;

		create procedure build_rts_model
		as
		begin
		  declare iteration       tinyint := 0;
		  declare temp_table_size integer := 0;

		  declare cursor size_cursor for
		  select count(*) from temp_table;

		  insert into temp_table
		  select
		    m.journey_id,
		    m.journey_index,
		    m.route_index,
		    m.line_id,
		    m.start_index,
		    m.end_index,
		    m.start_time,
		    r.peak_time
		  from
		    rts_timetable r,
		    rts_model     m
		  where
		    m.start_time <> 0
		    and
		    m.end_time = 0
		    and
		    m.line_id = r.line_id
		    and
		    r.start_index = m.start_index
		    and
		    r.end_index = m.end_index
		    and
		    r.iteration = 0;

		  update
		    rts_model
		  set
		    end_time = temp_table.end_time
		  from
		    rts_model,
		    temp_table
		  where
		    rts_model.journey_id = temp_table.journey_id
		    and
		    rts_model.journey_index = temp_table.journey_index
		    and
		    rts_model.route_index = temp_table.route_index;

		  update
		    rts_model
		  set
		    start_time = temp_table.end_time
		  from
		    rts_model,
		    temp_table
		  where
		    rts_model.journey_id = temp_table.journey_id
		    and
		    rts_model.journey_index = temp_table.journey_index
		    and
		    rts_model.route_index = temp_table.route_index + 1;

		  open size_cursor;
		  fetch size_cursor into temp_table_size;
		  close size_cursor;

		  while :temp_table_size > 0 do
		    truncate table temp_table;

		    insert into temp_table
		    select
		      m.journey_id,
		      m.journey_index,
		      m.route_index,
		      m.line_id,
		      m.start_index,
		      m.end_index,
		      m.start_time,
		      r.peak_time
		    from
		      rts_timetable r,
		      rts_model     m
		    where
		      m.start_time <> 0
		      and
		      m.end_time = 0
		      and
		      m.line_id = r.line_id
		      and
		      r.start_index = m.start_index
		      and
		      r.end_index = m.end_index
		      and
		      r.iteration = 0;

		    update
		      rts_model
		    set
		      end_time = temp_table.end_time
		    from
		      rts_model,
		      temp_table
		    where
		      rts_model.journey_id = temp_table.journey_id
		      and
		      rts_model.journey_index = temp_table.journey_index
		      and
		      rts_model.route_index = temp_table.route_index;

		    update
		      rts_model
		    set
		      start_time = temp_table.end_time
		    from
		      rts_model,
		      temp_table
		    where
		      rts_model.journey_id = temp_table.journey_id
		      and
		      rts_model.journey_index = temp_table.journey_index
		      and
		      rts_model.route_index = temp_table.route_index + 1;

		    open size_cursor;
		    fetch size_cursor into temp_table_size;
		    close size_cursor;
		  end while;
		end;

Edited 1 time(s). Last edit at 10/17/2014 08:09AM by Peter Brawley.