[jira] [Closed] (CAY-3013) MySQL: JDBC-level streaming for iterators

["Andrus Adamchik (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CAY-3013?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Andrus Adamchik closed CAY-3013.
--------------------------------
    Resolution: Fixed

> MySQL: JDBC-level streaming for iterators
> -----------------------------------------
>
>                 Key: CAY-3013
>                 URL: https://issues.apache.org/jira/browse/CAY-3013
>             Project: Cayenne
>          Issue Type: Improvement
>            Reporter: Andrus Adamchik
>            Assignee: Andrus Adamchik
>            Priority: Major
>             Fix For: 5.0-M4
>
>
> MySQL is one major database that ignores fetch size parameter by default (without "
> useCursorFetch=true" on the connection). So there's no API way to e.g. limit memory of huge iterated queries (think LinkMove).
> While profiling this, I discovered a couple of interesting things:
> 1. setting fetch size to Integer.MIN_VALUE switches to the streaming mode
> 2. On large queries, it doesn't simply *save memory* (which is does), it also *speeds the query itself up to 40%*. (MySQL 8.4, local Docker, driver 9.1.5)
> 3. On smaller queries, it actually makes things slower
> So, based on these findings, we will automatically switch MySQL iterated queries to the streaming mode (unless an explicit fetch size was set). This should visibly improve performance of batch queries without any additional configuration.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)