Be the first to rate this file! 13 Downloads (last 30 days) File Size: 3.26 KB File ID: #36367
image thumbnail

Fetch (Big)

by

 

27 Apr 2012 (Updated )

Fetch command useful for SELECT queries on large data sets.

| Watch this File

File Information
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
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (4)
02 May 2012 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!

02 May 2012 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.

30 Apr 2012 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.

29 Apr 2012 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

Updates
09 Apr 2013

Added a note that this file is unnecessary in r2013a and later, as the functionality is now built in.

Contact us