hi all,

i had proble with below stored procedure.

DELIMITER ‘/’;
  CREATE PROCEDURE generatedate()
  BEGIN
  declare i DATETIME;
  set i = ‘2005-01-10 20:05:05’;
  create table DIM_DATE_TIME(date varchar(13),year integer(4),quarter varchar(2),month varchar(10),week int(2),day
  int(2),dayname varchar(10),hour integer(2));
  while i <now() DO
  SET i = DATE_ADD(i, INTERVAL 1 hour);
  insert into DIM_DATE_TIME values(i,YEAR(i),concat(‘Q’,QUARTER(i)),MONTHNAME(i),WEEK(i),DAY(i),DAYNAME(i),HOUR(i));
  END WHILE;
  END;/
  DELIMITER ;

infobriht:
First time this exectued in 30 min .
if am exectuting the same procedure again it takes 40 minutes after that and 70 minutes .

same procedere is executed in less than 3 sec in mysql.

can you please tell how can i reduce this timelines in infobriht??

Is there any specific instructions for stored procedured in infobright?

Hi !

I can’t see “engine=brighthouse” in your table definition, so I take that brighthouse is your default engine.

The procedure is OK. It executes slowly because it does inserts. Inserts take time. Try modify the code like this:

With this modification writing new rows will happen only at the end, not for each row - should be much faster.
Also the coming release of Infobright handles inserts much more efficiently - I recommend to upgrade when the new release is available.

hi Borkowski,

Thank you for your valuable suggestion.

You solved my problem. with this modification, my Stored procedure is executed in less than 1 minute.

call generatedate();
Query OK, 0 rows affected, 41827 warnings (50.91 sec)

i tested this 3 times, every time it took less than 1 minute.

i am ready to upgrade and aslo wating for the next release.

Thank you,

Raja k