Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 37

Displaying posts with tag: Stored Procedures (reset)

Are MySQL stored procedures slow?
+0 Vote Up -0Vote Down

 

Yes, if compared to code in Java or C#. For example, this overly simple code took 284 seconds.

CREATE PROCEDURE CountTest()
begin
    declare counter int default 0;
    select now();
    repeat
        set counter = counter + 1;
    until counter > 120000000
    end repeat;
    select counter;
    select now();
end

Ignoring my off by one error, here is equivalent code in C# (the language …











  [Read more...]
MySQL’s stored procedure language could be so much more Useful
+0 Vote Up -0Vote Down

MySQL has a stored procedure language. People have told me how bad it is, how inefficient, how it had to be there to make MySQL appear more professional. Yes, it’s far from perfect, it’s not pre-compiled so not necessarily any faster than running single statements by hand. However, it works and you can do many useful things with it. The complaints however miss the point of stored procedure languages as used inside a database. They are generally used for 3 things:

  1. To hide the implementation from the database user, so that the implementation can change but the interface remains stable.
  2. To simplify the DBAs day to …
  [Read more...]
Community at work - SIGNAL
+1 Vote Up -0Vote Down





Long time ago, I saw a blog post by Jorge Bernal, with a simple implementation of SIGNAL for MySQL stored procedures. If you have ever tried to write MySQL stored procedures, you know how dearly missed is this feature.
I discussed this feature internally, and everyone told me "don't bother, we're going to implement SIGNAL in MySQL 6.1". And indeed, the full implementation for …

  [Read more...]
MySQL Stored Procedure Not Working as Expected Puzzler
+0 Vote Up -0Vote Down

I recently spent 3 hours pulling my hair out trying to fix a MySQL stored procedure that wasn’t working as expected. I’ve boiled the ultimate problem down to a simple example below. Can you spot the problem?

# First, let's create some sample data
CREATE TABLE TrendicsTest (
DateTime DATETIME NOT NULL,
Value INT NOT NULL,
UNIQUE KEY DateTime (DateTime)
);
INSERT INTO TrendicsTest VALUES ('2008-01-01 01:00', 1);
INSERT INTO TrendicsTest VALUES ('2008-01-01 02:00', 2);

# Next, lets' define a stored procedure to query the sample data








  [Read more...]
MySQL: Efficient Stored Procedure Editing
+0 Vote Up -0Vote Down

This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

  • In your …
  [Read more...]
MySQL: Efficient Stored Procedure Editing
+0 Vote Up -0Vote Down

This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and "Create Stored Routite..." This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

  • In your …
  [Read more...]
Materialized Views for MySQL
+0 Vote Up -0Vote Down

I just read about OLAP4ALL's Materialized Views for MySQL software that "offers the functionality of Materialized Views in MySQL that are not natively supported in the MySQL database.".

What nags me about this is the fact that it is "implemented as a separate Java program running on the server where MySQL is installed".

The server-side of this should be doable using Stored Procedures and/or …



  [Read more...]
10 Newer Entries Showing entries 31 to 37

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.