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 I’m currently learning).  It took 419 milliseconds, or MySQL took 677 times











  [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:

  • To hide the implementation from the database user, so that the implementation can change but the interface remains stable.
  • To simplify the DBAs day to day tasks of maintaining the database.
  • To speed up complex queries.
  • While MySQL doesn’t excel at point 3, it can be used in the other two cases. I’ve used Sybase for quite a while and many

      [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 SIGNAL and RESIGNAL is in the roadmap.

    What does that mean? Should we wait two or three more years to





      [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
    DELIMITER |
    DROP PROCEDURE IF EXISTS summarizeTrendicsTest|
    CREATE PROCEDURE summarizeTrendicsTest(dateTime DATETIME)
    BEGIN
    SELECT @endDateTime := DATE_ADD(dateTime, INTERVAL 1 HOUR);
    SELECT * FROM












      [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 ~/.bashrc add the following...
      export EDITOR=vim VISUAl=vim
      
    • Create / Edit ~/.vimrc file, and add the following...
      set noai
      set nonu
      
    • Log into the MySQL CLI, and set the


      [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 ~/.bashrc add the following...
      export EDITOR=vim VISUAl=vim
      
    • Create / Edit ~/.vimrc file, and add the following...
      set noai
      set nonu
      
    • Log into the MySQL CLI, and set the


      [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 Triggers. For the client-side a bit of convenience functionality for query rewriting would be needed.

    Sounds like an interesting project that I would like to pursue when I find the time.









      [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.