Topic: Excel vs ACCESS vs NO SQL vs OLEDB?

[ds22L]

For starters, using Excel and AutoCAD datalinks is...horrible slow. Interop.Excel of any kind is an unfortunate solution when handling named ranges. If all someone needs is static references to an excel sheet, then OLEDB is many magnitudes faster. The only sacrifice being for those who need non static named ranges to be resolved.

A faster solution would be to "create" the statically referenced version of Excel directly from your programming language of choice but at that point you might as well explore database options. Not to mention drift. I prefer not to deal with drift in my spreadsheets. 2 years of drift and the current spreadsheet vs version 1 just aren't compatible anymore. To many incidental changes (even though structurally they are very near similar). Or combine OLEDB and Interop.Excel (ew)

Which brings me to ACCESS and NOSQL. For all practical purposes, I have no experience with ACCESS and I am not generally a fan of SQL and tables. Above all, I don't like thinking about JOIN operations. I prefer to pre-plan my DB structure and tune it before deploying it. I like massive pools of data with intelligently made indexes. I don't see a solution directly within VANILLA AutoCAD that allows me to interact with a NOSQL database like MangoDB or DynamoDB. Data access times over the net to a secure DynamoDB is pretty fast, even when handled behind an API/SQS/Lambda setup so this shouldn't be a concern.

Anyone thought of or actually abandoned the SQL/Excel options currently available? Correct me if I am wrong, but isn't it as simple as creating a function that can add data from a reference (let's say an API or AWS SDK), tag that data with an XRecord of the reference, either the direct reference or a map; and a separate function to update any added data when it finds one of those XRecords? Or create an object that holds the ID's of the objects that have been referenced and their reference?

Maybe some handling for data input and output shape being variable. But that doesn't seem all that big of a deal.

Moving to more modern cloud based infrastructure would be extremely beneficial to our communication with customers, sales reps, managers etc. The separation behind an Excel sheet just doesn't work. You end up having to write queries to pull the data out of all the sheets and with drift always being a factor, it ends up being more of a data munging task than anything. The vast majority of our tools are online using AWS or Google.

Any examples of what you implemented as your solution?
Anyone just suck it up and deal with excel? Anyone take that middle ground where you pragmatically create the excel file with static named ranges?

I am currently using excel but am in a position where I have the time and motivations to replace it. Advice?

DS

[It's Alive!]

I’ve done a ton of performance investigations with different tools to import data into CAD. COM, SQLite, My SQL, Access… from a C++ perspective.
The fastest, by far is directly reading the .XLSX file, i.e., this https://github.com/troldal/OpenXLSX can read 4,000,000 cells per second. This is raw data, if you need fonts, cell properties, ..
There’s a few very well written libraries like this for .NET that would be orders of magnitude faster than datalinks.

Never used NO SQL, but if you want a local server, SQLite is a great choice, the speed can be several thousand queries per second.
SQL servers are great as you can store blocks , images, or other blob data. In C++ SQLite can be embedded directly into an ARX, for crushing performance, and best of all, no drivers
MS is always screwing with access drivers... toxic...

MySQL is also a great choice if you’re planning on setting up a small server, using ODBC, I was getting about a 1000 queries per second over a network.
SQL times were calculated 10 rows per query, so that’s fast enough where you can’t feel a lag.

Lots of options.

SQLite blocks https://www.theswamp.org/index.php?topic=58135.0
ODBC Field https://www.theswamp.org/index.php?topic=57864.0
XLSX Field https://www.theswamp.org/index.php?topic=56095.0

[ds22L]

Hmm, I took a look at the library but I couldn't find any hints for resolving formulas.

" // Nota that OpenXLSX does not calculate the results of a formula."



I hope it is an old note in the code...This is unfortunately not something I can get away from in excel. To many input/output shape variability. OFFSET(COUNTA), OFFSET(INDEX), LAMDA() + circular logic enabled, is the only way to solve some problems internally in Excel.

Interop.Excel just keeps popping up

I'm leaning more and more towards a database setup...I know it will be in a different magnitude of speed compared to a local read with c++ with the call over the network...But, coverage is important for me here. A one size fits all solution for pushing Data to AutoCAD on a project basis is necessary.

I currently have a VBA in excel that pulls the projects information from the DB into excel for all the calculations, and then I sit there for 1 minute in AutoCAD while it updates. I waste min 10, max 30 minutes every day or 2.5-7.6 days every year waiting on DATALINK updates. I could have spent that time to implement a solution y1 

[It's Alive!]

If you are using excel to evaluate expressions at runtime, then OpenXLSX / SQL is not right for you.
It’s a big constraint I missed in your original post.

Excel evaluates formulas on save/close, OpenXLSX (and other XLSX readers) read the last evaluated result.

You can run some evaluation stuff in SQL statements, but you probably won’t get the full power of excel.
I only mention C++, because that what I tested with. There’s SQLite, MySQL and XLSX reader libraries for .net.

Interop isn’t that slow, every time datalink update is run, it has to create a new instance of excel, which is pretty heavy lifting.

It’s not clear to me what your doing, so it’s hard to give advice

[57gmc]

I've used MSSQLExpress for years. It come with VS Community. Its file based, but acts just like big brother. But it does have some limitations, none of which affected me. You can use MSSQL Server Management Studio to manage it's instance. With it you can write stored procedures to mimic xl functions (possibly) and call the sp's from the VS db entities.

I used Access for years, but often had issues with data access with more than one user.

[n.yuan]

Correction: SqlExpress is not "file based". It is exactly the SQL Server with some features stripped, such as analysis services, reporting services...It is installed to your computer as services, which can be accessed from other computers in the network, if configured properly.

It seems the OP really need Excel's built-in computing power, thus, choosing any other data storage would require home grown computing features to be incorporated. And yes, with SQL Server/Express, stored procedures would a good tool to build internal computer function into the data storage.

The original question (rather, questions) is really difficult to respond because the main objectives are not that clear: if the slowness is the main concern, using data link to populate table from Excel obvious is the cause. So, the question is: does the sheet in Excel do a very complicated calculation that it is very hard to be developed/programmed in other means? In most cases, it would be not, I'd say.