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
I switched from dbWriteTable() to DBI::sqlAppendTable with odbc() driver and MySQL connector, and it's fast (6000 rows in 1 sec).