-
1. Re: Slow down when fetching large result sets
shawkins Aug 31, 2016 12:49 PM (in response to ehudreches)It would take profiling or gc information to determine exactly what is happening. A possible issue would be if the result set is being buffered in any way - are you using result set caching, a scrollable resultset, etc.? Are you using a socket connection?
Locally when I mock up this scenario against H2, I don't see anything like the behavior described regardless of the number of runs.
> But obviously large values for process-batch-size will not solve the problem when hundreds of millions of rows are fetched.
It is definitely fine to increase the processor batch size when you are in a low query volume scenario, but yes, it shouldn't be increased to unreasonable levels.
-
2. Re: Slow down when fetching large result sets
ehudreches Aug 31, 2016 4:03 PM (in response to shawkins)Thanks for the quick response.
- We don’t ask explicitly for result set caching or scrolling. In the log I can see: “Command has no cache hint and result set cache mode is not on”.
Any other type of result set buffering/caching?
- Socket connection: The client calls from JDBC. We use a socket with wire protocol WireProtocol.teii. Is that what you are asking?
If it’s not too difficult, could you tell me how to issue the query directly to Teiid, on the server side, bypassing JDBC and the wire altogether?
If you have any other ideas for experiments or what Teiid code to look at, please let me know.
Thanks.
-
3. Re: Slow down when fetching large result sets
shawkins Aug 31, 2016 4:49 PM (in response to ehudreches)> We don’t ask explicitly for result set caching or scrolling. In the log I can see: “Command has no cache hint and result set cache mode is not on”.
Then it will default to forward only and you wouldn't expect the result set to be buffered/cached.
> Socket connection: The client calls from JDBC. We use a socket with wire protocol WireProtocol.teii. Is that what you are asking?
Yes. However locally I don't see any issue with a local or socket connection.
> If it’s not too difficult, could you tell me how to issue the query directly to Teiid, on the server side, bypassing JDBC and the wire altogether?
You can't bypass JDBC, but you can consume directly with a local connection from EmbeddedServer.getDriver() which bypasses the socket layer.
I'll try specifically using HSQL with 8.13 rather than just testing on master with H2 to see if that makes a difference. If there is more you can provide about what you are doing, such as a stripped down reproducer I could easily provide more input.
-
4. Re: Slow down when fetching large result sets
shawkins Sep 7, 2016 6:30 PM (in response to shawkins)I am not able to reproduce this. Using 8.13, HSQL, and a socket connection I can retrieve 2000000 rows of 40 small strings in under 10 seconds. We'll need more details of your scenario to understand what you are seeing.
-
5. Re: Slow down when fetching large result sets
ehudreches Sep 8, 2016 10:06 AM (in response to shawkins)Ok, thank you for testing it. There must be something else going on in our environment. I'll post if I find anything related to Teiid.