List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 21 2009 6:31am
Subject:Re: Low performance due high network latency - batching ?
View as plain text  
In the last episode (Oct 20), Bermejo, Rodrigo (GE Infra, Aviation) said:
> We are facing a preformance issue with a desktop application which
> connects remotly to a Mysql / DB ( ping times 300-800ms).  We do not have
> time to invest in modifications to create a 2-tier ...  Web application
> The initial plan was to implement a Client cache or a local DB (mysql
> slave or XML files).
>  
> After reviwing the code and the sql logs I figured out there are a lot of
> insert/updates and replaces within loops.  Sometimes there are more than
> 200 inserts statments coming for a loop.
>  
> What I did was to create a long string with all statments separated with
> semicolons (batching) and then just send them all in just one statment. 
> This reduced the major application use case time in 300%

A reasonable optimization.  Note that you can insert multiple rows in one
INSERT statement: http://dev.mysql.com/doc/refman/5.1/en/insert.html

  INSERT statements that use VALUES syntax can insert multiple rows. To do
  this, include multiple lists of column values, each enclosed within
  parentheses and separated by commas.  Example:

  INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

  
-- 
	Dan Nelson
	dnelson@stripped
Thread
Low performance due high network latency - batching ?Aviation) GE Infra21 Oct
  • Re: Low performance due high network latency - batching ?Johan De Meersman21 Oct
  • Re: Low performance due high network latency - batching ?Dan Nelson21 Oct