Prepared statements tend to be faster because the query planner only have to plan them once (as both Patricks already mentioned).
Like I explained in my talk at ServoyWorld the planner uses meta-data about your data to see if you are looking for a common value or a more unique value (and other characteristics).
So using prepared statements the planner doesn’t have all the info on what you are searching for so you get a more generic and could be a less optimal plan.
Another thing prepared statements also give is the benefit of secure inputs. Just concatenating (possible) arbitrary values into your SQL might open you up for SQL injection. Of course all this depends on your use-case. And you could sanitize your inputs yourself before concatenation but that will take up some processing time as well.
To see what is really going on you might want to run some EXPLAINs on these queries.
You could run your SQL in a query editor and check what the query plan is for each value (and the query times) using the following syntax:
Using plain SQL:
EXPLAIN
SELECT glb_employee_working_time_id
FROM glb_employee_working_time
WHERE start_date <= '<yourdateval1>' AND end_date >= '<yourdateval2>' AND owner_id='<yourownerid>'
For a prepared statement you need to do more steps. First you need to create the prepared statement like so:
PREPARE myplan (timestamp, timestamp, character varying) AS
SELECT glb_employee_working_time_id
FROM glb_employee_working_time
WHERE start_date <= $1 AND end_date >= $2 AND owner_id=$3;
So now we have the prepared statement with the name ‘myplan’ we can explain this using passed values like so:
EXPLAIN EXECUTE myplan ('<yourdateval1>','<yourdateval2>','<yourownerid>');
EXPLAIN EXECUTE myplan ('<yourdateval3>','<yourdateval4>','<yourownerid>');
-- etc.
Afterwards you may want to remove the prepared statement from memory like so:
DEALLOCATE myplan
(for more info on using prepared statements in your query editor see here)
Hope this helps.
One side-note about your 8.4.4. install. I hope this is not a production environment and just the Developer bundled Pg install.
First of all PostgreSQL 8.4 is EOL since July 2014 and the latest minor update was 8.4.22 (!) It’s very much recommended to keep your version up to date with the latest minor version because of security- and/or bug fixes.
See this link for the PostgreSQL version policy.
I know Servoy bundles PostgreSQL with Developer but doesn’t provide minor updates for it. It’s one of the reasons to actually run the community distribution instead. The installer allows you to do this using the EDB installer. Enterprise DB (EDB) has indeed a commercial Postgres product but they also employ some community members that create the community installers. EDB hosts these as well.
Updating this distribution is just a matter of running the installer and seconds later you have your PostgreSQL instance running the latest minor version.
Also be aware that Servoy will not start or shutdown this version since it will be running as a service.
I hope this clears things up.
