How do I cache dynamic query from a stored procedure?
Right now I have created my stored procedure like this :
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @sqlQuery = CONCAT("SELECT Name From Users WHERE UserID > ", UserID, " AND UserID IN ( SELECT UserID FROM OtherTable WHERE UserID = ", UserID, " ) Order by Name")
PREPARE stmt FROM @sqlQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
But this kind of query does not cached. So, every time it gets more time to execute/process query.
Now I have tried some other method like this:
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @UserID = UserID;
SET @sqlQuery = "SELECT Name From Users WHERE UserID > ? AND UserID IN ( SELECT UserID FROM OtherTable WHERE UserID = ? ) Order by Name";
PREPARE stmt FROM @sqlQuery;
EXECUTE stmt @UserID, @UserID; -- here i passed same variable twice.
DEALLOCATE PREPARE stmt;
END;
In the above case I have to pass same variable (@UserID) twice, because it is used 2 times in my query. but this job is very hectic in long or complex query. so, how do I avoid this?
One another method I tried as follows:
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @UserID = UserID;
SET @sqlQuery = "SELECT Name From Users WHERE UserID > @UserID AND UserID IN ( SELECT UserID FROM OtherTable WHERE UserID = @UserID ) Order by Name";
PREPARE stmt FROM @sqlQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
But above query again does not cached. so, execution time is very long. And this type of variable declared as session global variable has may be conflict with another stored procedure's variable. Because I have call stored procedure within this stored procedure and variable name should be same in another stored procedure.
So, let me know what is the best solution for the same.
Thanks.