Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
proHappyJim

Prepared Statements Vs Stored Procedures Vs Stored Functions

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.  

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...