Re: Becoming a FAQ: Executing multiple sql statements in a single network round trip

[Christopher Taylor]

I've written some pretty heavy duty reporting code (spanning all data
transactions for a month for commissions), and I don't cache anything.  If
you write your SQL correctly, you can get MySQL to do most of the work for
you, which would reduce a lot of the network traffic.  For example, I have a
monthly report that spans 10,000 records spread across a number of tables,
so I do simple joins on those tables to combine several SQL statements into
one.

The keys to performance when you have MySQL doing the work are indices and
memory tuning for the database.  I originally did all my development without
indices, and then applied them afterwards and got a 5x speed up on the same
code.  In both cases, the network traffic was drawfed by MySQL churning
through the data.

I guess my point is: don't optimize where it's not necessary.  Can't you use
joins to reduce the number of roundtrips for your selects?

-Chris

----- Original Message ----- 
From: "Paul DuBois" <paul@stripped>
To: "Matthew T. Adams" <matthewadams@stripped>
Cc: <java@stripped>
Sent: Friday, October 03, 2003 5:54 AM
Subject: Re: Becoming a FAQ: Executing multiple sql statements in a single
network round trip


> At 12:55 -0700 10/2/03, Matthew T. Adams wrote:
> >I didn't realize that he wrote it.  My bad -- sorry.
>
> That's Mark's fault.  He was too modest to mention it.
>
> >I guess I was thinking that using a batch of
> >statements, a la Statement.executeBatch() would work,
> >but since no one mentioned it, and I dug a little
> >deeper, I don't think it will, either.
> >
> >I think my best bet is to wait for server-side
> >prepared statement support in 4.1 and use that.
> >
> >Sorry for my dumb mysql newbie questions...
>
> No, they're not dumb questions at all.  They're good questions.
>
> It's just that when they get answered, and you keep asking them,
> *then* they appear a little suspect. :-)
>
>
> >
> >--matthew
> >
> >--- Paul DuBois <paul@stripped> wrote:
> >>  At 9:56 -0700 10/2/03, Matthew T. Adams wrote:
> >>  >Yes, they are mostly queries.  Yes, I know I can
> >>  >cache, as long as the data underneath doesn't
> >>  change.
> >>  >These happen to be for reporting purposes, and the
> >>  >nature of the reports require (so far) the creation
> >>  of
> >>  >a temporary table and a subsequent select from it.
> >>  >
> >>  >It's not that I don't appreciate the suggestions,
> >>  but,
> >>  >please, can someone tell me if there is a way to
> >>  batch
> >>  >statements using MySQL via Connector/J?
> >>
> >>  Mark Matthews has already answered your question.
> >>  He *wrote* Connector/J, so his answer is
> >>  authoritative.
> >>  Why is it that you appear not to believe his answer?
> >>
> >>
> >>  >
> >>  >--- Christopher Taylor <cstaylor@stripped> wrote:
> >>  >>  Are you mostly making queries?  Why not cache
> >>  that
> >>  >>  information instead of
> >>  >>  going to the database everytime?
> >>  >>
> >  > >  > -Chris
>
>
> -- 
> Paul DuBois, Senior Technical Writer
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com
>
> Are you MySQL certified?  http://www.mysql.com/certification/
>
>
> -- 
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe:    http://lists.mysql.com/java?unsub=1
>
>