Which DB Server should I use?

I have to develop a new (desktop) app for a small business. This business currently has an Access database with millions of records. The file size is about 1.5 GB. The boss told me that searching on this DB is very slow. The DB consists of a single table with about 20 fields.

I also think the overall DB design isn't great. I thought to use another DB server with a new design to improve both perf开发者_Python百科ormance and efficiency.

Considering this is a relatively small business, I don't want to spend much for a DB license, so I want to ask you what would you do.

• Continue to use Access, maybe improving and optimizing the DB in some way
• Buy a DB server license (in this case, which one?)
• ? (any idea?)

Things like SQL Server Express, MySQL and PostgreSQL are available for free, no license purchase necessary.

For improving search speeds, you will probably also want to look at things like what indexes are defined for the table, what exactly searches are doing, et cetera.

2nd the recommendation for Firebird. We've been using it for about 5 years and never had an issue. Cross platform, embedded & server deployments... brilliant. Oh, and Free as in Beer. Mozilla Public LIcense.

Put me down as another recommendation for Firebird. We use it with our commercial Point of Sale product. We have it installed at over 1,000 sites, with databases as large as 40+ Gigabytes. It's fast, stable, simple, easy to deploy, and requires no management.

Your could replace the Access Database with a SQL Server database that will scale well moving forward. You can use SQL Server Express which is free and supports databases up to 4Gb I beleive.

SQL Server Express. Free for database size up to 10 GB.

SQL Server Express is a perfect fit for this. http://www.microsoft.com/express/database/

I warmly recommend MySQL. Its sometimes free and is easy to install on both Windows and linux.

There are also a lots of great free tools to manage its content like tables, users, indexes etc...

I would look into if the big table needs to be broken up into smaller ones(rarely needed, but still) and also what indexes are on it. And for Database software I would recommend PostgreSQL. It is free, easy to use(and I consider it easy to setup, though others beg to differ), and it is fast enough for enterprise applications.

You can take a look at Firebird

Firebird is one of the best database for desktop application and will allways be free.

Some tools exist to convert database from access to firebird.

I also recommend Firebird.

Its key advantages for your scenario are (from top of my head):

• embedded version. You can ship it with your application - no separate installation kit needed, no .NET dependencies etc.

• later on you can scale seamlessly to the full client-server model. No code changes required.

• very small footprint

• the entire database is stored in a single file. Much easier to deploy compared with other solutions.

• you can have your server on any platform you want: Windows, Linux, MacOSX etc. Of course, you can have your client also on the same platforms but since you mentioned Access, I suppose that you have a Windows application.

• no need for server administration. It just works.

I recommend PostgreSQL as well (especially as an alternative to MySQL)

No-one ever seems to mention it, but Oracle also do a free-as-in-free-beer version of their database: Oracle Express Edition (aka XE). It is limited to 1 CPU, 1GB RAM and 4GB of user data but that sounds plenty big enough for your application.

As for your database design, just one table sounds more like a spreadsheet than a database application. Probably you have lots of denormalised data. Splitting those out into smaller de-duplicated tables might well speed up certain queries. However if you only have twenty columns there may not be a lot of scope for tuning.

As for recommendation, the question is, which products do you know? If you have a familiarity with Access then I suggest you try to optimize your existing database. I have worked with Access databases which store several million rows and they performed well enough. After all, there is no guarantee that moving the same design to a different product will automatically make things run x times faster. Another advantage of Access as a tool is that it comes with a built-in front-end tool. If you move from Access you may need to think about re-building your application.

Wow, folks have a lot of platform recommendations for you.

Let me just say this.

If you feel like there are design issues as well as platform issues, why not try the design changes first? These are changes you are likely to make anyway.

If they make no performance difference in Access, you are no worse off, since these changes improve maintainability on any platform.

Then you can try other platforms with the knowledge that you have a solid design, and that you have not wasted any time.

SQLite can also be a candidate.

I would recommand not going with express anything - yep your small but what if the busness takes off and need much higher loads in the future - surely thats the way you want it to go ?? do not want to have to switch db layers / run 2 ..

I would look at MySQL or Firebird (used to borland interbase) both very high quality.