MATLAB: Database query is slow in R2018a

databaseDatabase Toolboxfetchbatchsizefetchinbatchesrowincrunsqlscript

I was using in R2016b the following code to connect to an oracle database:

>> conn = database.ODBCConnection(prod,serv,pswd); 
>> setdbprefs('FetchInBatches','yes') 
>> setdbprefs('FetchBatchsize','10000') 
>> data = runsqlscript(conn,'SQL_query.sql');

It used to take 2 seconds.

Now that I tried to run it in R2018a it doesn't recognize the 'FetchInBatches' or 'FetchBatchsize' commands so I'm just running the code as

>> conn = database(prod,serv,pswd); % aparently .ODBCConnection syntaxis is depricated 
>> data = runsqlscript(conn,'SQL_query.sql');

The query takes 3 minutes now!

I haven't been able to find in the documentation how to make my query faster. Could you please let me know how to fix this and make my query run fast again?

Best Answer

In R2016b, you used _setdbprefs _to fetch data in batches.

Retrieving data in batches reduces overall retrieval time.

Now, setdbprefs('FetchBatchSize','1000') and _setdbprefs('FetchInBatches','yes') _have been deprecated in R2018a (Refer Release Notes for more details).

So, while querying database, it is taking more time than it used to.

You can use '_*RowInc*_' nave-value pair in runsqlscript.

_*RowInc *_is row limit indicating the number of rows to retrieve at a time, specified as the comma-separated pair consisting of 'RowInc' and a positive numeric scalar.

Use this name-value pair argument when importing large amounts of data. Importing data in increments helps reduce overall retrieval time.

By default, the _runsqlscript _function imports all rows of data from the executed SQL statements. The value 0 specifies to import all rows of data.

data = runsqlscript(conn,'SQL_query.sql', 'RowInc', 1000);

Hope this helps!

Related Solutions

MATLAB: Am I getting the error “Beyond SQL_ACTIVE_STATEMENTS limit” when querying from SQL database with Database Toolbox

"Beyond SQL_ACTIVE_STATEMENTS limit" means that your application has tried to create more "hStmt"s by ODBC driver than the database is allowed.

It probably means that you are creating a new "hStmt" each time you submit a new query, but you are not releasing these "hStmt"s after the queries complete.

Please make sure to close the cursor object after the the query is completed with the "close" function as shown in the example below:

>> sqlquery = 'SELECT * FROM inventoryTable ORDER BY productNumber';
>> curs = exec(conn,sqlquery,'CursorType','scrollable');
>> curs = fetch(curs,'AbsolutePosition',10);
>> data = curs.Data;
>> close(curs);

MATLAB: How to execute a complex set of SQL commands at once with Database Toolbox

The EXEC command only returns a result set if the first SQL command is SELECT or the name of a stored procedure. Since the above example began with a declaration, no result set was returned.

There are a number of ways to accomplish this task. Some of these options include:

1. Save the batch of SQL commands as a stored procedure in the database.

2. Use one EXEC command to prepare the data and store it into a temporary table. Then retrieve the data from the temporary table with a simple SELECT statement.

3. Use the database's built-in stored procedure for evaluating a string as SQL commands. For example, Microsoft SQL Server has a built-in stored procedure called sp_executesql. This function will execute any string and return the results of the command to Database toolbox. For example, in Microsoft SQL Server semicolons (;) are used to separate statements so you can execute a batch program as follows:

>> s = 'sp_executesql "declare @id as int;set @id = 2;select * from testtbl where id = @id"'
>> e = exec(conn,s)
>> e = fetch(e)
>> e.Data

4. Another possible workaround is to use 'runsqlscript' command. Create a .sql file with list of SQL statements separated by ';'. For example, .sql file can look like below.

/* .sql file begins */
declare @supplierNumber as int;
set @supplierNumber = 1000;
select * from suppliers where supplierNumber = @supplierNumber;
/* .sql file ends */
>> conn = database('test','<username>','<password>');
>> results = runsqlscript(conn,'test.sql')
>> close (results)
>> close(conn)

Best Answer

Related Solutions

MATLAB: Am I getting the error “Beyond SQL_ACTIVE_STATEMENTS limit” when querying from SQL database with Database Toolbox

MATLAB: How to execute a complex set of SQL commands at once with Database Toolbox

Related Question