Performance
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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
This loads DatabaseLink and connects to the demo database.
This creates a simple table. The table name is BATCH with columns X and Y. The data types for X and Y are integers.
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.
This uses Map to execute the SQL insert 10,000 times.
This demonstrates that 10,000 elements have been inserted.
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.
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.
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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".
This loads DatabaseLink and connects to the demo database.
This creates a simple table. The table name is BATCH with column X and Y. The data types for X and Y are integers.
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.
Now the insert operation is carried out. This has reduced the time for the operation by a factor of more than 14.
This confirms that 10,000 inserts have been carried out.
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.
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.
This drops the table and closes the connection.
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 "Catalog" or "Schema" options. These are described in "Table Structure: Table Description" and "Column Structure: Column Description".