SCM

[#1000515] Prepare() makes command very slow

View Trackers | Bugs | Download .csv | Monitor

Date:
2006-01-03 12:17
Priority:
3
State:
Closed
Submitted by:
Nobody
Assigned to:
Nobody (None)
Npgsql Version:
None
Category:
None
Group:
None
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: 2013-09-30 05:38
Sender: Shay Rojansky

Ran this again and got 51ms for the regular query and 33ms for the prepared query.

This is clearly no longer a problem... Will add the speed test to our unit test suite.
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.

Attached Files:

Changes:

Field Old Value Date By
status_idOpen2013-09-30 05:38roji
close_dateNone2013-09-30 05:38roji
Powered By FusionForge