Prepared Statements Vs Stored Procedures Vs Stored Functions

[proHappyJim]

Hi Larry,

 

I am loving the book, as I work towards converting lots of Classic ASP to PHP 7.

 

I have in the past created hundreds of Stored Procedures in MSSQL. One thing that has bugged me in the past was that I found it hard to search for variables, etc., in the Stored Procedures when I changed something.

 

In addition to liking the fact that they will be in my (easily searchable) code, I like the security and performance perks of using Prepared Statements as I translate these Stored Procedures... but I am wondering:

 

Would Stored Procedures or Stored Functions be faster than Prepared Statements?

 

Again, thank you for making such an excellent book - it's really helping me get up to the next level in my coding abilities.

 

Thanks,

Jim

[Larry]

Thanks so much for the nice words, Jim! As for your question, I generally think stored procedures would be faster than prepared statements, but I don't have hard data on that. I don't believe the difference between the two is tremendously significant in terms of speed. Certainly prepared statements are a bit easier to create, use, and debug.  

[proHappyJim]

Great! That is how I was leaning. I am already seeing so much speed improvement going to PHP 7. I would rather have the convenience of prepared statements at this point.

 

Thanks again! I am enjoying getting more clear on jQuery at the moment. I am really grateful for great books like yours.

 

Jim

[proHappyJim]

Larry, I have a follow up question. As one forum comment puts it "To be worth the overhead, each prepared statement must be executed more than once (such as in a loop). If a query is executed only once, more overhead goes into preparation of the prepared statement... I would use a prepared statement, but only if I had to repeatedly make the same query multiple times in the same session."

 

Is this true? Should I just use regular db calls (inline SQL) unless the statement will be called multiple times (in a loop)?

 

Thanks so much!

[Larry]

Thanks, Jim! As for that statement, it's a bit complicated b/c it has to do with the database's caching mechanism and how resources are shared, etc., etc. What's always true is that prepared statements are reliably more performant when the query is run multiple times in the same script. If the query is only run once, it's hard to say for certain without doing some deep investigations.  

 

But prepared statements have a secondary benefit in that they are (perhaps only slightly) more secure than using straight SQL with escaping functions.