There are two main reasons why you should prepare your SQL:
This is a very important part you should remember. Auto-parameterization feature, which is available in CUBRID, does NOT protect you from SQL injections. Even if all the queries get parameterized automatically, it happens on the server side after the query is already populated with values in your application long before it is processed by the Broker. Therefore, to avoid SQL injections, you must prepare your queries yourself.
If you have a query which is executed multiple times consequently in your application code, you are highly recommended to prepare your query once, then bind values and execute each query. There is big performance implication behind this. To understand this better, I will give you the following example.
Assume you have this query SELECT * FROM mytable WHERE id = 10 which you plan to execute using cubrid_execute() in your application several times consequently. This may be a part of your cron task or something like that.
Now asume you first cubrid_prepare() the query, then cubrid_bind() values and finally cubrid_execute().
Thus, when you prepare the query in advance and execute the query multiple times consequently, you save much time, CPU and memory resources required to parse the SQL statement and read from the cache.
I have created two small PHP programs to visually illustrate the performance implications of preparing the query. The first program directly executes SELECT * FROM zip_codes WHERE id = 50 in a loop of 5,000 without preparing it. And I have rerun this program 10 times after cold restart of CUBRID server.
The second program, first, prepares SELECT * FROM zip_codes WHERE id = ?, then in a loop of 5,000 it binds the value and calls cubrid_execute(). Also I have rerun the program 10 times after restarting CUBRID. Since in CUBRID prepared queries can be executed in synchronous and asynchronous modes, I have run the program in both modes.
So, below is the performance test results. What I would like to note here is that you should not look at the raw values displayed along the y-axis because the programs were executed in a low-end virtual machine environment. But what is important is the ratio between preparing the queries and not.
As you can see if queries, which are supposed to run multiple times, are prepared in advance, the execution can be 200% faster in sync_mode, or 60% in async_mode. Therefore, it is highly recommended to prepare queries where necessary.
You should also know that synchronous execution of SELECT quieres is not always faster than asynchronous execution. If you expect large result sets after each query execution, sync_mode can consume much memory and become slower, while in async_mode large results can be consumed right away after each query execution, thus may be faster. You should experiment this on your application and decide which one is better for your case.