Low performance due high network latency - batching ?

------_=_NextPart_001_01CA51DA.5853547B
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hola List /.
=20
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).
=20
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.
=20
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%
=20
=20
I am planning to write some code that can keep all "modification"
statments in memory until a select is needed commands in memory and then the select>
=20
Any of you have some experience using this approach /?
Any drawback you can see /?
=20
Thanks for your comments.
=20
-r.

------_=_NextPart_001_01CA51DA.5853547B--

Re: Low performance due high network latency - batching ?

--0016e6d97735498d7e04766c12b2
Content-Type: text/plain; charset=ISO-8859-1

Well, it's not exactly a clean approach, but if those are the limits you
have to work within, so be it :-)

I suspect you could gain some more performance by using MySQL's multiple
select syntax. It's rougly like this:

insert into *table* (*field*, *field*, *field*) values (*value*, *value*, *
value*), (*value*, *value*, *value*), (*value*, *value*, *value*);

and so on :-) Have a look at
http://dev.mysql.com/doc/refman/5.1/en/insert.html for more info.


On Wed, Oct 21, 2009 at 1:09 AM, Bermejo, Rodrigo (GE Infra, Aviation) <
rodrigo.bermejo@ge.com> wrote:

> Hola List /.
>
> 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%
>
>
> I am planning to write some code that can keep all "modification"
> statments in memory until a select is needed > commands in memory and then the select>
>
> Any of you have some experience using this approach /?
> Any drawback you can see /?
>
> Thanks for your comments.
>
> -r.
>



--
That which does not kill you was simply not permitted to do so for the
purposes of the plot.

--0016e6d97735498d7e04766c12b2--

Re: Low performance due high network latency - batching ?

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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org