Optimization Suggestion

Optimization Suggestion

Postby 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
glavian
 
Posts: 2
Joined: Thu 01 Sep 2011 00:39

Postby 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).
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Postby 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.
glavian
 
Posts: 2
Joined: Thu 01 Sep 2011 00:39


Return to dotConnect for PostgreSQL