Fetch (Big)

Fetch command useful for SELECT queries on large data sets.

Description

This fetch command is useful for SELECT queries on large data sets. First, it runs a query to determine the number of rows being returned, then preallocates the space necessary for the input query's result, then runs the main query, fetching the results in batches.

This allows pulling much more information from a database in a single query than the built-in methods (by using batches to avoid using too much of the Java heap) and does this much faster (by preallocating space for the output).

Note for r2013a: This functionality is now built into the 'fetch' command in the Database Toolbox. See the documentation for that command.

• Required Products: Database Toolbox; MATLAB
• MATLAB release: MATLAB 7.14 (R2012a)
• Other requirements: Database accessible with ODBC or JDBC driver; Statistics Toolbox in order to use dataset type

[Tucker McClure]

Good point, Robin. The fetch_big code has more overhead than regular fetch and therefore is only faster on larger queries. Try it with 10,000 rows, and I bet you'll see a big difference!

[Robin Jens]

Thanks Tucker

I tried your code an compared it with the code I normally use when I import data from an Access database to matlab.

Your code:

conn = database('DATABASE1', '', '');
setdbpref('DataReturnFormat','cellarray');
y = fetch_big(conn, 'select * from TABLE1');
test=y(:,[1 2 3 4]);

It took around 0.3seconds to import a cell array with the dimensions 200x20 with numeric data and strings.
Elapsed time is 0.032313 seconds.
Elapsed time is 0.027668 seconds.
Elapsed time is 0.028188 seconds.
Elapsed time is 0.032034 seconds.
Elapsed time is 0.028520 seconds.

My old code:

conn = database('DATABASE1', '', '');
curs=exec(conn,'select * from TABLE1')
setdbprefs('DataReturnFormat','cellarray');
curs = fetch(curs);
y=curs.Data;
test=curs.Data(:,[1 2 3 4]);
close(curs)

My old code seems to be faster. Is it because it is only a small amount of data I import and therefore preallocate does not have any impact on the time?

Elapsed time is 0.016508 seconds.
Elapsed time is 0.015066 seconds.
Elapsed time is 0.015458 seconds.
Elapsed time is 0.015469 seconds.
Elapsed time is 0.015922 seconds.
Elapsed time is 0.015577 seconds.

[Tucker McClure]

Hi Robin,

It takes care of the cursor stuff, so the code is actually much simpler.

conn = database('DATABASE1', '', '');
setdbprefs('DataReturnFormat','cellarray');
y = fetch_big(conn, 'select * from TABLE1')

Also, you can always try 'help fetch_big' for much more information! Please let me know how this works for you.

[Robin Jens]

Dear Tucker

I have a question. Assume I have an Access database. The name of the Access database is DATABASE1. In this database I have different tables. I want to import all column in the table called TABLE1. How should the code look like if I want to use your function?I tried:
%conn = database('DATABASE1', '', '');
curs=exec(conn,'select * from TABLE1');
setdbprefs('DataReturnFormat','cellarray');
curs = fetch_big(conn,curs)
y=curs.Data

Best Regards

Robin