aspose file tools*
The moose likes JDBC and the fly likes MySql: ResultSet clarification Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql: ResultSet clarification" Watch "MySql: ResultSet clarification" New topic
Author

MySql: ResultSet clarification

Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
In populating my database, I have the following field and method:

On the first insert, the method prints out:
1.
The second time through the loop, it prints out:
2
2

The third time through the loop:
3
3
3


By the time I get up to 15,000, my app starts getting very slow. I have looked at the docs for ResultSet, but I can't find anything to explain that behavior. As far as I can tell, a new ResultSet should be getting created each time, but it appears that the last_key just gets added to the result set, and all of the elements of the result set are changed to be equal to the last key. Is there a better way of doing this, or some explanation for why the ResultSet acts like it does?

[ July 17, 2006: Message edited by: Chad Clites ]

[edited to add line breaks]
[ July 17, 2006: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30919
    
158

Chad,
Suppose you had the main query being "select name from term_ids". You would expect to get one row back for each row in the table, right? That's just what your query is doing. It is calling last_insert_id() once for each row you have in the table.

last_insert_id() returns the same value each time per the reference documentation within the same query. So you get duplicate results. To avoid this, you could add a clause to the sql query to only return one row. Or you could run the query against a system table instead of your table.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
Thank you, that makes perfect sense. I am trying an alternate solution. I changed my query to:
"SELECT DISTINCT last_insert_id() AS lastKey FROM term_ids"

That seems to be going quite a bit quicker. I think I will try your idea too, just for comparison, and building my sql vocabulary.

(Thanks for editing my post also)
[ July 17, 2006: Message edited by: Chad Clites ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Chad Clites:
Thank you, that makes perfect sense. I am trying an alternate solution. I changed my query to:
"SELECT DISTINCT last_insert_id() AS lastKey FROM term_ids"

That seems to be going quite a bit quicker. I think I will try your idea too, just for comparison, and building my sql vocabulary.

(Thanks for editing my post also)

[ July 17, 2006: Message edited by: Chad Clites ]


SELECT DISTINCT is still wrong; the database is still fetching every row in the term_ids table into memory in order to calculate a result that doesn't at all depend on any of the values in the table. It will get slower and slower as the table grows.

As the MySql documentatation makes clear, here:
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html
you should simply be doing:


MySql is relatively unusual in its allowed SQL syntax.
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
Interestingly enough, using the following code from the JConnector docs:

Takes the same amount of time as the initial code I was using. In the beginning, roughly 100 inserts were accomplished each second. By 50,000 inserts, it was down to 15 inserts per second. It appears (to my limited understanding) that the same process is still occuring in the background.

Cheers.
[ July 18, 2006: Message edited by: Chad Clites ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Interesting. I would guess (and guess is exactly what it is) that it wouldn't be any better with the DISTINCT, or any other way, that the issue is either with the function itself, or with your table definition.

Just exactly what column is your id column, and is it a primary key? Or an indexed column? If it's neither, that's probably why it's slowing down as the table gets beigger.
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
The id column is the primary key, and it is auto-incremented. My guess is that the system table(s?) holds what I need (as Jeanne already suggested), and I just need to investigate it further.

It's not that I need my app to be overly efficient, but it just makes sense to me that somewhere the last insert id should be stored.

Thanks for both of your input.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30919
    
158

I agree with sut about select distinct being inefficient. It's not that the data you need is in the system table. It's that you are calling a function that isn't tied to any table. So if you can run it against a table with only one row, it will save processing.
 
 
subject: MySql: ResultSet clarification