SQL Server CE or Access for portable database from C #?
I am developing an application that needs to store large amounts of data.
I cannot use the SQL Server Express edition as it requires a separate installation and our target customers have already downloaded our previous version complaints to us using SQL Server express.
Now my options are between SQL Server compact and Access.
We store a huge amount of data for reporting (3 million per week). Which database can I use?
Portable and product based application.
Our company asks us to provide the application in such a way that it can be downloaded and used by any of our sites. Please, help.
Thank.
Edit: 40,000 records in an hour is the approximate rate at which it is stored. The data stored is just normal varchar, datetime, nvarchar, etc. There are no images and no binary or special content.
source to share
What is "3 Million Data" / 3 Million Large Images? 3 million bytes? There can be a huge difference.
Anyway, I would choose SQL CE over Access if the actual data size does not exceed what SQL CE supports (4GB). I have used SQL CE for applications that collect several hundred thousand records per week with no problem. A database is one file, portable and has a huge advantage that full SQL Server can simply attach to it and use as a data source even in replication scenarios.
source to share
40,000 records per hour - 10 records per second. I would suggest creating the various tables and indexes needed for both and testing in the first place. And let the test run up to 8 hours and see what happens.
It is possible that the first x records may come in well enough, but they get slower and slower. x is some number between 10K and 1M. Slower and slower are subjective and application dependent. In Access, I suggest doing compact on a regular basis, i.e. after 100K records, perhaps to clean up indexes. However, if the application wants to insert records for 8 hours in a row without interruption, then this clearly will not help.
Or you can try dropping indexes, inserting records and re-creating the indexes. However, if users want to query for data when inserting records, that won't work either.
Also, access can be significantly faster if the database is not shared. Again, this may not be practical.
Finally, if you still don't get decent results, or even if you do, given that the user has installed a solid state drive and put their database file on it. A multi-hundred dollar 32GB SSD buys a lot of developer time, dumping everything with things.
source to share