OpenJPA provided some parameter in the FetchPlan. (http://openjpa.apache.org/builds/1.2.0/apidocs/org/apache/openjpa/persistence/FetchPlan.html) And I was stuck in one of them, the FetchBatchSize. Hope someone can kindly share their experience.
Here is the scenario, say I was going select up to 1000 record from the database(MS-SQL) 1. if I left FetchBatchSize to its default value -1, it will take some 20 seconds to return the 1000 records;
if I set the FetchBatchSize to 100, the time cost was reduced, dramatically, to less than 2 seconds, that's good, but if I try to get only 50 records, which is less than the FetchBatchSize 100, it will take a much longer time (50 seconds in my test) which is not acceptable.
ok, so, I guess if the FetchBatchSize exceeds the number user wanted, it will take more time. I could change the FetchBatchSize in runtime, in accordance to the count set by user. you want 1000, I set FetchBatchSize to 1000, you want 100, I set it to 50, but what if the user only needs a few records? I too set the FetchBatchSize to some like 2?
So my question is, how to select a proper FetchBatchSize when the maxResultCount is variable? if there any DEFAULT value, like 20, in the openJPA document that will suitable for most scenario? Or it is recommended to change it at runtime.
Any comment is appreciated. Thanks!
This is actually more of a MS-SQL tuning question than an OpenJPA question -- the FetchBatchSize value is passed along to the driver.
There's certainly nothing wrong with setting the value dynamically at runtime based on the max result count parameter. Also, assuming that the delay that you experienced when specifying a number greater than the limit clause is not due to some obscure bit of configuration, it'd be interesting to add something to the OpenJPA SQLServerDictionary to cap the batch size when a max result value is present. Might be a good thing to bring up on the OpenJPA mailing list.