topic Re: Save to Database is too slow (10k records per minute) in Discussions

Save to Database is too slow (10k records per minute)

jumper — Mon, 01 Dec 2014 13:49:09 GMT

I have a JMP table with 270,000 records and 72 columns.

I am trying to save it to a database via ODBC but the process is painfully slow: it is about 10,000 records per minute (yes, per minute, it's not a typo).

I tried to export to both a Microsoft SQL Server 2014 and a PostgreSQL 9.3 server: the speed (or lack thereof...) is the same in both cases. Also, both servers are in the same building as the workstation I am running JMP from. I am the only user with access to the two servers, so I know they are not busy with anything else.

I thought the slow speed could be due to JMP trying to figure out the length of the text fields, so I removed all the text columns and left only numeric fields, but the speed is the same.

If I retrieve a table of similar size from either database via ODBC it takes less than 20 seconds.

ETL tools writing to the two databases via ODBC or JDBC are much much faster.

Any thoughts on how I could make writing to the databases faster? As of now the function is so slow it's practically unusable.

Thank you.

Re: Save to Database is too slow (10k records per minute)

pmroz — Mon, 01 Dec 2014 15:11:14 GMT

You could export the JMP dataset to a csv file, and then use the SQL Server bulk insert functionality. A bit of a kluge but it should be much faster.

Re: Save to Database is too slow (10k records per minute)

jumper — Mon, 01 Dec 2014 15:44:59 GMT

PMroz wrote:

You could export the JMP dataset to a csv file, and then use the SQL Server bulk insert functionality. A bit of a kluge but it should be much faster.

Thank you. I had in fact tried something similar. However, I'd really like to know if there is a way to make 'save to database' faster, i.e. feasible, since right now it's so slow it's unusable.

Is it something to do with the ODBC settings/drivers (but JMP is fast at retrieving data from the databases), or is that just the way JMP is?

Re: Save to Database is too slow (10k records per minute)

briancorcoran — Mon, 01 Dec 2014 19:09:10 GMT

The emphasis in JMP development has been on getting data into the product. We have put considerable time into getting "block" fetching mechanisms working for Open Table, but we do not have a similar mechanism for Save Table. We have an open request to do this, but cannot commit to a specific implementation date.

Brian Corcoran

Re: Save to Database is too slow (10k records per minute)

jumper — Mon, 01 Dec 2014 19:55:45 GMT

BrianCorcoran wrote:

Brian Corcoran

which effectively means that the 'save to database' option is utterly useless for tables of more than a few thousand records. If only the specs of this expensive software were clearer...

Re: Save to Database is too slow (10k records per minute)

thomasz — Tue, 22 Mar 2016 19:36:15 GMT

It is now year 2016 and several versions later. And I can confirm slowness, but in my case I get 200 records per minute. Having 16.064.704 records, it will take 2 months before submitting them to the database. Cancel button doesn't work and the only way out is killing the process.

Please, do something about it - with so much place for improvement, it shouldn't be that hard!

Re: Save to Database is too slow (10k records per minute)

msharp — Fri, 28 Oct 2016 13:15:20 GMT

I never used the Database>Save Table option. Actually I didn't know it existed till reading this post, so I wrote a bunch of my own functions of common tasks. While I feel they are slow, they at least can boast speeds better than anything in this thread.

Here's my BulkInsert function:

//Insert Table to Database..Table --assumes ncols matches

BulkInsert = function({db, dbTable, dt}, {Default Local},

//db - database connection

//dbTable = database table

//dt - JMP datatable

numCols = NCols(dt);

numRows = NRows(dt);

for(i=1, i<=numRows, i++,

valuesList = {};

for(j=1, j<=numCols, j++,

If(isDate(column(dt, j)),

insert into(valuesList, char(Format(column(dt, j)[i], "ddMonyyyy h:m:s")))

insert into(valuesList, column(dt, j)[i])

);

valuesText = substitute(char(valuesList), "{", "(", "}", ")", "\!"", "'");

Substitute Into(valuesText, ".,", "NULL,");

SQL_string = "Insert Into " || dbTable || "\!N" ||

"Values " || valuesText;

Execute SQL(db, SQL_string);

);

You'll need the isDate function I wrote that you can find here: Is there an easy way to know if a column is Date/Time?

Or you can comment out that portion of the code if you don't have any date/time columns.

Re: Save to Database is too slow (10k records per minute)

bryan_boone — Thu, 24 Mar 2016 17:21:20 GMT

There have been quite a few improvements in JMP 13 concerning database interaction. One of which is the Database>Save Table option.

In JMP 13, multiple rows are now inserted (Bulk Insert) for databases which support it (which are most of them). JMP 13 database interaction improvements also include preserving column names and case (when possible) as well as preserving TIMESTAMP information.