Optimization Suggestion

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
glavian
Posts: 2
Joined: Thu 01 Sep 2011 00:39

Optimization Suggestion

Post by glavian » Thu 01 Sep 2011 00:47

So I am sending 300 Update commands to the database. If I use the ProtocolVersion.Ver20 I'm able to send all the commands as 1 string. The command processes in about 50ms vs 300ms if I use ProtocolVersion.Ver30 sending individual PgsqlCommand objects. I'm not concerned with SQL Injection so is using the ProtocolVersion.20 the best way for me to get the best perfomance or does anyone have any other suggestions on how I can improve my performance.

Thanks,
keith

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 07 Sep 2011 15:26

glavian wrote:If I use the ProtocolVersion.Ver20 I'm able to send all the commands as 1 string.
Please use the "UnpreparedExecute=true;" connection string parameter with ProtocolVersion.Ver30 to send all commands with one cmd.ExecuteNonQuery(). For more information, refer to http://www.devart.com/forums/viewtopic.php?t=14354.
glavian wrote:does anyone have any other suggestions on how I can improve my performance.
The performance of Protocol 3 with "UnpreparedExecute=true;" is approximately the same as the performance of Protocol 2.
Protocol 2 (or Protocol 3 with "UnpreparedExecute=true;") is better to use if you want to execute many simple commands. But to work with Blob is better (faster) using Protocol 3 with "UnpreparedExecute=false;" (default).

glavian
Posts: 2
Joined: Thu 01 Sep 2011 00:39

Post by glavian » Wed 07 Sep 2011 15:50

I found that writing a stored procedure and sending the parameter information as an 2 seperate arrays of 300 ints works the fastest. Thanks for the feedback though I understand the Protocol stuff alot better now.

Post Reply