N+1 selects will be solved
Brought to you by:
cbegin
Menu
▾
▴
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.
Cheers,
Clinton
"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">
</resultMap>
SELECT
'' as prod, -- indicates beginning of product data
product.*,
'' as cat, -- indicates beginning of category data
category.*
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.
Thoughts?
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.
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.
"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
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.
"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.
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.
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">
select
PRD.ID as id
PRD.DESCRIPTION as description
CAT.ID as "cagegory.id"
CAT.DESCRIPTION as "category.description"
FROM PRODUCT PROD, CATEGORY CAT
WHERE PROD.ID = #value#
</select>
Cheers,
Clinton
Whoops typo...PROD = PRD or vice versa...
"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 />
</resultMap>
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">
</resultMap>
The benefit is map reuse and better maintenance. The more tables/column/joins you have the greater the benefit.
"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:
http://prdownloads.sourceforge.net/ibatisdb/iBATIS-SqlMaps-2.pdf?download
Cheers,
Clinton
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);
else
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?
> 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.
Thanks,
Philippe
> It will be NOTHING! :-)
Yes, I missed that. That's a very good news, thanks for clarifying that.
Hi Clinton,
Is there any progress on N+1 issue? This is so far the biggest performance concern of our current project.
Thank you!
Regards,
Raymond
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.
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.
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.
Cheers,
Clinton
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?
Kris
Thanks Kris. Send the bagels to Stanford University Campus, Palo Alto, CA. Attn: Canadian Guy.
Cheers,
Clinton