Roderick Rocamora wrote:
> 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.
Where did you read that stored procedures would be faster than embedded SQL? Currently, the implementation of stored procedures will indeed lead to worse performance than embedded SQl because no query execution plan caching is implemented, meaning the stored procedure must be re-evaluated each time it is executed. Though this may change in the future, performance is not currently an advantage of stored procedures. Currently the main advantages are code encapsulation, blackboxing of business logic into the database tier, organization of logic into SQL blocks, and possibly permission organization.
> The stored proc below was too slow ...
>
> 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
Your code is actually suffering doubly. In the first case, you are suffering the penalty of stored proecures having to re-evaluate the SQL each time. And secondly, you are suffering the performance penalty of using prepared statements and dynamic SQL in your stored procedures, which forces an additional evaluation at runtime. Double whammy.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com