How does a prepared statement work? What does it mean to say my query is compiled?

Question

I'm not sure I understand a prepared statement as it relates to speed or efficiency. I read that a prepared statement is compiled on the database server and can be use over and over.

But, say I have a webpage. It does one query. Next person pulls up the page, same query, just different parameters for that user.

On the second hit to the webpage, is the prepared statement looking on the dbms to see if that particular query already exists on the database server? I don't understand what it means to say the statement is compiled and can be run "over and over." It doesn't prepare it each run of the page?

Also, this is not about sql injection. That's one part I do understand as it relates to compiling.

Thanks.

edit: I have searched, but I cannot find the answer. I am only led to this question.

edit: Based on the comments below, given my scenario, I cannot see any efficiency benefit. Security yes. The whole compiled thing. No. Does anyone have a scenario they can give as an example that does meet the efficiency question?

Answer 1

The query plan that gets created during the first execution may be cached, either by the database (potentially providing the benefit to all applications connecting to the database, running the same query) or by the database driver (providing the benefit to the application, as long as it isn't restarted). A "soft parse" will still be made on each execution but that is normally much cheaper.

Note that your mileage may vary, depending on database type, driver etc. The statement cache is also normally limited, which means that only a certain number of statements will be kept in the cache.

Ask Tom provides some answers regarding the difference between soft and hard parses.