Access database INSERT out of order

[egraham]

I was encountering a problem when inserting a large amount of data (thousands of rows) into an Access database. I did a search and didn't find anyone saying they encountered a similar problem so I thought I'd share this for future reference. Maybe someone can even explain the behavior of this problem?

I was inserting time stamp and DBL data into a table in Access. I was noticing that sometimes large blocks of the data were out of order. It all seemed to be there, but the time stamps weren't always in the correct order. The problem was much worse when the Access DB file was stored on a network drive. It seemed to happen occassionally on my local drive too, but much less frequently. Move the file to a network drive to see if the problem shows itself.

I attached some test code I was using. To open it requires the Database and Connectivity Toolkit as well as the LabSQL VI's available here. The connection to the database using LabSQL requires you to configure ODBC data sources. You can see I tried three different methods and none of them were any different from the other. The VI will delete the data from the table if it already exists and then insert the new data.

Because I wanted a serverless database I switched to a SQLite database with MUCH better results. Not only will the insert times on the SQLite DB smoke an Access DB, but the file size is a fraction of the size of Access.

I'm using the SQLite tools made available from Shaun R. with great success. I'm continually impressed by all the good code made available by community members. If I could thank him personally I would.

http://www.labview-tools.com/

The moral of the story: unless I require any of the functionality of Access, avoid using it for storing data. SQLite is the way to go.

[Matthew_Kelton]

You should never be assuming insertion order to begin with.  Your database and it's connected apps should not break because of insertion order.

Regardless, I'm almost surprised that Microsoft makes Access anymore.  It must still be used quite a bit, but there are much better solutions out there.  Microsoft also has SQL Server Compact which is server-less and will work directly with the DB Connection Toolkit.

[egraham]

Breaking wasn't a problem. Having a query return data with time stamps out of order would make for a messy graph if the points were connected by lines (unless the data was sorted before plotting).

Thanks for the pointer to SQL Server Compact. It would be interesting to see some benchmarks tests between that and SQLite.

[Matthew_Kelton]

You should never assume a SELECT statement will return data in any particular order.  That's why you add the ORDER BY clause to it.  You would specify your timestamp column in your select statement and receive sorted data.

[Wart]

I would think best practice would also include using an ID field for each row you insert.  That way your order by is always independent of anything else, such as time/date format.

[egraham]

Matthew, do you know if ORDER BY slows the query at all? So far, despite it not being a guaranteed behavior, all this data I'm writing to a SQLite file is written and read in order.

Wart, there is a row ID by default. According to Matthew there should be no expected correlation between the row ID and any of the data in the rows as it's inserted.

I guess the only potential advantage to having the data insert in order is if ORDER BY adds any noticeable time to the query when reading the data.

[Matthew_Kelton]

Well, it will definitely add something, as you are doing additional work.  The DB driver will be responsible for it, so it will vary.  I know the dBase driver is horrible for sorting.  It is so bad, I do it LabVIEW instead.