N+1 selects will be solved

  • Clinton Begin

    Clinton Begin - 2004-07-15

    Amidst the many messages of the other two threads, this topic might have become fuzzy.  The other two threads were largely dealing with syntax and schema changes to reduce the verbosity around joins that included duplicate column names. 

    This had nothing to do with the N+1 selects solution. 

    iBATIS already has an N+1 solution for 1:1 relationships (nested bean prop syntax w/join).  It's very simple and not verbose at all.  Duplicate column names are easily resolved using SQL aliases. 

    As for the N+1 solution for 1:M and M:N, these are documented thoroughly in the Developer Guide.  It clearly states in the developer guide that a solution for this is coming.  2.0 was designed from the start to handle this and it won't be a difficult change. 

    Gilles and I have briefly discussed the XML stanzas and/or attributes that we'll need to add for this.  It's simply a matter of making a choice.   The community will be involved with making that choice.  We'll hopefully get the alternatives posted here soon.


    • oneway

      oneway - 2004-07-15

      "Duplicate column names are easily resolved using SQL aliases."

      Not easily, no. This involves column relabeling, map definition AND column remapping, i.e. a lot of mundane work if the number tables/columns/joins is large.
      In case when the Bean property and the db table column names are the same this is especially clumsy and unnecessary. There has got to be a better way.

      I'd like to offer another solution that automates the effort (hence greatly simplifying/clarifying it and removing the need for a lot of relabel-remap maps) AND it does NOT require any use of "special" rsMetaData methods:

      <!- note the "prefix" tag -->
      <resultMap id="get-product-result" class="com.ibatis.example.Product" prefix="prod">
      <result property="id" column="ID"/>
      <result property="description" column="DESCRIPTION"/>
      <result property="category" resultMap="get-category-result"/ prefix="cat">

      '' as prod, -- indicates beginning of product data
      '' as cat, -- indicates beginning of category data
      FROM product, category
      WHERE ....

      The only thing this approach requires from the user is that he adds to the SELECT a few columns with predefined labels, much easier thing to do than relabeling.


      • oneway

        oneway - 2004-07-15

        Forgot to mention that prefix="" is an OPTIONAL property. If it's not defined then everything works as it does now, so people who do not want to use it, do not have to.

      • Brandon Goodin

        Brandon Goodin - 2004-07-15

        I'm most likely the last person you want to hear from on this subject. But... Introducing abnormal sql semantics in order to accomodate ibatis is not a good practice. Labeling is a common and well understood practice in sql. So, it is acceptable. I'm sorry if verbosity is a subject that slays you. But, it is not one that we are willing to abandon unless it fits nicely into normal SQL usage.

        • oneway

          oneway - 2004-07-15

          "abnormal sql semantics"

          I have to disagree. This IS a very normal, standard sql semantic, i.e. a regular calculated column with assigned label.

          "I'm sorry if verbosity is a subject that slays you."
          It's not verbosity but unnecessary verbosity combined with the logic that's simply redundant, time consuming and dull:
          E.g. select value as cat_value -> define map -> convert cat_value back to value. Why force ALL people to do this when there is an easy AND portable solution?

          Can you clarify this if this is the position of the whole team? Thanks

          • Larry Meadors

            Larry Meadors - 2004-07-15

            This part of the team shares that position. :-)

            There are apparently philosophical differences in what we consider the expected functionality of iBATIS to be and what you expect it to be.

            I hate to give the impression that your opinion does not matter, because it does. However, if your expectations vary significantly from those of the team, perhaps you should look for something more aligned with your expectations, or write the code yourself to do it and submit a patch.

            • oneway

              oneway - 2004-07-15

              "or write the code yourself to do it and submit a patch."

              I thought that was not an option. I believe it was stated that the patch, if presented, will NOT be applied to the project's core code base. The patch WILL touch the core, though it could be done in a back portable and transparent way so people who do not want to use the functionality do not have to.

              • Brandon Goodin

                Brandon Goodin - 2004-07-15

                That still holds true. It will not be applied to the core. But, the idea of making the ibatis architecture more pluggable would allow for your idea to see the light of day for you own scope and purposes. so, Larry's comment may be a bit misleading.

      • Clinton Begin

        Clinton Begin - 2004-07-15

        I fail to see how that is either easier or more concise than the following common SQL and solution that works in iBATIS today...

        <select id="..." parameterClass="int" resultClass="com.ibatis.example.Product">
            PRD.ID as id
            PRD.DESCRIPTION as description
            CAT.ID as "cagegory.id"
            CAT.DESCRIPTION as "category.description"
        WHERE PROD.ID = #value#


        • Clinton Begin

          Clinton Begin - 2004-07-15

          Whoops typo...PROD = PRD or vice versa...

        • oneway

          oneway - 2004-07-15

          "I fail to see how that is either easier..."

          The difference is the effort required for map definitions:

          With your approach the map will be:
          <resultMap id=get-product-result class=com.ibatis.example.Product>
          <result property=id column=PRD_ID/>
          <result property=description column=PRD_DESCRIPTION/>
          <result property=category.id column=CAT_ID />
          <result property=category.description column=CAT_DESCRIPTION />

          With the proposed approach plus "same name property/column automapping" (these two features are complimentary to each other, see the respective thread) the map will look like:

          <resultMap id=get-product-result class=com.ibatis.example.Product prefix="prod">
                  <result property="category" resultMap="get-category-result" prefix="cat">

          The benefit is map reuse and better maintenance. The more tables/column/joins you have the greater the benefit.

          • Clinton Begin

            Clinton Begin - 2004-07-16

            "The difference is the effort required for map definitions: With your approach the map will be:"

            It will be NOTHING!  :-)

            You clearly are not understanding yet.  With the approach I presented, there is NO result map required.  It leverages a feature of iBATIS called "auto-mapping", or "implicit result maps". 

            Please refer to the section entitled "Implicit Result Maps" on page 23 of the developer guide that you can download here:



        • John Didion

          John Didion - 2004-07-22

          How will this handle the case in which I call a stored procedure that may return a different set of data depending on the parameters, and I want to map each result based on the value of a certain column or combination of columns. For instance:

          String type = selectTypeByShapeId(id);
          If (type == circle)
            return selectCircleByShapeId(id);
            return selectSquareByShapeId(id);

          I can do this in one stored procedure call, but right now, iBatis has no way to say "map this result as a circle if the value of the type column == 'circle', or map it as a square if the value of the type column == 'square'). In my previous communications with you (Clintion), you said this N+1 syntax would handle such a case. Can you explain?

    • Philippe Laflamme

      > Gilles and I have briefly discussed the XML stanzas and/or attributes that we'll need to add for this.

      Would it be possible to get a preview/idea of what the choices are?

      I'm very much interested in this: I don't know what numbers you're used to, but I'm working on a project that has close to 150 resultMaps and 250 mapped statements; and these numbers can only get bigger...

      Having an idea of what is to come in terms what iBatis is able to offer is quite important to us.


    • oneway

      oneway - 2004-07-16

      > It will be NOTHING! :-)

      Yes, I missed that. That's a very good news, thanks for clarifying that.

    • Raymond

      Raymond - 2004-09-30

      Hi Clinton,

      Is there any progress on N+1 issue? This is so far the biggest performance concern of our current project.

      Thank you!


    • Tim Chen

      Tim Chen - 2004-10-06

      IBatis team.
         I've run into the N+1 issue myself and was wondering why it you cant solve it with a bit of introspection and the same syntax as the N+1 issue for a 1:1 mapping?

      For example I have the following:
      (Sorry in advance for the paste formatting)

      select distinct ntiQuarterNumber as quarter, ntiQuarterName as name, ntiQuarterDesc as description, ntiSeasonYear as year
      from BroadcastDate
      where ntiSeasonYear = #year#
      order by ntiQuarterNumber;

      And have to get months of that quarter by doing:

          select distinct ntiMonthName as name, ntiMonthDesc as description, min(broadcastDate) as broadcastStartDate, max(broadcastDate) as broadcastEndDate
          from BroadcastDate
          where ntiSeasonYear = #year#
          and ntiQuarterNumber = #quarter#
          group by ntiSeasonYear, ntiQuarterNumber, ntiMonthName, ntiMonthDesc
          order by min(broadcastDate);

      Of course tying the whole thing with a result map

      That same sql would look just like if it were a 1-1 mapping normally:

      select distinct ntiQuarterNumber as quarter, ntiQuarterName as name, ntiQuarterDesc as description, ntiSeasonYear as year, ntiMonthName as month.name, ntiMonthDesc as month.description, min(broadcastDate) as month.broadcastStartDate, max(broadcastDate) as month.broadcastEndDate
      from BroadcastDate
      where ntiSeasonYear = #year#
      group by ntiSeasonYear, ntiQuarterNumber, ntiMonthName, ntiMonthDesc
      order by ntiQuarterNumber, min(broadcastDate);

      Again sorry for the ugly paste.

      Now obviously this reduces the amount of configuration I have to do but looks a bit like the 1:1 formatting.
      months since it would map to a Collection type (which can be found out by a simple introspect) will alert that this is a m:n scenario.

      The result set can then be run thru a m:n handler without having to do setup result maps or anything special.

      You guys know I tend to ramble a bit but I hope that makes sense.

      To me this is a very simple solution without the need to come up with a 'special' sql syntax.

      Let me know if I missed something by oversimplifying.

    • John Didion

      John Didion - 2004-10-28

      Any progress on this? I'd love to have this available to use on the next version of our product in January. As I've said before, I'm ready, willing, and able to write the code once we decide on the right scheme for doing this.

      • Clinton Begin

        Clinton Begin - 2004-10-28

        Sorry, I'm terribly frustrated with myself for not getting this done sooner.  I've been super busy with "real" work.  Plus, I've been away from home for the last 2 months.  That combined with the Apache efforts means that a lot of things are currently moving a little slowly.

        That said, I do have a plan and a design for this (2.0 was actually designed for this from day 1).  At the very least, I'll try to get a discussion started within the next week so that everyone can be involved. 


        • Kris Jenkins

          Kris Jenkins - 2004-10-28

          Cool!  Glad to hear it. :-)

          This is a feature I'm really looking forward to - count me in for time, coding, documentation, TestCases, performance tuning...erm...shipping bagels out to Canada?


          • Clinton Begin

            Clinton Begin - 2004-10-28

            Thanks Kris.  Send the bagels to Stanford University Campus, Palo Alto, CA.  Attn:  Canadian Guy.



Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks