Question on SELECT TOP 3?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Izak van Langevelde

    #1

    Question on SELECT TOP 3?

    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

    #2
    Re: Question on SELECT TOP 3?

    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" <eezacque@xs4al l.nl> wrote in message
    news:eezacque-ACCD89.02150804 082005@news1.ne ws.xs4all.nl...[color=blue]
    >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...[/color]


    Comment

    • Allen Browne

      #3
      Re: Question on SELECT TOP 3?

      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" <eezacque@xs4al l.nl> wrote in message
      news:eezacque-ACCD89.02150804 082005@news1.ne ws.xs4all.nl...[color=blue]
      >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...[/color]


      Comment

      • Izak van Langevelde

        #4
        Re: Question on SELECT TOP 3?

        In article
        <42f18872$0$119 33$5a62ac22@per-qv1-newsreader-01.iinet.net.au >,
        "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote:
        [color=blue]
        > 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.[/color]

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

        Comment

        • Izak van Langevelde

          #5
          Re: Question on SELECT TOP 3?

          In article
          <42f18872$0$119 33$5a62ac22@per-qv1-newsreader-01.iinet.net.au >,
          "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote:
          [color=blue]
          > 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.[/color]

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

          Comment

          Working...