SQL Server CE or Access for a portable database with c#?

Question

Hello,

I'm developing an application which needs to store large amounts of data.

I cannot use SQL Server Express edition since it requires separate installation and our target customers have already loaded us with complaints about our previous release with SQL Server express.

Now my choices are between SQL Server compact and Access.

We store huge amounts of reporting data (3 million a week). Which database can I use?

The application is portable and a product based application.

Our company is asking us to provide the application in such a way that it can be downloaded and used by anyone from our website. Please help.

Thanks.

Edit : 40,000 records within an hour is the approximate rate at which it is stored. The data stored is just normal varchar,datetime,nvarchar,etc. No Images and No binary or special stuff.

Answer 1

If you are tightly coupled to .Net : sql server compact would be a better choice.

If not, consider using: sqlite

Answer 2

What is "3 million data"/ 3 million large images? 3 million bytes? There could be a vast difference there.

At any rate, I'd probably choose SQL CE over Access if the actual data size isn't going to exceed what SQL CE supports (4GB). I've used SQL CE for applications that collect a few hundred thousand records in a week without problem. The database is a single file, is portable, and has the huge benefit that full SQL Server can just attach to it and use it as a data source, even in replication scenarios.

Answer 3

40,000 records per hour is 10 records per second. I'd suggest creating the various tables and indexes required in both and testing first. And let the test run for a solid 8 hours and see what happens.

It's quite possible that the first x records may insert reasonably well but they get slower and slower. x being some number between 10K and 1M. Slower and slower is quite subjective and depends on the app. In Access I'd suggest doing a compact on a regular basis, ie after 100K records maybe, to clean up the indexes. However if the app wants to insert records for 8 hours straight without a break then clearly this won't work well for you.

Or you could try deleting the indexes, do the record inserts and recreate the indexes. However if the users want to query on the data while the records are being inserted then this too won't work.

Also Access can work significantly faster if the database isn't shared. Again that may not be practical.

Finally if you still don't get decent performances, or even if you do, considering having the the user install a solid state disk drive and place your database file on it. A 32 Gb SSD drive for few hundred dollars buys a lot of developer time mucking around with things.