A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I'm at the end of my rope with Jet.
I've got *very* complex queries and large data sets. I need to move to something more powerful and I don't have time to mess around.
I don't need network support. This is a standalone db, located on the same machine as the app.
I do need to convert the db into a file (backup and restore)
Possible options (with the limited information that I think I know):
SQL Server - costs something to distribute; should be easy to convert; big community (books, developers, etc.)
MySQL - low cost; don't know if query optimization is any good; big community; don't know when a stable version with stored procedures is available.
Firebird - free; don't know if query optimization is good; backup files are unknown, etc.
Can anyone fill in some of the many blanks in my knowledge?
My fantasy: a full-featured db (big data, great query speed, backup files) with a big community and 0 cost.
"My fantasy: a full-featured db (big data, great query speed, backup files) with a big community and 0 cost."
In that case, I'd scratch MySQL off the list and add PostgreSQL. Query optimization is good, even for large datasets and heinous queries. Backup/restore is trivial, as well.
The only thing that's likely to be annoying is the periodic vacuuming. Rather than purge the old versions of rows in real-time, once the db realizes no existing connections need to access the old versions, it does it as a periodic process (which it calls vacuuming); this is typically the only administrative operation besides backup that you'll need to schedule. If you have a reasonable way of doing that (or even if you don't - they're building more reasonable ways of doing this into the app all the time), look into Postgres. http://www.postgresql.org
MSDE gives you the advantages of SQL Server, but without the cost. The DB size is limited to 2GB, but then you can easily upgrade to full SQL Server and if any of you customers can fill that up they ought to be using SQL Server anyway.
Friday, April 29, 2005
I forgot to add - this will be on WinXP. The postgres web site says that the windows port is not mature yet. Do you agree or are they just being extra-careful?
I'm including MSDE in "SQLServer". This definitely reduced cost - I don't need to buy it for smaller customers.
PS - one more feature in my fantasy list: encryption/security. No one should be able to tap into the db without a password.
I have no data on the Windows port. I assumed you were on a Windows system, and I knew they had a Windows port, but I didn't know they didn't consider it mature.
In the absence of further data, I'd be inclined to trust their judgment that it's not ready for prime time. If you can find someone else with data that it's stable enough for you, that might be good enough, but I'm not that person. :)
If you are considering the free MSDE then you might also look at its successor, SQL Server 2005 Express Edition:
It's the free edition of SQL Server 2005 and, even though it is pre-release, you can use it in production today:
Saturday, April 30, 2005
"The postgres web site says that the windows port is not mature yet. Do you agree or are they just being extra-careful?"
All previous versions of Postgres were all iffy on Windows. The latest version is Windows native (no Cygwin) and been heavily tested. I'd say they are being extra-careful because this essentially the first version for Windows but it honestly should be fine.
I'd consider it first over MySQL and Firebird. MySQL is great, stable, and whatnot but, from the type of usage you describe, it would drive you insane. Firebird is alot like Jet in that's very old and has lots of strange inbuilt limitations.
I would rule out MySQL versions <= 4.1, as other posters suggested.
You could try MySQL 5.0. It's in development status though -- not release status, but in my experience they fix bugs and anomalies really fast; and even the development versions are very stable (I never recieved a crash or wrong data in a MySQL development version...)
What I would do is download MySQL 5.0, install it, get a snapshot of your data moved over, and then try as many complex queries as you can to test if it will be suitable.
If it doesn't meet your expectations, then try some of the other fine DB's suggested. If the size of your data will always be less than 2GB, then MSDE Express might be your ticket. I like PostGres too, but I don't think it's as widely used as MySQL (but it has great transactional abilities). Also, MSDE and MySQL you can buy support contracts, though there might be some 3rd party offering PostGres support.
What else? Encryption. I couldn't tell you. I can tell you that what you're encrypting and how you're encrypting it could have a huge performance impact regardless of DB.
For example, if you encrypt last name, and then perform a query where you want all of the last names between 'L' and 'N', inclusive, depending on the encryption method, you might have to read *every* last name, decrypt it, and see if it's the one you're looking for... So, that's something to consider...
Anyway, I hope you find the DB that is best suited to your needs.
"Yes it is old...but how is that a bad thing?"
Anybody remember Windows 95? DOS?
"What are the "strange inbuilt limitations"?"
There are limitations on key size. Maybe that's not a problem, but this page exists to find out:
It might not be a pratical problem but I find it a bit distasteful.
It depends largely on what your front end is written in and whether its separated into the three general layers. If its VBA to Jet then the simplest migration would be to MSDE, then you would get the performance enhancements of the DBMS.
However, you have to bear in mind two things. With a single machine using VB you're still basically in a single wrapped application rather than client server and if you are using VB you aren't really going to get as big an improvement as you'd hope for as VB isn't data centric.
Personally, and this is a bias from experience and given you don't have the same experience it may well be exactly the wrong thing to do, I would reengineer in Visual Foxpro, not particularly for the database, that could be in MSDE or native VFP, but because VFP is a data centric language and also gives me the N-tier separation that also removes (or at least minimises), the dependance upon any particular DBMS.
Sunday, May 01, 2005
Oh, and MSDE is being replaced with SQL Server 2005 Express, Emperor's new clothes and all...
Sunday, May 01, 2005
What about Cloudbase from IBM? It's mature and Open source, but it's Java-based, which you may not like...
Sunday, May 01, 2005
The key limitation in firebird can be anoying.
Version 2.0 fixes this problem though:
A few here have mentioned MySql, but it is not free for commercial use
Also, MySql has very poor support for scripting (is there even a server side language out yet?).
And, triggers etc. requires the inno extensions (at least this is now part of the disto).
The free sql-express edition from MS seems about the best choice. It supports triggers, t-sql (for server side procedures), and even supports the running of .net code on the server side (that is sooo cool!!). The throttle, and user limitations that the old MSDE had are now removed.
Max data database size is now 4 gigs.
So, you can pay to use MySql, or you can get a free sql server with the following features:
Some features from the web site show:
Snapshot isolation level
Advanced query optimizer
Native XML datatype
Support for structured and semi-structured data
Transactional replication subscriber
Merge replication subscriber
Support for distributed transactions
Hum, for a free engine (like JET is), the express edtion seems darn good. You could however purchase MySql, and use that also.
Edmonton, Alberta Canada
Albert D. Kallal
Sunday, May 01, 2005
Oracle Personal Edition? $500 a seat, but it's the real deal -- full Oracle functionality
Monday, May 02, 2005
Thanks everyone for excellent advice.
I went ahead with SQL Server/MSDE (I decided I just don't have any time with a possibly tougher migration).
We've already put in two long days (many complex queries, lots of vestigial stuff in our VB code to move to stored procedures, etc.). I expect another two.
But this stuff is amazing! I was an extreme skeptic on the benefits of moving from Access. So far, SS rocks.
One extra benefit - SS files are a third the size of my humongous Jet files (about the size of the compressed Jet files). I'm not surprised that the data file works more efficiently, but I did not think about how many more customers I can handle with MSDE. This path seems much more economical than I thought.
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz