Mysql – Procedure MySQL cursor taking a long time to execute

cursorsMySQLmysql-5.7optimizationstored-procedures

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below

DECLARE cs CURSOR FOR
           (SELECT  a.mag_id FROM A a  WHERE
                a.creation_date BETWEEN (v_fromdate) AND (v_todate) 
                AND a.type_id IN (SELECT type_id FROM key2 WHERE   sessionId=v_sessionId)
                AND a.mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                )
     UNION
                (SELECT  b.combo_mag_id FROM B b 
                WHERE
                b.creation_date BETWEEN (v_fromdate) AND (v_todate) 
                AND b.type_id IN (SELECT type_id FROM    key2 WHERE   sessionId=v_sessionId)
                AND b.combo_mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                );

DECLARE
CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_cur_time = now();
SET v_cur_query = 'cursor';
OPEN cs;
SELECT timestampdiff(SECOND,v_cur_time,now()) into v_diff;
SELECT CONCAT('Current Query: ', v_cur_query, ' Time taken: ', v_diff);

Both table A and B have millions of records.I have partitions created in both the tables with partition by range on creation_date.For 3 months of a date range the cursor takes almost 4 mins to execute.But when i tried running the same query in MySQL workbench editor,by setting the parameters for the same date range it took only 22 seconds. Can somebody tell me why it executes faster in an SQL editor, and since i have to use it in the stored procedure, is there any way to optimize it ??

Best Answer

IN ( SELECT ... ) is often poorly optimized. See if you can turn it into either a JOIN or EXISTS ( SELECT 1 ... )
Don't use a CURSOR if it is possible to write a JOIN or something to do the entire task rather than acting one row at a time.
PARTITIONing is unlikely to be beneficial; let's see the details. (Composite indexes often do the job adequately.)
Remove the ORDER BY in subqueries; it has no effect. If you need ordering, please elaborate; we should be able to find another way.
Are there parameters being fed in? Which?

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K

MySQL – How to Merge 3 Tables with Conditions

As first step I merge the two product tables. I need an full outer join on product1 and product2 tables. To obtain on outer join in mysql we can union a left join and a right join. Start with a left join. In the following query IFNULL(p1.key1, p2.key1) is useful to merge two key column coming from the two product tables and IFNULL(, 'NaN') is useful to obtain 'NaN' in the output.

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial =     p2.serial;

To obtain a full outer join I need to repeat the previous query with 'right join' and union the result with the 'left join'

select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

For convenience, I create a view with the previous query:

create or replace view p12 as
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
left join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial
union
select IFNULL(p1.key1, p2.key1) as key1, 
       IFNULL(p1.key2, p2.key2) as key2,
       IFNULL(p1.serial, p2.serial) as serial,
       IFNULL(p1.product_data, 'NaN') as product_data1, 
       IFNULL(p2.product_data, 'NaN') as product_data2
from       product1 p1
right join  product2 p2 on p1.key1 = p2.key1 and p1.key2 = p2.key2 and p1.serial = p2.serial;

To check we can try with:

mysql> select * from p12 where key1 = 1 and key2 =1 ;
+------+------+--------+---------------+---------------+
| key1 | key2 | serial | product_data1 | product_data2 |
+------+------+--------+---------------+---------------+
| 1    | 1    | 0      | 15.556        | 5.556         |
| 1    | 1    | 1      | 14.996        | 4.996         |
| 1    | 1    | 2      | 12.556        | NaN           |
| 1    | 1    | 3      | 15.669        | NaN           |
+------+------+--------+---------------+---------------+

So the he final query is:

select mi.key1, mi.key2, p12.serial, mi.info1, mi.info2, mi.date, p12.product_data1, p12.product_data2 
from main_info mi 
inner join p12 on mi.key1 = p12.key1 and mi.key2 = p12.key2
order by mi.key1, mi.key2, p12.serial;


+------+------+--------+-------+-------+----------+---------------+---------------+
| key1 | key2 | serial | info1 | info2 | date     | product_data1 | product_data2 |
+------+------+--------+-------+-------+----------+---------------+---------------+
| 1    | 1    | 0      | 15    | 90    | 20120501 | 15.556        | 5.556         |
| 1    | 1    | 1      | 15    | 90    | 20120501 | 14.996        | 4.996         |
| 1    | 1    | 2      | 15    | 90    | 20120501 | 12.556        | NaN           |
| 1    | 1    | 3      | 15    | 90    | 20120501 | 15.669        | NaN           |
| 1    | 2    | 0      | 14    | 92    | 20120601 | 12.556        | 2.556         |
| 1    | 2    | 1      | 14    | 92    | 20120601 | 13.335        | 3.335         |
| 1    | 2    | 2      | 14    | 92    | 20120601 | NaN           | 2.56          |
| 1    | 2    | 3      | 14    | 92    | 20120601 | NaN           | 3.556         |
| 1    | 3    | 1      | 15    | 82    | 20120801 | 12.225        | 2.225         |
| 1    | 3    | 2      | 15    | 82    | 20120801 | 13.556        | 3.556         |
| 1    | 3    | 3      | 15    | 82    | 20120801 | 14.556        | NaN           |
| 2    | 1    | 0      | 17    | 90    | 20130302 | 12.556        | NaN           |
| 2    | 1    | 1      | 17    | 90    | 20130302 | 13.553        | NaN           |
| 2    | 1    | 2      | 17    | 90    | 20130302 | 12.335        | NaN           |
| 2    | 2    | 0      | 16    | 88    | 20130601 | NaN           | 2.556         |
| 2    | 2    | 1      | 16    | 88    | 20130601 | NaN           | 3.553         |
+------+------+--------+-------+-------+----------+---------------+---------------+

Best Answer

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

MySQL – How to Merge 3 Tables with Conditions

Related Question