Time issue when selecting several columns

[florentmathieu]

Hello,

first thank you for this great work, the API is really easy to use and it's a pleasure the I'm now able to extract data from my colleagues' files without asking them to export to csv...

I noticed that the time needed to extract several columns at a time was more than the time needed to extract these columns separately. In my case, if I extract 6 columns at a time it takes me about 1.2 seconds, and if I extract 6 times 1 column it takes 0.4 seconds overall. In the end it can make quite a difference.

It's not a problem to do that for me, it's just a little bit less convenient and pretty to copy/paste the SELECT line. Is this something related to sqlite or to your API ?

Best,

Florent

[kyamagu]

@florentmathieu Thanks for reporting this. Probably this is due to the driver API.

There could be several reasons. Perhaps the biggest is time to allocate memory during construction. The current implementation first keeps the copy of the database rows in C++ array then converts all the array items to Matlab objects so that in the first stage the number of items can be calculated.

https://github.com/kyamagu/matlab-sqlite3-driver/blob/master/src/sqlite3mex.cc#L263

The current implementation works but inefficiently consumes memory due to the first conversion. When there are more columns, the converter takes more time to allocate memory. I suspect this is the main reason why retrieving multiple columns gets slow.

Currently there is also the official API by Mathworks. Maybe you can check if alternative implementation solves this issue?

https://www.mathworks.com/help/database/ug/working-with-the-matlab-interface-to-sqlite.html

[florentmathieu]

Hello,
thanks for the quick reply. Indeed it can explain that kind of behavior. I didn't realize there was an official API now, maybe I'll check this one. I'm quite happy with yours already, but I'll see when I find time.