Stored Proc SLOWER than embedded sql

[Roderick Rocamora]

Hi to anyone bothered by my comment.


Stored proc is expected to be faster in retrieving data from database server
compared to embedded SQL. It doesn't seem to be true to me.

The stored proc below was too slow compared to embedded SQL on EXE file when
querying a table with about 500,000 records when it returned a recordset. Can anyone prove me wrong?

I plan to move back and embedded it on EXE if no one can help. Here's the stored
proc:

CREATE PROCEDURE `sp_collection` (
IN sID VARCHAR(8)
,IN sTbl VARCHAR(10)
,IN sFrom VARCHAR(22)
)
BEGIN

SET @s='SELECT CONCAT(LEFT(c.period,4),"-",right(c.period,2)) AS "Post Period"';
SET @s=CONCAT(@s,', c.coldate AS Date,FORMAT(c.amt,2) AS Amt,FORMAT(c.balance,2) AS Balance');
SET @s=CONCAT(@s,',c.idtrans AS Type,c.ref AS Reference,t.transact AS Transaction FROM tbl_col');
SET @s=CONCAT(@s,sTbl,' c,tbl_1transcod t WHERE ');
SET @s=CONCAT(@s,' c.idmploye=', sID ,' AND c.idtrans=t.idtrans');
IF LENGTH(sFROM)>0 THEN
SET @s=CONCAT(@s,sFrom);
END IF;
SET @s=CONCAT(@s,' ORDER BY pk ASC');
#SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
END