DATABASELINK TUTORIAL

Performance
This feature is not supported on the Wolfram Cloud.

Batch Operation

When large amounts of data are being transferred between the Wolfram Language and a database, you may find that the operations are slow. In this case, it may be advantageous to use a batch operation mode. If many small operations are being repeated, this will be likely to improve the performance. This section will demonstrate how to use batch statements.

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

This loads DatabaseLink and connects to the demo database.

In[1]:=
Click for copyable input

This creates a simple table. The table name is BATCH with columns X and Y. The data types for X and Y are integers.

In[2]:=
Click for copyable input

This generates data to insert into the table. X will range from 1 to 10,000 and Y will range from 1 to . The data consists of 10,000 rows.

In[5]:=
Click for copyable input

This uses Map to execute the SQL insert 10,000 times.

In[6]:=
Click for copyable input
Out[6]=

This demonstrates that 10,000 elements have been inserted.

In[7]:=
Click for copyable input
Out[7]=

This uses a batch mode to insert the data. This is done by passing a list of arguments to SQLExecute. Each element of the list contains an SQLTable expression, an SQLArgument expression with the sequence of column names, and an SQLArgument expression with the pairs of values.

In[8]:=
Click for copyable input
Out[8]=
In[9]:=
Click for copyable input
Out[9]=

The batch operation has reduced the time by more than a factor of three. This is because it has done the insert operation in one call rather than 10,000 smaller calls.

The new table is dropped and the connection closed.

In[10]:=
Click for copyable input

Simplifying Substitution Patterns

Simplifying substitution patterns is another technique for increasing performance. This will be demonstrated using a table identical to the previous example.

If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the package, as described in "Using the Example Databases".

This loads DatabaseLink and connects to the demo database.

In[12]:=
Click for copyable input

This creates a simple table. The table name is BATCH with column X and Y. The data types for X and Y are integers.

In[14]:=
Click for copyable input

Since the table and columns are always the same for this call, it is faster to place them directly into a prepared statement rather than substitute values for them each time. It is also faster to use a list for the values rather than an SQLArgument expression. This creates the data to be used for the test.

In[17]:=
Click for copyable input

Now the insert operation is carried out. This has reduced the time for the operation by a factor of more than 14.

In[18]:=
Click for copyable input
Out[18]=

This confirms that 10,000 inserts have been carried out.

In[19]:=
Click for copyable input
Out[19]=

A final performance improvement can be done by using JDBC syntax for substitutions. This limits dynamic values of the types of data that may be substituted to only Real, Integer, String, True, False, Null, SQLBinary, and SQLDateTime. It also uses '?' instead of the `1` notation (the first parameter in the list will replace the first question mark and so on).

Now the operation runs nearly 50 times faster than the original simple operation of repeated inserts.

In[20]:=
Click for copyable input
Out[20]=
In[21]:=
Click for copyable input
Out[21]=

The Wolfram Language command SQLInsert uses this last technique. When you pass a table of values as a parameter to SQLInsert, it uses the fastest way to insert the data.

In[22]:=
Click for copyable input
Out[22]=
In[23]:=
Click for copyable input
Out[23]=

This drops the table and closes the connection.

In[24]:=
Click for copyable input

Result Sets

When many rows of data are returned from a database query, they may require a significant amount of memory to hold. For your purposes, you may not need to hold all of the data. You may need to use each row individually as part of a computation or you may only need to sample the rows. In cases such as these, you may find the result set functionality beneficial. This is described in "Result Sets".

Descriptive Commands

If the database is very large, then certain descriptive commands, such as querying the number of tables with SQLTables, can be slow. In this case, if some of the tables in the database have been placed into catalogs, performance can be improved by using the or options. These are described in "Table Structure: Table Description" and "Column Structure: Column Description".