SQL optimisation

Thread view:

Roedy Green - 12 Jun 2004 04:21 GMT

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" );

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Gert van der Kooij - 12 Jun 2004 12:13 GMT

> 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.

Reply to this Message

Roedy Green - 12 Jun 2004 19:35 GMT

>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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Will Hartung - 14 Jun 2004 22:18 GMT

> > 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@msoft.com)

Reply to this Message

Roedy Green - 14 Jun 2004 22:17 GMT

>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.

Signature

Canadian Mind Products, Roedy Green.
Coaching, problem solving, economical contract programming.
See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.

Reply to this Message

Hal Rosser - 13 Jun 2004 07:51 GMT

IT looks loverly
Most efficient way is 'elbows on the desk' as you type - and type correctly
without having to backspace.
;-)

Reply to this Message

KC Wong - 14 Jun 2004 10:36 GMT

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 ;-)

Reply to this Message

SQL optimisation

Free Magazines