[#1000515] Prepare() makes command very slow

Date:
2006-01-03 12:17

Priority:
3

State:
Open

Submitted by:
Nobody

Assigned to:
Nobody (None)

Npgsql Version:
None

Category:

Group:

Resolution:
None

Summary:
Prepare() makes command very slow

Detailed description
Server: Windows 2003, Postgres 8.1.0 Client: Windows XP, Npgsql 1.0 beta 1 (version 0.7 had the same issue) Look at the following function: ---------------------------------------------------------------------------- public void InsertData(string data, bool prepareFirst) { IDbCommand command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "insert into test(x) values(:data)"; IDbDataParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.DbType = DbType.String; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); // HERE IT IS ///////////////////// if(prepareFirst) command.Prepare(); /////////////////////////////////// for (int i = 0; i < 100; i++) { dataParameter.Value = data; command.ExecuteScalar(); } } ----------------------------------------------------------------------------- Having simple table on my postgres: --------------------------------------------------- CREATE TABLE test ( x text ); --------------------------------------------------- and calling function InsertData("X", false) - in my environment about 90 ms InsertData("X", false) - in my environment about 65000 ms !!!!!!!!!!!! The same situation when calling stored procedures, etc...

Followup

Message
Date: 2008-12-18 00:12 Sender: Jon Hanna Note that prepared commands are often less efficient with some data than unprepared at the level of PostgreSQL itself. Prepared commands cache a query plan, and in not having to re-do that there can be an efficiency gain. However, the query plan cached may not be as efficient as one postgres would produce for a particular set of data. The most obvious cases are where a partial index can be used for some but not all inputs, but there are plenty of other cases. Npgsql is not implicated in such cases.
Date: 2006-01-05 18:26 Sender: Francisco Figueiredo jr. Hi Wojciech. We have to optimize even more prepared statements calls :) I will try some changes and let you know if there is any improvement in this area. Sorry about that :( Thanks for your feedback about that!
Date: 2006-01-05 14:50 Sender: Wojciech Strzalka Now my test results are: InsertData("X", false) - 90 ms InsertData("X", true) - 250 ms Looks like the bug is fixed but still prepared command is much slower then one which is not prepared. Isn't it strange?
Date: 2006-01-05 13:31 Sender: Francisco Figueiredo jr. Hi Wojciech. This is already fixed in cvs. Please, give it a try and let me know if it worked for you.

No Changes Have Been Made to This Item