0

I'm seeing a 300-fold performance penalty, depending on which drivers I use, and the fast one is unavailable on shinyapps.io.

I'm reading a CSV into a tibble using readr::read_csv, then using dbWriteTable() to upload it to my MariaDB database. If I use odbc::odbc() with the latest MariaDB connector, it takes 1-2 seconds to load 6000 records, which is great. However, that connector is not available on shinyapps.io, which is where my app will be deployed. The MariaDB drivers available there are 1) odbc, with the MySQL connector, or 2) the RMariaDB driver. When I use either of those drivers (running either locally or on shinyapps), the same load takes 8-10 minutes!

Is there a faster way? My Shiny app lets the user select a CSV to upload, the largest of which will be 10-15k rows. The RMariaDB package maintainers suggest using load_data_local_infile = TRUE when connecting to the database, but my hosting provider has that feature disabled. :(

Any suggestions would be appreciated! Matt

MattB
  • 15
  • 5
  • 1
    Use `LOAD DATA ...` from MariaDB so that it is read directly into the data base without going through R. – G. Grothendieck Nov 07 '22 at 17:29
  • Thanks @G.Grothendieck. LOAD DATA LOCAL is disabled on my hosting platform (lithiumhosting.com), so when I use `dbConnect(load_data_local_infile = TRUE)`, then `dbWriteTable()`, returns the error `The used command is not allowed with this MariaDB version` (which [turns out](https://mariadb.com/kb/en/load-data-infile/) to not be an accurate message). LOAD DATA (without LOCAL) is also unavailable because I don't have access to the MariaDB Server file system. – MattB Nov 07 '22 at 18:40
  • MariaDB-10.7+ has [improved bulk load perfromance](https://mariadb.org/10-7-preview-feature-innodb-bulk-insert/), but I suspect your provider isn't offering that either. And your provider won't change a setting to improve your performance? I'd find a new one. – danblack Nov 07 '22 at 21:29
  • Thanks for sharing that, @danblack! I think you're right; my hosting plan is very inexpensive, and I am looking into other options. Regarding InnoDB Bulk Insert: If I understand your linked article correctly, that improvement should happen automatically for large INSERT … VALUES SQL, or a LOAD DATA SQL statements on 10.7+, correct? – MattB Nov 08 '22 at 16:03
  • Yes, there's the constraint that this is for new tables that are empty. [ALTER TABLE EXCHANGE PARTITION](https://mariadb.com/kb/en/alter-table/#exchange-partition) may help if they form a logical part of a larger table. – danblack Nov 08 '22 at 20:47

1 Answers1

0

I switched from dbWriteTable() to DBI::sqlAppendTable with odbc() driver and MySQL connector, and it's fast (6000 rows in 1 sec).

MattB
  • 15
  • 5