This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Which Query is faster Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Which Query is faster" Watch "Which Query is faster" New topic
Author

Which Query is faster

Chiranjeevi Kanthraj
Ranch Hand

Joined: Feb 18, 2008
Posts: 289


or

[ July 23, 2008: Message edited by: Chiru Raj ]

-Chiru
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41627
    
  55
That's impossible to say in general because it depends on which DB is used, which indices are in place etc. What did your tests indicate?


Ping & DNS - my free Android networking tools app
Chiranjeevi Kanthraj
Ranch Hand

Joined: Feb 18, 2008
Posts: 289

I am using MySql with Type as InnoDB
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Again, this is still impossible to answer. Query performance is largely driven by: the data you have in your database, the indices you have defined, the state of those indices etc.

I could make a wild guess that the second query may be slower, since this uses a subquery. But the only real way to know is to test it. You should do that.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

My gut says the opposite - I would think the second is faster if you have an index on primaryColumn. The execution plans would be different for both scenarios.

First:
1) Retrieve all rows from disk (sorting by primaryColumn if no index is returned)
2) Return the first one across the network
3) Hope the db is smart enough to retrieve all the rows before discarding them (many are not)

Second:
1) Go directly to the max row via a sorted index
2) Retrieve the one row from disk
3) Return the one row across the network

As you can see, it is possible to speculate that either is faster. Paul and I bother have different gut instincts. All that matters is what is faster for you of course. Which means you'll have to try it.


[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
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I'm going to go back and sec and say you really need to run tests on real data (not test data). Query optimizes are magically creatures that may or may not use indexes properly. In other words, while the proper indexes should guarantee a near-optimal query path, there's no guarantee a particular DBMS system will find the path; since most query optimizes do not have the time to search all paths.

The only piece of advice I can give you is to avoid nested queries. In general, query optimizes make better use of search trees with joins rather than nested queries, although in the case of max/min, I'm not sure you can avoid the subquery other than sorting your results.

Also, some systems support a third query "SELECT TOP 1 * FROM table order by primaryColumn desc".


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Which Query is faster