PenWin wrote:
I wonder: Is it worthwhile to use prepared statements in cases where multiple applications use essentially the same queries, but each application only once? For example, just about every application asks user for login and password and validates it against the database. Obviously the same query will be called many times (if there are many users), but probably only once per instance of the application. Will I see any noticeable benefit from using prepared statements:
- LOGINQUERY = prepare "SELECT * FROM users WHERE username=? AND password=?" - execute LOGINQUERY, "pepak", "mypassword"
rather than the usual:
- query "SELECT * FROM users WHERE username=? AND password=?", "pepak", "mypassword"
I guess if I my application were inserting a whole lot of values, it would make sense to prepare the statement first and then execute it many times, saving the time on parsing the query. But will I see the same benefit if the inserts are distributed between a large number of connections? I suppose the question really boils down to "if a connection 1 prepares statement S in M seconds and connection 2 prepares the same statement S in N seconds, will N be significantly smaller than M or will they be equal?
Vlad Khorsun answers:
Firebird doesn't cache statements issued by applications and moreover doesn't share prepared statement between connections. But there is an exception: system queries, used by the engine to loadupdate metadata, stored procedures and triggers. All this kind of statements are cached and shared between all connections. So, if you really need to save time of parsing of some often executed query(s) - use stored procedure for this purpose.