Thread Subject:

SQL

Dear All,

I have an application which uses the database toolbox to repeatedly query a plain text file using SQL. (Frustratingly, I can't find the code I modified to do this, but it was on the mathworks.com site. The key requirement was creating a schema.ini file, so Matlab knows which columns contain which kinds of variable).

Anyway, in total, each run of the application requires about 100 SQL queries. However, the network I'm using is pretty slow; so this is a fairly time consuming process. The question, then, is as follows. Is there a way of using SQL to query a Matlab variable (as opposed to a text file or database table). Can I, for instance, load up the data file as a cell and then point the SQL query at the cell?

Thanks,

James.

I don’t think so. Unless somebody will write a driver for it.
Running queries on plain text file is usually quite slow because
driver needs to run full file scan. I would load data into database
e.g. Access and create indices for the queried variables – this should
increase performance dramatically.

BTW, here is connection strings to a CSV file:

DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=\\server\folder

and a query

select MyVar from MyTable.csv where … and so on and so forth …

Kirill

"james bejon" wrote in message <il3hdo$a57$1@fred.mathworks.com>...
> Dear All,
>
> I have an application which uses the database toolbox to repeatedly query a plain text file using SQL. (Frustratingly, I can't find the code I modified to do this, but it was on the mathworks.com site. The key requirement was creating a schema.ini file, so Matlab knows which columns contain which kinds of variable).
>
> Anyway, in total, each run of the application requires about 100 SQL queries. However, the network I'm using is pretty slow; so this is a fairly time consuming process. The question, then, is as follows. Is there a way of using SQL to query a Matlab variable (as opposed to a text file or database table). Can I, for instance, load up the data file as a cell and then point the SQL query at the cell?
>
> Thanks,
>
> James.

If you have enough memory then I suppose you could perform the query 'select *' to import the table into MATLAB. Then, you could replace the 100 queries with MATLAB commands.

Thanks to both of you.

@Husam: Yes, true enough. I just like using the SQL because: a] it's quite easy, and b] as the application runs, it makes a log of the SQL it generates, which is then very easy to check/debug.

@Kirill: OK, I'll give that a go. I didn't realise it would make a difference, but what you're saying makes sense.

Hmmm...have changed the code so it points to an Access database (and indexed the table). Unfortunately, however, there's no improvement speed-wise.

If your CSV files is not very big, say less than 10-100MB, you may
lose time on overhead: network, compiling queries, connection to the
database etc. You may run profiler to check. In this case it would
be faster to load entire file in the memory and manipulate it with
Matlab commands.

There are also exotic solutions like mounting a disk in computer
memory, copy database there and manipulate it with SQL queries.
Should work too, I guess.

Kirill