Hello Mark, Thank you for the note and feedback. BTW, it was not over a network. Both on local PC. So all the time went into building the resultset in memory. I'm surprised at the time that takes.
Further to why I'm desirous to do this query, twofold. One I get the record count (rows in the table); since realised there are other ways to get he number of rows. For the second requirement I cannot see an alternative. Not on this table, but on another where I keep a timestamp, I'd like to get the 50 most recent inserts. So what I do is; (SELECT * FROM MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC) thus get all the rows, ordered desc on timestamp and then I have the most recent 50 inserts. If anyone could assist me with this, I'd use other means, rather than retrieving the full resultset. Kind regards Emmanuel > -----Original Message----- > From: Mark Matthews [mailto:[EMAIL PROTECTED]] > Sent: 19 January 2002 16:25 > To: Emmanuel van der Meulen > Cc: [EMAIL PROTECTED] > Subject: Re: Understanding throughput with JDBC > > > > ----- Original Message ----- > From: "Emmanuel van der Meulen" <[EMAIL PROTECTED]> > To: "MySQL Java List" <[EMAIL PROTECTED]>; "MySQL General List" > <[EMAIL PROTECTED]> > Sent: Saturday, January 19, 2002 7:43 AM > Subject: Understanding throughput with JDBC > > > > Hello all, > > > > Could anyone please assist me to understand this. I want to understand > > where the time is going to and whether there is something I can do about > it, > > with the following query when using JDBC; > > > > Here is the table declaration; > > CREATE TABLE Memberships ("EMAIL CHAR(60) NOT NULL, > > NAME CHAR(50) NOT NULL, > > MIDDLEINITIAL CHAR(1) NULL, > > SURNAME CHAR(50) NOT NULL, > > NEWSLETTER CHAR(1) NOT NULL, > > USERNAME CHAR(15) NOT NULL, > > PASSWORD CHAR(15) NOT NULL, > > COUNTRY CHAR(50) NOT NULL, > > PRIMARY KEY (EMAIL)) TYPE=InnoDB" > > > > > > MySQL table memberships has 110080 rows. > > > > Here is the select statement I use with JDBC; > > SELECT * FROM memberships ORDER BY EMAIL > > > > Same select statement I use from the MySQL 'commandline'; > > mysql> select * into outfile 'mysql200000.txt' from memberships order by > > email limit 200000; > > > > My observations; > > ---------------- > > A. When running the SQL from the MySQL 'commandline' with above > statement > > this is the response; > > mysql> select * into outfile 'mysql200000.txt' from memberships order by > > email l > > imit 200000; > > Query OK, 110080 rows affected (1.27 sec) > > > > B. When running the exact same select as stated above in a java program > > using JDBC, from when issuing, ResultSet rs = stmt.executeQuery(query) > until > > the java program gets control back, to create the resultset, takes 23-25 > > seconds; I ran program several times. > > > > > > Please advise whether this is to be expected, or whether I could alter > > anything to get comparable results via JDBC as with MySQL 'commandline'? > > No network traffic in the first case, always network traffic in > the second. > No memory allocation in the first case, needing to allocate space for the > entire result set in the second (the MySQL protocol doesn't mesh > really well > with the JDBC spec's requirements at this point in time, so the > JDBC driver > is required to read the entire result set in to memory to allow > things like > thread safety and multiple concurrent queries without being > overly complex). > > With JDBC, it is usually not a good idea (with any driver) to > select a whole > slew of rows, because you won't get the performance you desire. In most > cases it isn't necessary, and isn't the most efficient way of doing things > either. When developing client server applications, you should use the > database for what it's good for, manipulating, aggregating and > transforming > large amounts of data, and then look at the results (which will > hopefully be > smaller). Only in very special cases do you need to return large > result sets > (like OLAP for example). > > I guess it comes down to, what exactly are you trying to do > selecing 110,000 > rows, and do you need to return the whole result set at once...If you're > just trying to dump it to a file, why are you doing that with Java when > MySQL can do it directly? > > -Mark > > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
