Question on SELECT TOP 3?

[Izak van Langevelde]

I would like to query a database for 3 children whose fathers are the
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first shot was:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE

However, in the above example this yields at least 6 children, as the
top 3 birth dates cover at least the 6 children of the oldest father.

My second attempt was to have the order-by clause enforce distinct rows
by adding a primary key:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE, DAD.ID

This works, although it is a bit slow, due to the double sort.
However, it looks a bit clumsy: is there a better, standard, solution?

Also, when I incorporate this in my Java application, as opposed to
running it as a small test, query performance drops by a factor 50. It
is the one query I haven't been able to incorporate successfully in my
code, and I suspect I hit some special case of resources becoming
scarce. I checked and double checked that I'm closing ResultSets,
Statements and Connections everywhere, I tried to use a new Connection
for this one query, but it didn't make a difference. Are there any
known issues with horrible performance drops in jdbc?

--
Grinnikend door het leven...

[Allen Browne]

Adding the unique field to the ORDER BY clause is the appropriate solution.

You could probably get better performance by using an inner join between KID
and DAD instead of the Cartesian product with WHERE clause to thin it out.

Make sure there is an index on the BirthDate field.

Presumably you have created a relationship between KID and DAD, with
enforced referential integrity, so Access should already have the (hidden)
index on Kid.Father.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Izak van Langevelde" <ee******@xs4all.nl> wrote in message
news:ee****************************@news1.news.xs4 all.nl...

I would like to query a database for 3 children whose fathers are the
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first shot was:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE

However, in the above example this yields at least 6 children, as the
top 3 birth dates cover at least the 6 children of the oldest father.

My second attempt was to have the order-by clause enforce distinct rows
by adding a primary key:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE, DAD.ID

This works, although it is a bit slow, due to the double sort.
However, it looks a bit clumsy: is there a better, standard, solution?

Also, when I incorporate this in my Java application, as opposed to
running it as a small test, query performance drops by a factor 50. It
is the one query I haven't been able to incorporate successfully in my
code, and I suspect I hit some special case of resources becoming
scarce. I checked and double checked that I'm closing ResultSets,
Statements and Connections everywhere, I tried to use a new Connection
for this one query, but it didn't make a difference. Are there any
known issues with horrible performance drops in jdbc?

--
Grinnikend door het leven...

[Allen Browne]

Adding the unique field to the ORDER BY clause is the appropriate solution.

You could probably get better performance by using an inner join between KID
and DAD instead of the Cartesian product with WHERE clause to thin it out.

Make sure there is an index on the BirthDate field.

Presumably you have created a relationship between KID and DAD, with
enforced referential integrity, so Access should already have the (hidden)
index on Kid.Father.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Izak van Langevelde" <ee******@xs4all.nl> wrote in message
news:ee****************************@news1.news.xs4 all.nl...

I would like to query a database for 3 children whose fathers are the
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first shot was:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE

However, in the above example this yields at least 6 children, as the
top 3 birth dates cover at least the 6 children of the oldest father.

My second attempt was to have the order-by clause enforce distinct rows
by adding a primary key:

SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD.ID
ORDER BY DAD.BIRTHDATE, DAD.ID

This works, although it is a bit slow, due to the double sort.
However, it looks a bit clumsy: is there a better, standard, solution?

Also, when I incorporate this in my Java application, as opposed to
running it as a small test, query performance drops by a factor 50. It
is the one query I haven't been able to incorporate successfully in my
code, and I suspect I hit some special case of resources becoming
scarce. I checked and double checked that I'm closing ResultSets,
Statements and Connections everywhere, I tried to use a new Connection
for this one query, but it didn't make a difference. Are there any
known issues with horrible performance drops in jdbc?

--
Grinnikend door het leven...

[Izak van Langevelde]

In article
<42***********************@per-qv1-newsreader-01.iinet.net.au>,
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

Adding the unique field to the ORDER BY clause is the appropriate solution.

You could probably get better performance by using an inner join between KID
and DAD instead of the Cartesian product with WHERE clause to thin it out.

Make sure there is an index on the BirthDate field.

Presumably you have created a relationship between KID and DAD, with
enforced referential integrity, so Access should already have the (hidden)
index on Kid.Father.

The indices are there, and the inner join shows some improvement; thanks
for your suggestions.

In the meantime I learnt the query isn't really the problem, as it takes
between less than one second and more than 40 seconds, depending on the
location in the Java source.

I suspect the jdbc odbc bridge driver plays jokes, and I'll spend some
more time isolating the problem.

--
Grinnikend door het leven...

[Izak van Langevelde]

In article
<42***********************@per-qv1-newsreader-01.iinet.net.au>,
"Allen Browne" <Al*********@SeeSig.Invalid> wrote:

Adding the unique field to the ORDER BY clause is the appropriate solution.

You could probably get better performance by using an inner join between KID
and DAD instead of the Cartesian product with WHERE clause to thin it out.

Make sure there is an index on the BirthDate field.

Presumably you have created a relationship between KID and DAD, with
enforced referential integrity, so Access should already have the (hidden)
index on Kid.Father.

The indices are there, and the inner join shows some improvement; thanks
for your suggestions.

In the meantime I learnt the query isn't really the problem, as it takes
between less than one second and more than 40 seconds, depending on the
location in the Java source.

I suspect the jdbc odbc bridge driver plays jokes, and I'll spend some
more time isolating the problem.

--
Grinnikend door het leven...