Question about SQL performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have some questions about the performance of certain types of SQL statements.

What sort of speed increase is there usually with binding parameters (and thus preparing statements) v. straight sql with interpolated variables? Will Postgresql realize that the following queries are effectively the same (and thus re-use the query plan) or will it think they are different?

	SELECT * FROM mytable WHERE item = 5;
	SELECT * FROM mytable WHERE item = 10;

Obviously to me or you they could use the same plan. From what I understand (correct me if I'm wrong), if you use parameter binding - like "SELECT * FROM mytable WHERE item = ?" - Postgresql will know that the queries can re-use the query plan, but I don't know if the system will recognize this with above situation.

Also, what's the difference between prepared statements (using PREPARE and EXECUTE) and regular functions (CREATE FUNCTION)? How do they impact performance? From what I understand there is no exact parallel to stored procedures (as in MS SQL or oracle, that are completely precompiled) in Postgresql. At the same time, the documentation (and other sites as well, probably because they don't know what they're talking about when it comes to databases) is vague because PL/pgSQL is often said to be able to write stored procedures but nowhere does it say that PL/pgSQL programs are precompiled.

Thanks
Jason


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux