Re: [jira] Commented: (OPENJPA-168) sql optimize n rows query hint
It has not been documented in the OPENJPA documentation. On 4/4/07, Patrick Linskey (JIRA) [EMAIL PROTECTED] wrote: [ https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12486820] Patrick Linskey commented on OPENJPA-168: - Has this new feature been documented? sql optimize n rows query hint -- Key: OPENJPA-168 URL: https://issues.apache.org/jira/browse/OPENJPA-168 Project: OpenJPA Issue Type: New Feature Reporter: David Wisneski Assigned To: David Wisneski Attachments: OPENJPA-168.patch.txt, OPENJPA-168.patch2.txt There werre various comments from Patrick, Abe and Kevin Sutter about the code that I checked related to Optimize hint. So I have gone back and relooked at this and wil be making some changes. At Kevin's suggestion I will do this through a JIRA feature so that folks will have opportunity to comment on this before the code is actually done and checked in. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-168) sql optimize n rows query hint
[ https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12484288 ] Patrick Linskey commented on OPENJPA-168: - 2. I don't think we need a FetchConfiguration instance variable. My understanding is that you can often provide hints about how many results might be returned, as well as whether one or many results will be loaded. So it seems like we might want to expose some way for the user to specify for an arbitrary query (or relation load) that they expect n records to be returned. 4. getOptimizeClause seems too generic. Agreed. The optimizations tend to get tucked into different parts of the SQL statement; this probably will end up needing to be significantly different for different db back-ends. sql optimize n rows query hint -- Key: OPENJPA-168 URL: https://issues.apache.org/jira/browse/OPENJPA-168 Project: OpenJPA Issue Type: New Feature Reporter: David Wisneski Assigned To: David Wisneski Attachments: OPENJPA-168.patch.txt, OPENJPA-168.patch2.txt There werre various comments from Patrick, Abe and Kevin Sutter about the code that I checked related to Optimize hint. So I have gone back and relooked at this and wil be making some changes. At Kevin's suggestion I will do this through a JIRA feature so that folks will have opportunity to comment on this before the code is actually done and checked in. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-168) sql optimize n rows query hint
[
https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12483203
]
Ritika Maheshwari commented on OPENJPA-168:
---
In the singleResult method of the kernel QueryImpl when null was returned I
threw an error using localizer property not-unique.But this error message
does not correctly define the error so in the localizer.properties I added
another property as follows
not-unique: The query on candidate type {0} with filter {1} was \
configured to have a unique result, but more than one instance matched \
the query.
is-null: The query on candidate type {0} with filter {1} was \
configured to have a unique result, but no instance matched \
the query.
so the is-null error would be thrown for the null conditions.So I will apply
this on top of patch2
sql optimize n rows query hint
--
Key: OPENJPA-168
URL: https://issues.apache.org/jira/browse/OPENJPA-168
Project: OpenJPA
Issue Type: New Feature
Reporter: David Wisneski
Assigned To: David Wisneski
Attachments: OPENJPA-168.patch.txt, OPENJPA-168.patch2.txt
There werre various comments from Patrick, Abe and Kevin Sutter about the
code that I checked related to Optimize hint. So I have gone back and
relooked at this and wil be making some changes. At Kevin's suggestion I
will do this through a JIRA feature so that folks will have opportunity to
comment on this before the code is actually done and checked in.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-168) sql optimize n rows query hint
[ https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12482421 ] Abe White commented on OPENJPA-168: --- Comments on the proposed patch: - I don't like the whole scheme of setting the expected result count to -1 for anything artificial. It's confusing and unnecessary. Just set it to the number of expected primary results, and the DBDictionary can invoke sel.hasEagerJoin(true) to figure out if the expected count can be used. Or just have the getter for the expected count always return 0 if there is an eager to-many join (or better yet, turn -1 into a value meaning unknown and have it return -1, which would then also be the default when no expected count is set). - I still think there should be a way to get rid of Union.is/setSingleResult by moving the expected result property to SelectExecutor -- which both Select and Union extend -- and taking advantage of the new expected result (1 obviously indicates a single result). - If you're going to validate the value of the user-supplied hint in the JPA QueryImpl, you might as well transform it into a Number at that point before setting it into the FetchConfiguration. Also, I'd accept any Number, not just an Integer (technically we should accept any whole number, but that's a pain to implement). Then in the JDBC layer, you can just cast the hint value directly to a Number and forgo validating it and checking for String values a second time. - DB2 really cares whether you use optimize for 1 row vs. optimize for 1 rows? That's ugly. - We should probably generalize the configuration of row optimization to the base DBDictionary with an override mechanism. - If you're going to invoke setUnique(true) on the underlying query from the JPA QueryImpl's getSingleResult, you need to do three things: 1. Unset it in a finally clause, because the very next call might be to getResultList, and in general getSingleResult shouldn't have stateful side effects. 2. Change the kernel's QueryImpl to throw an exception when unique is set but the query doesn't return any results. Right now it allows 0 results and will return null, which is indistinguishable from a projection on a null field that returned 1 result. 3. Get rid of the code immediately following in getSingleResult that extracts the value if a List is returned, because after setting the unique flag on the underlying query, it will never return a List. - The hint key should be a constant in the kernel's Query interface or somewhere like that. sql optimize n rows query hint -- Key: OPENJPA-168 URL: https://issues.apache.org/jira/browse/OPENJPA-168 Project: OpenJPA Issue Type: New Feature Reporter: David Wisneski Assigned To: David Wisneski Attachments: OPENJPA-168.patch.txt There werre various comments from Patrick, Abe and Kevin Sutter about the code that I checked related to Optimize hint. So I have gone back and relooked at this and wil be making some changes. At Kevin's suggestion I will do this through a JIRA feature so that folks will have opportunity to comment on this before the code is actually done and checked in. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-168) sql optimize n rows query hint
[ https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480920 ] Abe White commented on OPENJPA-168: --- I don't recall any way to know whether a given Select it is a top-level Select or a parallel Select. Note that if you take my advice and implement this as an ExpectedResultCount property of SelectExecutor (which can also replace the isSingleResult propertyof Union), then you don't need to know. The query/storemanager/whatever that constructs the top-level select will set the expected count based on hints / knowledge, and the dictionary will use it as needed. It won't get passed to parallel eager selects accidentally. sql optimize n rows query hint -- Key: OPENJPA-168 URL: https://issues.apache.org/jira/browse/OPENJPA-168 Project: OpenJPA Issue Type: New Feature Reporter: David Wisneski Assigned To: David Wisneski There werre various comments from Patrick, Abe and Kevin Sutter about the code that I checked related to Optimize hint. So I have gone back and relooked at this and wil be making some changes. At Kevin's suggestion I will do this through a JIRA feature so that folks will have opportunity to comment on this before the code is actually done and checked in. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-168) sql optimize n rows query hint
[ https://issues.apache.org/jira/browse/OPENJPA-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480177 ] Abe White commented on OPENJPA-168: --- 1. I think the hint name should be more like openjpa.hint.OptimizeResultCount. It should be hint and not hints to match the existent openjpa.hint.OracleSelectHint. And I see no reason to tie it to SQL. 2. I don't think we need a FetchConfiguration instance variable. For find() calls and loading hollow objects we always know we're only loading one instance because we use the StoreManager.load() call internally. There are similar internal APIs used only when traversing a to-one relation. So the JDBCStoreManager or relation field strategies can set the proper expected result count into the Select directly. Much better than having to set a FetchConfiguration value and then make sure it disappears for subsequent selects. The only problem is Query.getSingleResult, because right now we execute the query as a multi-result query and then extract the one result at the JPA layer. We don't have to do it this way, though. The underlying kernel Query already has a Unique property you can set to indicate a single result. The property doesn't have quite the semantics we want, because it allows a query that returns 0 results whereas that's an error in JPA. We can't just change the Unique semantics because of JDO, but we could certainly make it configurable on the Query instance whether a Unique query can legally return 0 results. So if we start using the Unique property rather than extracting the single result at the JPA layer, we'll know when the user is using getSingleResult when we construct the Select, and we can again set the expected number of results directly into the Select. 3. The SelectImpl already knows when it has to-many eager joins; no need for additional state. See SelectImpl.hasEagerJoin(boolean toMany). 4. getOptimizeClause seems too generic. I'm also not clear on what use it has in the base DBDictionary class if you state that individual dictionaries will still have to override toSelect themselves to insert the optimization SQL. sql optimize n rows query hint -- Key: OPENJPA-168 URL: https://issues.apache.org/jira/browse/OPENJPA-168 Project: OpenJPA Issue Type: New Feature Reporter: David Wisneski Assigned To: David Wisneski There werre various comments from Patrick, Abe and Kevin Sutter about the code that I checked related to Optimize hint. So I have gone back and relooked at this and wil be making some changes. At Kevin's suggestion I will do this through a JIRA feature so that folks will have opportunity to comment on this before the code is actually done and checked in. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
