Data Import Memory Management

To import data with simple queries, you can use the Database Explorer app. For more complex queries and managing memory issues, use the command line to import data into the MATLAB® workspace. To understand the differences between these two approaches, see Data Import Using Database Explorer App or Command Line.

Database Toolbox™ provides various ways to import data into the MATLAB workspace from a database.

sqlread Function

If you are not familiar with writing SQL queries, you can import data using the sqlread function. This function needs only a database connection and the database table name to import data. Furthermore, the sqlread function does not require you to set database preferences.

select Function

For memory savings, you can import and access data using the select function. With this function, you save memory by importing data using data types specified in a database. The table definitions in a database specify the data type for each column. The select function maps the data type in the database to a corresponding MATLAB data type for each variable during data import. Instead of importing every numeric value as a double in MATLAB, the select function allows the import of different integer data types. You no longer need to convert the data type of a numeric value to a specific numeric type after data import. The MATLAB memory size used by integer or unsigned integer data types is less than double precision. Therefore, the select function saves memory.

This table shows the numeric data types in a database and their MATLAB equivalents when using the select function.

Database Data TypeMATLAB Data Type

SIGNED TINYINT

int8

UNSIGNED TINYINT

uint8

SIGNED SMALLINT

int16

UNSIGNED SMALLINT

uint16

SIGNED INT

int32

UNSIGNED INT

uint32

SIGNED BIGINT

int64

UNSIGNED BIGINT

uint64

REAL

single

FLOAT

single

DOUBLE

double

DECIMAL

double

NUMERIC

double

Boolean

logical

Date, time, or text

char

For example, create a table Patients with this database table definition:

CREATE TABLE Patients(
    LastName VARCHAR(50),
    Gender VARCHAR(10),
    Age TINYINT,
    Location VARCHAR(300),
    Height SMALLINT,
    Weight SMALLINT,
    Smoker BIT,
    Systolic FLOAT,
    Diastolic NUMERIC,
    SelfAssessedHealthStatus VARCHAR(20))

These table columns have numeric data types in the database:

  • Age

  • Height

  • Weight

  • Systolic

  • Diastolic

The fetch function imports the columns of numeric data with double precision by default. However, the select function imports the columns into their matching integer data type. When you import using the select function, the corresponding MATLAB data types for these columns are:

  • uint8

  • uint16

  • uint16

  • single

  • double

The fetch function imports the Smoker column as a double in MATLAB. However, the select function imports the Smoker column as a logical variable.

To see data types after data import, use the select function with the metadata output argument.

Define Import Strategy Using SQLImportOptions Object

You can customize the import options for importing data from a database into the MATLAB workspace by using the SQLImportOptions object with the fetch function. The select function specifies the MATLAB data type by default. However, with the SQLImportOptions object, you can define the import strategy for specific database columns and specify the MATLAB data type for the corresponding imported data.

Also, you can specify categorical, datetime, and integer data types for imported data using the SQLImportOptions object. The MATLAB memory size used to store these data types is less than the memory size used for alternative data types, such as string or double.

See Also

| | |

Topics