Not the answer you need?
Register and ask your own question!

Real Performance of Stored Procedure Testing

sql_ersql_er ContributorInactive User Role Beginner
Hello,

We are using MySQL 5.0.44. When I run a stored procedure first time, it might take over a second. If I immediately rerun it, it takes milliseconds. Each subsequent rerun is also super fast.

Why is this happening? Is there some caching?

I thought there is no stored procedure caching in our mysql version.

The reason I'm asking is because I am trying to optimize a stored procedure 'A'. So I created a modified version 'B'. I then randomly choose 1000 inputs, and run A on all of them and then run B on all of them. I wonder if B performs better because I just ran A on the same input and something got cached.

Can someone shed a light on what could cause 2nd run of same stored procedure on same input be much faster than first one?

And also, what is the best way to perform performance comparison of 2 different stored procedures?


Thanks a lot!

Comments

  • RyanLoweRyanLowe Entrant Inactive User Role Beginner
    Each connection has its own stored procedure execution plan path. So subsequent runs of a procedure using the same connection will be much faster. If your application is using connection pooling, you can see the benefits from this.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.