Re: Use Placeholders. For SECURITY and (sometimes) for PERFORMANCE

[pg]

A very good point, and something that everyone serious about database performance should know.

A prepared statement is the same as a piece of compiled code. At the time you prepare it, it gets compiled and cached. This is much faster than execute a SQL statement on fly.

Have said this, I would like to mention another trick related to "compiled" stuffs in database. Many times, you can improve performance of queries against big tables, by creating a view on top of the table. By doing this, the view get "compiled". Later instead of querying the table, you just query the view.

It sounds like nothing, and only difference is that you moved some of you where clauses from your query statement to the create view statement. But actually there is a big difference, as at the time you create the view, your where clauses get "compiled" as part of the create view statement.

I recently had an application's execution time cut 96%, by creating view, along with other tunings.

Database (or poor database design and tuning) is one of the major performance bottle necks you see from time to time nowadays.