Kim schreef:
> On Mar 13, 3:38 pm, Pavel Lepin <p.le...@ctncorp.com> wrote:
>> Kim <kims...@gmail.com> wrote in
>> <d2e7d459-4e5d-466f-aa6c-08cd9879f...@s12g2000prg.googlegroups.com>:
>>
>>
>>
>>> On Mar 12, 3:05 pm, Luuk <L...@invalid.lan> wrote:
>>>> Kim schreef:
>>>>> Im using MySQL 5.1 to create a stored procedure, but
>>>>> without luck.
>>>> try this:
>>>> SET @a=1;
>>>> PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
>>>> EXECUTE STMT USING @a;
>>> DELIMITER /
>>> CREATE PROCEDURE _sp()
>>> BEGIN
>>> DROP TABLE IF EXISTS `tmp_tbl`;
>>> CREATE TABLE `tmp_tbl`(`colAA` bigint(20) unsigned NOT
>>> NULL);
>>> SET @tip_a = 0;
>>> SET @tip_b = 0;
>>> SET @tip_next = 0;
>>> SET @tip_last = 0;
>>> SET @tip_value = 0;
>>> SET @tip_found = NULL;
>>> SELECT count(*) INTO @tip_last FROM `tbl`;
>>> PREPARE stat1 FROM 'SELECT colA,colB INTO @tip_a,@tip_b
>>> FROM `tbl`
>>> LIMIT ?,1';
>>> PREPARE stat2 FROM 'SELECT * INTO @tip_found FROM
>>> `tmp_tbl` WHERE
>>> colAA=?';
>>> WHILE @tip_next < @tip_last DO
>>> EXECUTE stat1 USING @tip_next;
>>> SET @tip_value = @tip_a;
>>> WHILE @tip_value <= @tip_b DO
>>> EXECUTE stat2 USING @tip_value;
>>> IF (@tip_found IS NULL) THEN
>>> INSERT INTO `tmp_tbl` VALUES(@tip_value);
>>> END IF
>>> SET @tip_value = @tip_value + 1; <------
>>> END WHILE
>>> SET @tip_next = @tip_next + 1;
>>> END WHILE
>>> DEALLOCATE stat2;
>>> DEALLOCATE stat1;
>>> SELECT count(*) FROM `tmp_tbl`;
>>> END /
>>> DELIMITER ;
>>> Notice that there is two tables.
>>> I get an error at the line, which I have marked with an
>>> arrow (<----). Error 1064 (42000) simply states its a
>>> syntax error, yet I see nothing wrong.
>> What about statement delimiters after your END IFs and END
>> WHILEs?
>>
>> --
>> In Soviet Russia, XML documents transform *you*.
>
>> What about statement delimiters after your END IFs and END
>> WHILEs?
> Thanks, that did the trick.
>
> Work that it works, performance seems to be an issue. As the stored
> procedure can only process about 10k inserts per minute which gives
> about a maximum time-to-complete of 300 days!!

this piece:
EXECUTE stat2 USING @tip_value;
IF (@tip_found IS NULL) THEN
INSERT INTO `tmp_tbl` VALUES(@tip_value);
END IF

could be done like this:
INSERT IGNORE INTO `tmp_tbl` VALUES(@tip_value);

which might make things quicker....
but i doubt if it will be quick enough... ;-)

so, maybe you should start all over again, and thin of a complete other
way to solve your problem?


--
Luuk