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

[Matthew T. Adams]

I will see what I can do with joining.  I guess using
sprocs in other database engines have made me a little
sql-lazy...

Thanks all for the help.

--matthew

--- Christopher Taylor <cstaylor@stripped> wrote:
> 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
> >
> >
> 
> 
> -- 
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe:   
>
http://lists.mysql.com/java?unsub=1
> 


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com