AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  SQL optimisation          AppletTalk.com Forum Index -> Java GUI Toolkits View previous topic :: View next topic   Author Message Roedy Green Guest Posted: Sat Jun 12, 2004 3:21 am    Post subject: SQL optimisation Is there a more efficient way to write this query? conn.prepareStatement( "SELECT confirm, ordertimestamp, unencrypted, encrypted FROM orders WHERE confirm > ? AND vendorid = ? ORDER BY confirm LIMIT 1" ); -- Canadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. Back to top Gert van der Kooij Guest Posted: Sat Jun 12, 2004 11:13 am    Post subject: Re: SQL optimisation In article <igtkc01otrmipd3p85oda5i9hcao8p9jlk (AT) 4ax (DOT) com>, Roedy Green (look-on (AT) mindprod (DOT) com.invalid) says... Quote: Is there a more efficient way to write this query? conn.prepareStatement( "SELECT confirm, ordertimestamp, unencrypted, encrypted FROM orders WHERE confirm > ? AND vendorid = ? ORDER BY confirm LIMIT 1" ); The most important thing is to have an index on vendorid. Because it doesn't look like a primairy key you might also need to add confirm to the index or create a seperate index on it. Warning: if the combination of both columns isn't unique and you need to page thru the records you might run into a loop, it might return the same record over and over again. Back to top Roedy Green Guest Posted: Sat Jun 12, 2004 6:35 pm    Post subject: Re: SQL optimisation On Sat, 12 Jun 2004 13:13:57 +0200, Gert van der Kooij <gert (AT) invalid (DOT) nl> wrote or quoted : Quote: The most important thing is to have an index on vendorid. Because it doesn't look like a primairy key you might also need to add confirm to the index or create a seperate index on it. Right now confirm is an index. It is unique. -- Canadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. Back to top Hal Rosser Guest Posted: Sun Jun 13, 2004 6:51 am    Post subject: Re: SQL optimisation IT looks loverly Most efficient way is 'elbows on the desk' as you type - and type correctly without having to backspace. ;-) "Roedy Green" <look-on (AT) mindprod (DOT) com.invalid> wrote Quote: Is there a more efficient way to write this query? conn.prepareStatement( "SELECT confirm, ordertimestamp, unencrypted, encrypted FROM orders WHERE confirm > ? AND vendorid = ? ORDER BY confirm LIMIT 1" ); -- Canadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system ([url]http://www.grisoft.com)[/url]. Version: 6.0.690 / Virus Database: 451 - Release Date: 5/22/2004 Back to top KC Wong Guest Posted: Mon Jun 14, 2004 9:36 am    Post subject: Re: SQL optimisation Quote: Is there a more efficient way to write this query? conn.prepareStatement( "SELECT confirm, ordertimestamp, unencrypted, encrypted FROM orders WHERE confirm > ? AND vendorid = ? ORDER BY confirm LIMIT 1" ); I'd just add a JTextArea, and call setText() on it... since this is comp.lang.java.gui. You've posted to the wrong group, Roedy ;-) Back to top Roedy Green Guest Posted: Mon Jun 14, 2004 9:17 pm    Post subject: Re: SQL optimisation On Mon, 14 Jun 2004 14:18:18 -0700, "Will Hartung" <willh (AT) msoft (DOT) com> wrote or quoted : Quote: This more explicitly says pretty much exactly what you want (though, as noted, you may well get multiple identical rows unless the vendorid and confirm tuple is unique). I have something even stronger, confirmNumber is unique. -- Canadian Mind Products, Roedy Green. Coaching, problem solving, economical contract programming. See http://mindprod.com/jgloss/jgloss.html for The Java Glossary. Back to top Will Hartung Guest Posted: Mon Jun 14, 2004 9:18 pm    Post subject: Re: SQL optimisation "Gert van der Kooij" <gert (AT) invalid (DOT) nl> wrote Quote: In article <igtkc01otrmipd3p85oda5i9hcao8p9jlk (AT) 4ax (DOT) com>, Roedy Green (look-on (AT) mindprod (DOT) com.invalid) says... Is there a more efficient way to write this query? conn.prepareStatement( "SELECT confirm, ordertimestamp, unencrypted, encrypted FROM orders WHERE confirm > ? AND vendorid = ? ORDER BY confirm LIMIT 1" ); The most important thing is to have an index on vendorid. Because it doesn't look like a primairy key you might also need to add confirm to the index or create a seperate index on it. Warning: if the combination of both columns isn't unique and you need to page thru the records you might run into a loop, it might return the same record over and over again. In an ISAM system with a vendorid and confirm index, you'd get this is in two disk hits. In a SQL system, who knows. Depending on the DB, optimizer, record volume, etc. etc., the system may well build a result set of all conforming records, THEN sort them, THEN return the set to you. You may well try something like this: SELECT confirm, ordertimestamp, unecrypted, encrypted FROM orders WHERE vendorid = ? AND confirm = (SELECT min(confirm) FROM orders WHERE confirm > ? and vendorid = ?) This more explicitly says pretty much exactly what you want (though, as noted, you may well get multiple identical rows unless the vendorid and confirm tuple is unique). I'd run both through the query optimizer and see what happens. These kind of "latest" queries are particularly horrible in SQL databases because the potential results sets can be so large. A naive optimizer can ruin your whole query. Regards, Will Hartung (willh (AT) msoft (DOT) com) Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java GUI Toolkits All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.