Cara menggunakan mysql stored procedure performance

You shouldn't be seeing a perceptible performance difference from preparing a statement compared to having one hard-coded into your stored procedure. You also should see no difference at all from the fact that the procedures are stored in a different database from your data.

From the start, though you're presenting us with two different unknowns -- different schema and dynamic SQL. The best way to troubleshoot this is to sectionalize the problem down to the individual variables... by putting a procedure with static (not dynamic) in a different schema from the data (shouldn't matter) or by putting a procedure with a dynamic query in the same schema as the data (also shouldn't matter).

But I suspect the problem is related to neither factor. I think it's the dynamic queries you're building. You may actually be getting wrong results, in addition to your queries taking longer to run.

You appear to have startdate and enddate concatenated right into the text of your dynamic sql. That cannot possibly be valid, since prepared statements do not share procedure variable scope with stored procedures, and concatenation like this puts the names of the variables, not their contents, in to the string... in fact, that dynamic sql should throw an error when you run it -- however, there's an insidious exception, here: it would appear to work if the view you're querying also happens to have columns called startdate and enddate... but the query isn't giving you the correct results, because it thinks you're referring to columns in the queried table in your WHERE clause -- not the procedure variables, making your where clause essentially not eliminate any rows based on those dates.

You could replace startdate with @startdate and enddate with

FROM ',
db_name, '.view 
WHERE 

FROM ',
db_name, '.view 
WHERE 

SET @startdate = startdate;  
SET @enddate = enddate;

Note that this doesn't put the contents of the variables into your query, either -- concatenating the contents of variables from outside your procedure into dynamic sql almost always is a bad (insecure) idea -- but this doesn't do that. It puts the variable names into the statement, which is safe.

I think this is the solution to your problem, if the question accurately represents the dynamic sql you're building.

However, I have a further recommendation, in the interest of security and good practice. I mentioned never concatenating external variables into dynamic sql... but you can easily sanitize the variable

FROM ',
db_name, '.view 
WHERE 

Replace this:

FROM ',
db_name, '.view 
WHERE 

with this:

FROM `',
(SELECT schema_name FROM information_schema.schemata 
  WHERE catalog_name = 'def' 
    AND schema_name = db_name), '`.view 
WHERE 

This scalar subquery will return only two possible things: it will always return either the same value passed in as

FROM ',
db_name, '.view 
WHERE 

FROM ',
db_name, '.view 
WHERE 

Dalam membuat aplikasi database MySQL 5, kita bisa meletakkan fungsi-fungsi yang merupakan bisnis prosesnya dengan 2 pilihan

1. Meletakkan fungsi-fungsi bisnis di dalam bahasa programming yang digunakan
   Cara ini mudah diimplementasikan. Untuk aplikasi yang sederhana lebih baik memakai metode ini.
   Untuk aplikasi yang kompleks, cara ini memiliki kelemahan:
   a. Jika processing banyak terjadi, performance akan turun
   b. Jika ada pengembangan lain dan memakai bahasa pemrograman lain, maka fungsi bisnis harus ditulis ulang
   c. Harus jeli mengenai konsistansi update suatu table yang mempunyai hubungan dengan tabel lain
2. Meletakkan fungsi-fungsi bisnis di dalam database sebagai store procedure, trigger, view dan object database yang lain
   Peletakan fungsi bisnis di dalam store procedure dan trigger mempunyai keuntungan
   a. Performance cepat
   b. Konsistensi data table akan terjaga
   c. Jika ada pengembangan lain yang memakai bahasa pemrograman berbeda, tidak perlu menulis ulang fungsi-fungsi bisnis tersebut

Tabel Sales Order
Sebagai contoh kita akan membuat tabel sales order dan diisi dengan sebuah data:

CREATE TABLE sales_order(
customer_name VARCHAR( 50 ) NOT NULL ,
total_price DECIMAL( 9, 0 ) NOT NULL DEFAULT ‘0’,
total_hpp DECIMAL( 9, 0 ) NOT NULL DEFAULT ‘0’,
profit DECIMAL( 9, 0 ) NOT NULL DEFAULT ‘0’,
so_id INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (so_id)
) ENGINE = InnoDB;
INSERT INTO sales_order (customer_name,so_id) VALUES (‘Serba Prima’, 1);

Hasilnya bisa dilihat di phpmyadmin :

Tabel Sales Item
Kemudian kita akan membuat tabel sales_item diisi dengan 2 data:

CREATE TABLE sales_item(
so_id INT NOT NULL ,
item_name VARCHAR( 100 ) NOT NULL ,
item_price DECIMAL( 8, 0 ) NOT NULL ,
hpp DECIMAL( 8, 0 ) NOT NULL ,
item_id INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (item_id)
) ENGINE = InnoDB;

INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, ‘Keyboard’, 100000,85000);
INSERT INTO sales_item(so_id,item_name,item_price,hpp) VALUES (1, ‘Mouse’, 50000,40000);

Hasilnya :

Store Procedure Resume Sales Order
Kemudian Store Procedurenya
CREATE PROCEDURE resume_sales_order (IN p_so_id INT)
BEGIN
    DECLARE v_total_price DECIMAL;
    DECLARE v_total_hpp DECIMAL;
    SELECT SUM(item_price),SUM(hpp) INTO v_total_price,v_total_hpp FROM sales_item WHERE so_id=p_so_id;
    UPDATE sales_order SET total_price=v_total_price,total_hpp=v_total_hpp,profit=v_total_price-v_total_hpp WHERE so_id=p_so_id;
END
//

Untuk membuatnya pastikan delimiternya //

Menjalankan Store Procedure
Untuk menjalankan store procedurnya dengan menjalankan
CALL resume_sales_order(1)
dan isi tabel sales_order akan menjadi :

Langkah berikutnya adalah membuat Trigger di MySQL 5 .

Kunjungi www.proweb.co.id untuk menambah wawasan anda.

Store Procedure di MySQL 5