Database toolbox: How can I avoid using a cursor?

Hey guys.

I'm having a problem with the database toolbox. So here is my situation:

I open a connection to the database. This connection remains active till I close the figure/program. For data import I have to use the database toolbox (not the visual query builder), because the users don't know how to handle SQL queries.

The data I'm fetching from the database is around one million rows, two columns per query. When I first execute the cursor exec statement, it takes about half a second. Here is the code:

curs=exec(conn,['Select EKG,unique_unix_timestamp from DATABASENAME where subjectId=XX order by unique_unix_timestamp asc`

The execution time should be fast as unique_unix_timestamp is a key and has an index on it. For fetching data, I use rowlimit until no more data is available:

while 1
curs = fetch(curs, rowLimit);
if rows(curs)==0
break
end
data = [data;curs.Data];
end

After that, I close the cursor until the user wants to load data from the next subject. My problem is: Every time I execute the cursor statement (not the fetch), it takes longer (from two s in the beginning to 5 minutes after two-three queries). Do cursors remain open even after closing them? Is there a way to view all open cursors? How comes the execution time is so slow (in contrast to actually fetching the data)? I have tried to close the cursor, close cursor and clear the cursor, manually execute garbage collection, nothing helps. I don't know what else to do and hope you can give me any hints. Here is what I do to clear the data.

close(curs);
clear('curs');
java.lang.System.gc()

When tracking the java heap space, it goes from 100mb to 300mb after each cursor execution and falls down to 100mb after garbage collection. The only thing that changes is how steep the java heap space increases..

I hope you guys understand my situation and can help me.

Thanks, Tom

update: I have added an image from my jconsole tracking. Here we go:

[Oleg Komarov]

Are you the only one using the DB?

[Thomas]

Not necessarily all the time, but at this time, yes

Answer 1

[Thomas]

update2:

My db preferences are as follows:

              DataReturnFormat: 'numeric'
                 ErrorHandling: 'store'
                NullNumberRead: 'NaN'
               NullNumberWrite: 'NaN'
                NullStringRead: 'null'
               NullStringWrite: 'null'
            JDBCDataSourceFile: 'C:\XX\database-source.mat'
         UseRegistryForSources: 'yes'
      TempDirForRegistryOutput: 'C\XX\AppData\Local\Temp'
            DefaultRowPreFetch: '10'

Hope anybody can help here? Don't know what else to do. Even splitting the execution query in small parts with MySQL Limit doesn't help..

Answer 2

[Greg]

I highly suggest that you open and close the connection as needed, instead of opening once and re-using the same connection until you close the program. Yes, it takes a second or two to open the connection, but in my experience (with Oracle) this is the only way to ensure consistent performance.

Simply write a function that returns the connection, then do this every time you fetch data:

conn = getDatabaseConnection;
sqlQuery = getSqlQuery;

curs = exec(conn, sqlQuery);
curs = fetch(curs);

% Use the data returned

close(cursor);
close(conn);

Answer 3

[Lionel Trebuchon]

Dear colleagues, the solution proposed by Greg does not work anymor, as the exec function does not return a cursor object anymore and the fetch function cannot work with cursors anymore either (I think since R2018b).

Does anyone have a suggestion that does not involve trying to adjust the queries with an "OFFSET" clause?

Thanks, Lionel