• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

Database talk: why use one over the other?

bigred

Penultimate Amazing
bigred
Joined
Jan 19, 2005
Messages
22,696
Location
USA
The .NET discussion was lively enough, so this should get real interesting :) I think we've hit on this here and there elsewhere, so pardon if this is a duplicate, but anyway....

Seems to me for "smaller" and/or simpler stuff, Access is more than enough (note to purist database snobs: stop rolling your eyes!).

Beyond that, SQL (esp SQL Server, it seems) is very popular. Of course Oracle is the other "biggie," but there are others of note, eg Informix or what have you.

So - again please try to keep personal bias on simmer and objectively state what you KNOW - why one over the other? I'm esp. interested in hearing from those who have worked with a variety of diff. ones on a professional level, although any *informed* opinion is welcome.

My experience is quite limited these days (although if we were still back in the 80s boy....). Access does seem fine to me for smaller/modest apps (anyone have a rough idea of its limitations?). I've used SQL Server in a limited way, ie as a BA....don't care for it much, but that's probably at least in part because I'm not really much of a techie anymore.

Some ideas about why one over the other I thought of:

Cost
Power/speed
Ease of use/friendliness (ie to code/maintain)
Portability
Reliability
 
It depends on what you have to do.

If what you're doing is simple enough that you could probably do it with flat files, and there is a tight connection between the program that's running the query and the database, probably Access is OK, though it has problems with multiprocessing.

SQL Server is OK for medium-sized things. It requires very little maintenance.

Oracle is good for large things. It requires a lot of tweaking and maintenance. Subqueries and decode are good for optimization. There are features in Oracle, such as CONNECT WITH, that you will only need about 1% of the time, but when you need it, you really need it.
 
bigred said:
Seems to me for "smaller" and/or simpler stuff, Access is more than enough (note to purist database snobs: stop rolling your eyes!).
Click to expand...
It doesn't take snobishness to appreciate that a storage medium that is prone to corrupting data is a poor choice for storing data of any importance. For this reason alone, Access fails in its primary purpose.

It has many nifty features as a front-end tool but as a database it's worthless.
 
Scalablity is a big issue, Oracle scales better than MSSQL.

Access at least the older version are not to be trusted in a multi user enviroment. Yes, you can do it, but it's has reliablity issues.

If you are in need of personal database, MSSQL is easier to use, as epepke said. Or you could try MySQL, open source database, with lots of nice tools.
 
epepke said:
It depends on what you have to do.

If what you're doing is simple enough that you could probably do it with flat files, and there is a tight connection between the program that's running the query and the database, probably Access is OK, though it has problems with multiprocessing.

SQL Server is OK for medium-sized things. It requires very little maintenance.

Oracle is good for large things. It requires a lot of tweaking and maintenance. Subqueries and decode are good for optimization. There are features in Oracle, such as CONNECT WITH, that you will only need about 1% of the time, but when you need it, you really need it.
Click to expand...

Agree. I would add, for smaller apps that MySQL may be a better choice than Access+VB.

My experiences and rating:
MSSQL 6.5 - Okay. (ERP system)
MSSQL 7 - Better. Pretty average (ERP system)
MSSQL 2000 - Much better. Great choice for mid-size applications. (ERP system)
Oracle 9iR2 - Great for big apps. Powerful. Easy transition (in a dba/dev role) from MSSQL to Oracle (worldwide client/server game)
MySQL - indirect experience, positive comments from those that were directly using it
Access - ugh
PostgreSQL - Good for small apps, and open source/free

I've found Oracle to be much better at making the minutea admin stuff readily available. MS, in an effort to keep it user friendly, hid a lot of the stuff. Which had its postitive and negative sides.

Edit to add: At least one acquaintence of mines _raves_ about FileMaker, but I have no experience with it.
 
MySQL is usually touted for it's speed, and criticized for it's non-ACID compliance (although I think they're supposed to be getting close).

Postgres is usually touted for it's ACID compliance.

Open Source apps, especially web ones, usually write for MySQL first, and someone patches in Postgres support if it's popular enough.

BTW, Microsoft has released SQL Server 2005 Express edition. It's a free (as in beer) database server with most of the features needed for low to middle end apps. It's designed to compete with MySQL. Similar to MSDE but easier to distribute with your own app (and with fewer features removed from the full SQL Server than MSDE had).
 
IllegalArgument said:
Nice article, but I was more commenting on Oracle's ability to handle large amounts of data.
Click to expand...

Yeah, but that isn't always needed. Part of the process of selecting a database is knowing roughly how much data you're talking about. I wouldn't select Oracle to run my web site because the data we're talking about is infintesimal.

I don't even think it's need to run these forums with significantly more data.
 
IMO SQLServer's managment tools are much easier to use than Oracle's and hte default development environment for TransactSQL is much better than for PL/SQL
 
IllegalArgument said:
Nice article, but I was more commenting on Oracle's ability to handle large amounts of data.
Click to expand...
There are production examples of multi-terabyte systems on each of the major DBMSs.

A big scalability issue in the 80's and 90's was limitations in query optimizers. Ingres was way ahead of the pack for a while, despite terrible marketing. Then of course, Computer Associates took them over in '92(?), a significant portion of the company walked out the door, and they were rarely heard from again. *sigh*

In the 90's, Oracle eventually got "reasonable enough" so my job as a DBA (after initial design and ongoing operations support) was to review programmers' SQL, instead of stand over their shoulders with a sharp object like I used to. All it took was a slight change in the order of the WHERE clause and performance went to Limbo, Purgatory, or Hell, depending upon the query.

SQL Server 2000 (and now 2005) has finally made me publicly admit that SQL Server is no longer a toy. I've been a DBA using all three products for 20 years - ok, SQL Server obviously not that long). Performance of anything but the most stupid SQL is good enough to concentrate on being productive, not on keeping the system alive. I now do terabtye ETL, Data Warehousing, and Data Mining without praying to the SQL deities to give me the query performance I need. Yes, OLTP and even BI systems still require expertise, but the infrastructure knowledge required has become a much smaller portion of the system and operational costs. I am down to 4 moderate to senior DBAs handling a load that would have taken dozens even a decade ago.

So that brings us right back to the points made by several others - IT DEPENDS! WHAT DO YOU NEED TO DO WITH THE TOOL? Now you get into the religious wars about which has a better ETL tool, which has better total cost of ownership, etc.

My personal take is that if I want a BI/DW/DM system up and running quickly, I will spend my money on SQL Server. I cannot speak to the latest tools relating to high availability OLTP, web development, middleware, or any of the many other things you might want to do with a DB.

YMMV,

CriticalThanking
 
varwoche said:
It doesn't take snobishness to appreciate that a storage medium that is prone to corrupting data is a poor choice for storing data of any importance. For this reason alone, Access fails in its primary purpose.

It has many nifty features as a front-end tool but as a database it's worthless.
Click to expand...
I question that. I haven't built a lot with it, but I have used Access databases a great deal, and have had very few problems. As stated, it may be largely a size thing.
 
IllegalArgument said:
Scalablity is a big issue, Oracle scales better than MSSQL.

Access at least the older version are not to be trusted in a multi user enviroment. Yes, you can do it, but it's has reliablity issues.
Click to expand...
Um definite "multi-user environment" - if that simply means more than 1 person using it, again I disagree. We've had them at work with a variety of users and though the scope was pretty small/simple, it worked fine.
 
epepke said:
It depends on what you have to do.

If what you're doing is simple enough that you could probably do it with flat files, and there is a tight connection between the program that's running the query and the database, probably Access is OK, though it has problems with multiprocessing.

SQL Server is OK for medium-sized things. It requires very little maintenance.

Oracle is good for large things. It requires a lot of tweaking and maintenance. Subqueries and decode are good for optimization. There are features in Oracle, such as CONNECT WITH, that you will only need about 1% of the time, but when you need it, you really need it.
Click to expand...
OK can you guys get more specific on what is (roughly) meant by "small/medium/large things" pls. ie put some ballpark metrics behind it? Thx
 
bigred said:
ETL? [tim the toolman voice] uhhhhh? [/tim the toolman voice]


see TT comment above

ditto

English if possible pls. :)
Click to expand...

Extract Transform and Load - moving data from an OLTP to an OLAP system
On Line Transactional Processing - a database defined for performance on Inserts/Updates/Deletes
as opposed to OLAP - On Line Analytical Processing - a database tuned for Reads
 
bigred said:
Um definite "multi-user environment" - if that simply means more than 1 person using it, again I disagree. We've had them at work with a variety of users and though the scope was pretty small/simple, it worked fine.
Click to expand...

multi-user usually means more than one person at the same time. You need a pretty good database to handle simultaneous writes/reads for data. Access is poor for this above more than about 2 people.
 
bigred said:
OK can you guys get more specific on what is (roughly) meant by "small/medium/large things" pls. ie put some ballpark metrics behind it? Thx
Click to expand...

Small: A medium-sized website, a Mom 'n' Pop, a regional office.

Medium: 2000 employees, maybe 10 or 20 million rows in the largest tables, 50 gigs total data.

Large: Bigger than that. Terabytes of data.
 
Good stuff here folks, thx

What about others like INFORMIX, DB2, SAP etc? Anyone?
 
You must log in or register to reply here.

Back
Top Bottom