Search code examples
mysqlstored-proceduresquery-performance

MySQL - Fast query but slow Stored Procedures

I know this question has been discussed quite a lot here. But I have a particular case when I need to pass a list of parameters (comma - separated) which prevents me to have a local variable declared and used for input parameter.

As pointed out in the above discussion, it is suggested to declare a local variable and assign the parameters to this variable. However, what should I do in case my parameter is of type Text and can be comma - separated list?

For example - 

CREATE DEFINER=`Admin`@`%` PROCEDURE `MyReport`(
    p_myparameter_HK Text
)
BEGIN
SELECT
*
FROM MyTable
WHERE 
(find_in_set(MyTable.column_HK, p_myparameter_HK) <> 0 OR MyTable.column_HK IS NULL)
;

END

Performance:

Query

If I just run the query - 300 ms

Stored Procedure

CALL MyReport('0000_abcd_fake_000')

This procedure keeps running endlessly.

My question is, how can I disable parameter sniffling and use local variable instead of find_in_set to match the query performance.

Solution

  • The times that I have needed to pass an arbitrary list of things to a Stored Procedure, I did it this way:

    • CREATE (or already have) a TABLE for passing the info in. Both the caller and the Procedure know the name of the procedure. (Or it could be passed in, but adds some messy "prepare-executes".)
    • Do a bulk INSERT into that table. (INSERT INTO tbl (a,b) VALUES (...), (..), ...;)
    • Perform JOINs or whatever to use the table efficiently.

    In my case, the extra effort was worth it.