Better, Faster Database Support in Origin 2020

Intro

Origin has database connectivity! I bet you didn’t know that. The fact is we’ve had it for many versions though we’ve never really made a big deal about it until now. Why now? Because we keep talking to customers who want to import increasingly large qualities of data from databases. So, for Origin 2020 , we decided to revisit our database features with an eye towards improving user experience and to support larger amounts of data. With the testing support of a few dedicated customers, we have been able to achieve exceptional results.

In this blog, I’ll discuss:

1. Ease of use improvements.
2. Improved speed.
3. How to actually connect to and query a database from Origin.

Note: If you simply want to learn how to connect to a database from Origin, skip to here

Ease of use improvements

For many versions, the primary method of connecting to and querying a database was via the Database toolbar. Starting with Origin 2020, we’ve hidden that toolbar and moved database connectivity under the Data menu where we have unified almost all data connectivity features in general. See illustration to the right.

In addition, once you select Connect to Database: New from the Data menu, you are immediately given the choice between using a  “Connection String” or manually creating a database connection. I’ll discuss this more later, so hang on if you don’t know what I mean.

Furthermore, connections (and even SQL queries) are easily saved to the active worksheet even if you did not actually run the query and import data. So you set the import up but put it off until later and then execute it via the database connection icon in the upper left of the worksheet (icon discussed later).

Also, when creating a connection, the settings and even query can be  applied to the active worksheet though the query isn’t actually run until you want it to.

Finally, you can now choose whether to encrypt your database credentials or not at the time you create a connection to a database. If connection credentials are not encrypted, they may appear as clear text any where a connection string is displayed. On the other hand, if they are encrypted, then the User ID and Password will be replaced by ‘***’ everywhere the connection string is displayed. Note that you can always encrypt your credentials later  in the SQL Editor dialog. Again, I’ll discuss this more later.

 

Improved speed

In Origin 2020, we were able to achieve significant improvements in the speed of pulling data from a database into Origin. Prior to 2020, Origin would attempt to update itself each time a “packet” of data arrived from the database (dates comes in a series of packets). This introduced what could be many small delays that add up to one long delay. For 2020, we reduced the frequency of these updates.  In addition, we optimized our database code for speed particularly when using ODBC data sources.

Let me throw some numbers at you based on my own informal testing. We created a test MS SQL Server 2014 database with a table containing a text column, several integral columns, and several floating point type data columns- 12 in all. It was populated with 20 million rows of data and was left intentionally unoptimized- no indexes, etc.

I wrote a query that pulls in 2 million rows  from all 12 columns using Origin 2019b and 2020. Additionally, I tested both OLE DB and ODBC. (Of course I know about database engine caches, so I primed the case by running my query a few times prior to the timing runs.) Just for fun, I threw in some figures for running he same query in Excel. Here are my results:

Version	OLE DB Speed (sec)	ODBC Speed (sec)
Origin 2019b	~40.33	~105.67
Origin 2020	~27.34	~23.06
Excel 2016*	~30	~32

The improvement in 2020 is pretty impressive. Sure, that is just one query against one RDBMS with testing type data. But we have seen similar speed improvements in other environments in-house and have reports of similar improved performance from the dedicated customers I mentioned before. I will say that can’t guarantee increased performance across the board because there are many many factors that effect performance, so if you don’t see a boost in performance for yourself, please let us know!

• While Excel’s speed was on par with Origin 2020’s, Excel has two things going against it:

1. There can only be 1 million rows in a worksheet. Origin is happy to handle many millions.
2. Excel files with connections to databases are extremely bloated size even when data is not saved with the file. By default, database data isn’t saved in the Origin project unless you want it to be, thus making the file sizes tiny in comparison.

How to actually connect to a database from Origin

The following two videos illustrate how to connect to an ODBC data source in Origin. The examples use MySQL, a highly popular RDBMS. For OLE DB connections such as to Microsoft SQL Server, an ODBC Data Source does not need to be created. Rather, the relevant OLE DB Provider can be directly selected from the Data Link Properties dialog in Origin.

 

First Step: Creating an ODBC Data Source

 

 

Second Step: Using an ODBC Data Source in Origin

 

まとめ

Well that’s it. I hope that if you need to import data from a database, this blog post was helpful.