Solved

Database Comparisons

Posted on 2000-03-17
17
1,091 Views
Last Modified: 2013-12-25
We are looking to upgrade our current VB5 / Btrieve 6.15 programs to a new or upgraded database.  I am looking for recommendations as to which DB you experts prefer, and why.

Also, one of our conditions is to minimize the conversion effort for the programming that we already have in place.  This code is based on the DAO table/index/seek paradigm.  Since ADO now has .index and .seek, comments on using your DB with these methods/properties would be appreciated.

(Yes, I know that converting to relational code is probably the way to go, but it has two problems: (1) it is slower for some cases, and (2) a four person MIS shop, with the ability to dedicate only one developer to the conversion, does not have the resources to update the number of lines of code that we have in use without some shortcuts :)

Notes:  MS Access is not an option - we already use it for some purposes, but it does not have the capacity to handle all of our data.

DB must be supported on Novell Netware (preferred) or Windows NT.  The management does not want to get into any flavor of Unix, or any non-PC based solutions.

Points:
Points will be given to more than one expert.  I will award additional points based on informational quality of the answers.  More than one answer on a given DB may recieve points.  General answers relating to a conversion may also be given points.  Points may also be awarded for links to good DB Benchmark comparisons and/or feature comparisons (preferably from sites other than the DB's home site).  Up to an additional 2000 points will be awarded for answers on this question.

Help? :)  Thanks
0
Comment
Question by:bhess1
17 Comments
 
LVL 12

Expert Comment

by:mark2150
ID: 2629637
Oracle. Expensive, but scales up really well and ports from Access with little effort.

m


0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2629737
Oracle :
1. Expensive as mentioned, but worth the money.
2. Available on multiple platforms Unix, Novell, NT, Windows 2000.
3. Reliable , Scalable and gives you good performance with large amounts of data. So when the database grows Oracle can handle it very well.
4. Majority of the development tools could be used . Eg: VB, Power Builder, VC++,C, C++,  Java, Delphi... Plus Oracle has got an extensive set of developments suites : Oracle Developer, Oracle Designer etc...
5. Very well integrated with Internet based applications too.

SQL-Server:
1. Cheap.
2. Cries a lot with large amounts of data.
3. Supported only on Windows Platforms
4. Very tedious process of tuning for performance.
5. But very easy to implement. Easy to find man power.


Best bet, considering the long run.. would be Oracle. This is my opinion.
Check out the comments of others.

Regards,
Sudhi.
0
 
LVL 9

Expert Comment

by:david_levine
ID: 2629928
I disagree with some of the above comments.

For me, MS SQLServer and Sybase Adaptive Server are brain-dead easy to install and administer compared with Oracle. In addition, Oracle requires significantly more tuning to optimize the database.

From a programming perspective, I don't think it matters much in terms of the effort required to do the conversion. I think they will all require about the same amount of work.

Unfortunately, I'm not familiar with Btrieve so I don't know how drastic the change to a relational database would be. But I'd guess that the same conversion effort to create and populate the tables in the target platform would be about the same regardless of which was chosen.

I've had 24x7x365 systems running with 500 concurrent users on Sybase and it performed well. Performing well is a result of implementing a good data model and programming for database efficiency and not always just doing something "because you can" (like populating a drop-down list with hundreds of entries).

If you want to stay on NT (I think Sybase makes a version that runs on Netware), I'd go with Sybase or MS SQL Server. I don't think you'd be disappointed.

David
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2629958
Good Point David. Thanks.
0
 
LVL 1

Expert Comment

by:Randyb
ID: 2629963
As a scale up from your current platform SQLserver may be a good bet, it is easy to install and manage and is tuned for an NT platform. Outside of that I would choose Informix over Oracle , although Oracle has a broader market share they still havent quite clued into the performance perks that Informix has used for years. Internally managed Raw disk being one such factor, Oracle is just now discovering the performance increase offered there, then Informix is just a more straight forward , easily tunable database and can handle terabyte DB's without trouble.

Oracle-Informix ...its a toss up, I lean towards informix, for your purpose SQLserver (7.0!) could be a wise choice. Of course for true advise you need to offer quite a bit more info. Number of users, size of databases, backup-restore options your looking for, redundancy issues, availability vs performance ...on and on ...

Good Luck !

Randy
0
 
LVL 32

Author Comment

by:bhess1
ID: 2630030
You're right, Randyb.  I should have offered more info on my requirements.

# of users - We currently have about 150 concurrent users, and could easily have 500 or more within a few years.

DB Size:  Currently about 1.6Gb of data in anout 100 different tables, plus another historical DB with 2.1Gb in 40 tables.  Could easily double or more in the next 3 - 5 years.

Backup-Restore options:  Not concerned at this time.  Since these are not currenty 24/7 operations, backups are handled during down time.  However, if there is something that should be known about this, then comments would be appreciated.

Redundancy:  Currently using mirroring, and are looking into RAID solutions as well.  Reliability is an issue, but not necessarily a show stopper.

Availability:  22/6 currently needed
Reliability:  High reliability needed.  Downtime due to recurring DB issues would be totally unacceptable.

Let's see, anything else?

Data Access methods:  To minimize conversion effort, the data provider should support something similar to a SEEK for rapid access to a specific record.  Performing a SELECT SQL call would require extensive re-writing of code.  Not ruling out a DB without SEEK-type access, but it is a major strike against it.

(For those who have not used the Table/Seek mentality, it allows for a quick lookup of a specific record in a given table based on one or more key values.  For example, to find a customer by name in an open table using DAO, code would be like:

CustTbl.Index = "CustName"
CustTbl.Seek ">=", LastName, FirstName

ADO (2.1 and greater) would use something like:

aCustTbl.Index = "CustName"
aCustTbl.Seek Array(LastName, FirstName), adSeekFirstEQ

)


0
 

Expert Comment

by:kthacker
ID: 2630093
Having made the transition from the Table/Seek mentality (Clipper) to SQL a few years ago, I am afraid that you will be disappointed with the results of trying to maintain that same approach on a relational database.  Regardless of which database you use, you will most likely be accessing it through ADO and ADO was designed with SQL and relational databases in mind.  My advice is to bite the bullet and convert from the Table/Seek mentality to the SQL mentality.  The sooner you do that the better off you will be.  
0
 
LVL 8

Expert Comment

by:drittich
ID: 2630424
Consider MySQL as a posssibility.  It runs on most platforms (not NetWare though), is optimized for speed, and has excellent online user support through mailing lists, etc.  I am using it with ADO via its ODBC driver at the moment and it is working extremely reliably and quickly.  The platform of choice would be unix/Linux, but it will run on any Win32 platform as well, although there is a license fee of a few hundred dollars for the windows version.  If you don't want to go relational, you can implement a flat file for now, and switch over to a relational design when you can find the time.

For some benchmark info go here:

http://www.mysql.com/benchmark.html
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 2631917
My comments will pretty much mirror those of the others above, but I'll add them to help give weight to the arguments.

I've had a lot of experience with both Oracle and SQL Server.  Oracle can work with a much higher volumn of data, and it can (more about this in a minute) be much faster in accessing huge tables.  Oracle can spread a database over multiple servers (earlier versions of SQL Server could not do this, I'm not sure about 7.0)  So, if you had a large database, you might be able to isolate tables that have a high volumn of accesses to separate servers.  Also, you can easily implement redundant mirrored servers that can immediatly come online if the main server goes down.

That said, the negatives of Oracle are that while Oracle "can" be faster, it often times is much slower.  This is because of the complex tuning that an Oracle database requires.  This tuning is not a one time thing, either.  As your tables grow or change, you'll need an expert to adjust optimizer defaults, build indexes, shared memory etc.  Don't even think of using Oracle unless you plan to have an Oracle expert on staff, full time.

One of the most annoying things about Oracle and VB is that there is no easy way to return a Recordset to VB via an Oracle Stored Procedure.  I've seen a couple of work arounds, but this is a major drawback.  Also, Oracle doesn't easily let you create temporary tables in your Stored Procs, as SQL Server will allow you to do.

SQL Server performs great straight out of the box.  In fact, it's kind of difficult to do any tuning to SQL Server.  SQL Server's optimizer overrules you on a lot of the old SQL tricks that you used to be able to pull to get an optimizer to use certain indexes etc.  Version 7.0 can now handle databases in the terrabyte (sp?) range, and I think their optimizer has gone through some upgrading as well.  

I was able to install it and build a well performing database by following simple instructions in a SQL Server book I bought from Barns and Nobel(!)  Try and do that with Oracle.

I don't think SQL Server is as reliable as Oracle, but nothing that will cause you recurring DB problems.

As far as the Table/Seek thing, you might want to check to ensure that you'll be able to use it against the database you choose.  I know that in DAO you could only use it against an Access DB, and I thought that early version of ADO were the same.  I haven't tried it recently.

I'm with everyone else on this though. Bite the bullet and do the SQL.  You can write your SQL to be just as simple as your table syntax, but it also has a lot of additional capabilities that it can offer you (in a SQL Server stored proc, for example, you can create temporary result tables, and join them to other temp tables to really transform your data)

For a company with your requirements, new to relational databases, I think that SQL Server will be a much better choice.  

Even though I think that Oracle is a much better database overall, it's very complex, and the things that are it's best features are not really that important to you.

Good luck.  And if you need to hire any consultants to get you up and running ...
0
 
LVL 2

Expert Comment

by:jkotek
ID: 2633199
As for the benchmarks - I think, that the most serious source are the TPC benchmarks (http://www.tpc.org/).
It look like your system is OLTP-based (manipulating with several rows from table, not doing comutations over one column in table (avg(salary) for all employees).
For OLTP benchmarks look for the TPC-C tests (the TPC-D, newly TPC-H and TPC-R are for DataWarehousing).
There are several recomendations I would give you:
1) go for Sybase Enterprise if you don't want a cluster
2) go for Oracle if you want a cluster
3) go for MS SQL if you areon a budget (their recent TPC-C benchmark requiered a LOT of programming in MS Transaction Server - so be prepared to do some COM+ stuff if you want to cluster MS SQL)
0
 
LVL 2

Expert Comment

by:jkotek
ID: 2633210
Just forgot to add:
Sybase has three different db servers, each for other usage.
Adaptive Server Anywhere for mobile/workgroup systems with automatic administration - your requierements are too high for this one (4 GB data, 150+ users)
Adaptive Server Enterprise - my recomendation to you - high performance OLTP database (capable of DataWarehousing too, but there is a third one...)
Adaptive Server IQ - THE database for DataWarehousing - from 10 to 100 times faster than others when doing queries over one column in whole table (=typical DW query)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2633568
Unfortunatly, i'm quite late to this question, and i could only agree on the facts already discussed.
My experiences with database cover larger applications (20-100 users;0.5-500 GB of data) using MSAccess (which is not in the choice list), Oracle 7.3 and SQL Server 6.5 AND SQL Server 7.

Oracle 7: Is a robust system, but is difficult to tune and maintain, and can fail as well. Backup/Restore operations need much experience and some facilities are missing (Stored Procedures returning a Recordset)

SQL Server 7: Is a more user-friendly system for the administrator, even newbies can quite easily perform operations like configuring a schedule for a backup procedure (using wizards)
I know that many DBA's don't like this, and I agree that one need's to know what happens behind the scenes when using such a wizard, but even that isn't too difficult. Even a dba won't ever know all of the possibilies.

I do prefer SQL Server, as it allows to do the following:
1) Use email to send notifications
2) Stored procedures can return (1 or more) Recordsets, optimizing network usage
3) Simple administration and comprehension of 'what-is-behind-the-scenes'
4) Used on NT, Security implementation is done in a moment (using Logins/Users/Roles extensively)
5) Repliation can be setup easily, even if modification of such a thing may be tricky.
6) Interface for analysis of Queries is complete (Query Analyser: Perform Index Analysis; Display Execution Plan; etc)
7) Size of Char/VARchar is 8000 (instead of 255 in SQL 6.5), so you don't need text or workaround for some fields.
8) Built-in functions for date operations like DatePart and DateDiff

 

0
 
LVL 1

Expert Comment

by:ramshank
ID: 2638256
I would go for SQL 7.0. I have a database that has 200GB of data. I am running SQL 7 on Windows 2000. I think it runs great. I process more than 8 million records per day and SQL is handling it very very well. With SQL 2000 being released soon, I am sure it is going to be better.

I came across these benchamark results for SQL 7.0 beta 3

Microsoft's SQL Server 7.0 beta 3 set new performance and price/performance records in recent ISV and TPC-C benchmarks. SQL Server 7.0 processed an incredible 41,328 checks per hour on PeopleSoft's Payroll version 7.0 and set a new record for the highest performance on Windows NT Server.


On the industry-standard TPC-C benchmark SQL Server 7.0, Enterprise Edition set another performance record. The new single-node performance record at 18,127 tpmC on Windows NT Server 4.0, Enterprise Edition is 50 percent higher than the previous four-processor Windows NT Server record held by SQL Server 6.5, Enterprise Edition (12,105 tpmC @ $32.76/tpmC on NEC Express 5800 Server). Along with 18,127 tpmC performance, SQL Server 7.0, Enterprise Edition also set a new price/performance record of $26.10/tpmC. All of these new performance results were achieved with a Compaq ProLiant 7000 6/400-1M server with four Pentium II Xeon processors


Hope it helps.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2647219
Interesting discussion about the pro's and con's of Oracle/SQL-server and others.
Can't add much to that.

I have been working with Oracle (PL-SQL and triggers & stored procedures), MS Access and I'm now starting with SQL-server 7.0.

That's the reason of my less technical contributions:

1) I think you shouldn't look only to the technical aspects of the database when making your choice. I see little or no reference to the time needed to train the department to go to work with the new database. It's my experience that knowing MS Access is making the learning curve for SQL-server very short. Even without training I could write (compose?) my first queries.

2) What do you expect for the development of the database product into the future ? Just analize the installed base and the growth of it, combined with the solidity of the company producing it.

3) How many people are trained (worldwide?) to work with the product. (The more, the better when you need extra manpower it's easier to get)

4) Don't try to use a product "against"  it's intended use.
(Have seen to many failures in that area) Thus advising you to setup the tables properly. Probably a product like S-Designer might be a help. (Or another tool that is able to create a technical datamodel from a variaty of table/record definitions, transforming it into a logical model and then back to the technical model for another DBMS and finally to generate from that the table defenitions)
5) For editing your existing code there is an overwhelming number of tools (thanks to the millennium-bug) that is able to parse and edit automatically a lot of coding.

And last but not least:
6) Choose a product that is supported on this EE-site (Okey, a bit overdone)

Good luck !
0
 
LVL 1

Expert Comment

by:guyss
ID: 2660015
I think SQL SERVER fits the requirements
they also have the DLookup function
which is ?? similar ?? to the seek function...

0
 

Expert Comment

by:NickMorgan
ID: 2663822
In my experience both MS SQL Server and Sybase Adaptive Enterprise both have performed well with the amounts of data and users that you have specified.

If it is going to be running on NT Server then the OS is more likely to crash than the DBMS.

I would probably opt for MS SQL Server as it links in better with the NT OS and is a cheaper option.
You can carry out online backups of the DB's.
I'd also go for hardware RAID level 5 on the server, or full mirroring if really required.

0
 
LVL 32

Author Comment

by:bhess1
ID: 2774629
Thanks to everyone who added to this discussion.  I'm going to be awarding a pile of points, so if you commented, look for a 'for you' message soon.

Just FYI, our final answer (yes, Regis, it is) appears to be to use Pervasive.SQL 2000.  Besides being a relational upgrade to the basic Btrieve product (allowing both ODBC and ADO access), the ADO driver supports the ADO SEEK (allowing for easier conversion of code to ADO in stages), and it is plug-and-play.... install the new DB engine, and our code runs as is, with no conversion necessary.  Update the files, and file access speed increases from 50% to 250%, still with no code changes needed.  A good answer for an undermanned MIS dep't needing to update.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now