Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How do I cache dynamic query from store procedure? Right now I have created my store 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 store procedure's variable. because I have call store procedure within this store procedure and variable name should be same in another store procedure.

So, let me know what is the best solution for the same.

Thanks.

share|improve this question

2 Answers 2

First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.

If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are repeating all the steps again. Take a look on http://stackoverflow.com/questions/11605783/mysql-permanently-prepared-statements , there is an example on how to get benefits from prepared statements inside stored procedure (briefly, you do not deallocate statement at the end, instead of this, you check whether a variable associated with the statement is not null and call prepare only once. That expands lifetime of the prepared statement to lifetime of session effectively caching execution plan).

share|improve this answer
    
+1 for the Excellent StackOverflow link. The key is to prepare the statement once and reuse it with a different parameter over and over. –  RolandoMySQLDBA May 3 '13 at 15:58
    
I have tested whatever you suggest. but I am surprised, because very first time I am getting my query result in 0.079s. but after that it goes to very high. (about 0.250s). why so? –  Manish Sapkal May 4 '13 at 6:45
    
@Manish Sapkal : Since you didn't provide execution plan, I can only guess. The most likely culprit [in my opinion] is "parameter sniffing" - procedure compiles ones on first execution, and optimizer chooses the best (from it's opinion - not 100% accurate) plan that fits passed parameters... Without having all the details it's hard to answer your question. –  a1ex07 May 4 '13 at 19:34
    
thanks @a1ex07, What details you want. If you suggest, I can provide you that detail. –  Manish Sapkal May 6 '13 at 5:47
    
@ManishSapkal: Can you post the latest version of your procedure, ` OtherTable` and Users tables ddl, and execution plan for your select statement ? –  a1ex07 May 6 '13 at 14:32

Dynamic queries are all about being dynamic - so can't be cached...
What I'd do is indexing the tables based on the fields you'll use in your WHERE clause, and create Views from affected tables with ORDER BY clause (and make the dyn-query SELECT work from the Views instead of the tables of course).
I'd also recommend reading this guy here: API Prepared Statements in MySQL, as well as using the USING keyword in EXECUTE, like EXECUTE stmt USING @UserID, @UserID;

Hope this helps!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.