Question 345170 in Mysql Question posted in
The official MySQL documentation can be found here.

Firebird's 'FOR SELECT … INTO … DO' equivalent in MySQL

I need to migrate Firebird database to MySQL, but I am stuck at the stored procedures more precisely I can’t find a solution for firebird’s FOR SELECT in MySQL when I need to iterate through a result set.

For simplicity lets say I have this code in Firebird:

create or alter procedure get_services(
    companyID: integer
returns (
     personid integer, 
     personname varchar(100), 
     companyname varchar(50), 
     serviceID integer,
     servicetimestamp timestamp, 
     description varchar(100)
as

    for select distinct a.personid, a.personname, a.serviceID, b.companyname, 
            from persons a 
            join companies b on (a.companyid = b.companyid)
           where b.companyId = :companyid into :personid, :personname, :companyname, :serviceID
      do begin
            select first 1 servicetimestamp, description 
              from services
             where serviceId = :serviceId
             order by servicetimestamp
           into :servicetimestamp, :description;
        suspend;
      end

One solution I have found on internet for MySQL was through use of a cursor fetch with repeat loop and a temporary table in which I have inserted the results but with a cost of performance.

MySQL code:

BEGIN 

DECLARE bDone INT;
DECLARE personid INT; 
DECLARE personname VARCHAR(100); 
DECLARE companyname VARCHAR(50); 
DECLARE serviceID INT;
DECLARE servicetimestamp TIMESTAMP, 
DECLARE description VARCHAR(100)

DECLARE curs
CURSOR FOR select distinct a.personid, a.personname, a.serviceID, b.companyname, 
                from persons a 
                join companies b on (a.companyid = b.companyid)
               where b.companyId = companyid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

DROP TEMPORARY TABLE IF EXISTS tblTemp;
CREATE TEMPORARY TABLE IF NOT EXISTS tblTemp (
    personid INT, 
    personname VARCHAR(100), 
    companyname VARCHAR(50), 
    serviceID INTEGER,
    servicetimestamp TIMESTAMP, 
    description VARCHAR(100)
  );  

OPEN curs;

SET bDone = 0;

REPEAT FETCH curs INTO personid,
                       personname,
                       companyname,
                       serviceID;
                       
SELECT servicetimestamp, description 
FROM services
WHERE serviceId = serviceId
ORDER by servicetimestamp
LIMIT 1                       
INTO servicetimestamp, description;

INSERT INTO tblTemp VALUES(personid, personname, companyname, serviceID, servicetimestamp, description);

UNTIL bDone END REPEAT;

CLOSE curs;

SELECT * FROM tblTemp;

END

The procedure in MySQL is 10 times slower compared with the same in Firebird and I have much more complicated procedures with nested loops and inserts that will take forever in MySQL using this method.

This simple procedure and some other selects I can solve with queries and sub queries, but as I have said there is much more complicated code and select query does not help.

Is something similar in MySQL as in Firebird ‘FOR SELECT’ that does not impact performance as using cursor?

Tags:

2

Answers


  1. 0

    This is how I would write the query if it had to be in a MySQL procedure:

    CREATE PROCEDURE get_services(IN in_companyid INT)
READS SQL DATA
BEGIN 
  WITH cte1 AS (
    SELECT DISTINCT a.personid, a.personname, a.serviceID, b.companyname
    FROM persons AS a
    JOIN companies AS b USING (companyid)
    WHERE b.companyid = in_companyid
  ),
  cte2 AS (
    SELECT cte1.*, s.servicetimestamp, s.description,
      ROW_NUMBER() OVER (ORDER BY s.servicetimestamp) AS rownum
    FROM cte1
    JOIN services AS s USING (serviceID)
  )
  SELECT * FROM cte2 WHERE rownum = 1;
END

    No cursor, no temp table (except for implicit temp tables created by the CTE’s or the DISTINCT).

    In fact, almost every case I see of developers using cursors in MySQL procedures is unnecessary and hurts performance. I use cursors only in cases where there are more complex conditional things to do in the loop based on the results of the cursor. Never use a cursor simply to prepare the result set.

    Remember to name your procedure input parameter something distinct from any column names in the tables. MySQL gets confused between companyid and companyid, because it cannot know which one you mean to be the column and which one you mean to be the procedure parameter. Column names are case-independent, so companyId and companyid still are seen by MySQL as the same identifier. The ambiguity may be resolved since you used a qualified column name like b.companyid, but if you forget to do this, you end up with a tautology companyid = companyid which compares the column to itself.

    P.S.: I don’t as a rule like to use MySQL stored procedures at all. They have no compiler, no debugger, no support for packages, no support for atomic deployment, and the language hinders productivity.

    Login or Signup to reply.
  2. 0

    Or simply

    INSERT INTO tbl VALUES(personid, personname, companyname,
                       serviceID, servicetimestamp, description)
     SELECT DISTINCT a.personid, a.personname, a.serviceID, b.companyname, 
            FROM persons a 
            JOIN companies b ON a.companyid = b.companyid
            WHERE b.companyId = companyid;

    ?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search