Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

If we have a prepared statement like:

SELECT my_func($1::text, $2::int)

Is there is any gain in speed if I prepare a statement with this call and do the call via the prepared statement.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Let me quote the docs here:

Prepared statements have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, for example, if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

Emphasize is mine. I think it clearly satets in which conditions PREPARE can have benefits.

Still, all languagues currently provide a native way to prepare statements (like PHP), so the overall machinery is executed for you behind the scenes.

To make it short:

  • if it is a one-timer from the client, execute directly;
  • if it comes from the application and assumes user input, use your platform and it's functionality to prepare for security reasons;
  • if statement is executed many times within a session, use any means (either PREPARE or platform's functionality) to prepare for performance reasons.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.12.24.2140\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["t comes from the application and assumes user input, use your platform and it's functionality to prepare for security reasons;\n  \u2022 if statement is executed many times within a session, use any means (either PREPARE or platform's functionality) to prepare for performance reasons.\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}