What DB should I use?

Edited text of question.

Here's what I can tell you, based on my previous experience:

Oracle is the most reliable and fastest database as long as

- you do not use ODBC;
- your SQL scripts are optimized for Oracle
- Use Direct Oracle Access (or even BDE's native driver)
- your Delphi code is well written
- your netork connections are fast and reliable

Paradox is ok (easy to use and all), but not reliable enough. I don't trust Pdx for crucial applications where the security of data is a matter of life and death. I'd use Oracle (or perhaps MS SQL).

If you use SQL scripts and stored procedures as much as possible, Oracle can beat any of the databases you mentioned, and that's a documented fact, not my personal opinion.

On the other hand, if your budget is tight, then I'd suggest MySQL with a well confured Linux RH 6.0 box.

I both cases a good TCP/IP connection is essential, since the physical aspect of the network is often the bottleneck I've found in database applications.

Between MySQL and Oracle, I'd stick with Oracle. Besides the obvious reasons, a very important one is that with MySQL you'd have to stick with ODBC. Anything that uses ODBC nowadays will be terribly slow. I mean HORRIBLY SLOW. ODBC is old technology and it surprises me it's still being used. OTOH, if you use Oracle, you have several options to use it with Delphi in such a way that the  access to the DB is made directly by the application or by BDE.

Yours,

Alex

I agree with Alex on almost all counts.  Oracle, using native drivers and well structured queries, is MUCH faster than just about any other DB Server solution.  I have seen comparison that measure it over 1900% faster than ODBC connections under some circumstances, and 400%-500% overall.  Other DB Servers also have native driver support (interbase comes to mind) but Oracle is a well supported and prolific server that is built to take a beating.  I have nothing against other servers, we personally use MS-SQL for most of our production servers, but that is mainly a cost constraint.  If money were no object, and performance was paramount, we would definitely go with Oracle.  Since you say you have access to it already, I would give that as my recomendation.  If you want a fast local database engine, I would have to recommend Access DBs using Diamond Access or a similar DAO interface.  It is much faster than Paradox (in my experiance) and much more scalable than using btrieve or dbase.  I have personally used Diamond Access and consider it a great component.  But if I had to chose between a local DB, or a server-based DB, I would go server based everytime.  They are just overall more reliable.

Heath

yup, i am also an oracle fan ;-)

Oh it seems you never have really worked with Interbase Server. This one is really fast, has a very small foot print, is well scalable, very easy to manage and is MUCH cheaper than Oracle. Using special components (IB Objects in this case) you can even avoid using the BDE and improve so the speed again very much.

Just my $0.02 :-)

Ciao, Mike

Anybody tried MidWare of F.Piette?

A tuned (I repeat, tuned) Oracle database cannot be beat.

jconde, why not use Oracle 8i and forget about the Delphi stuff? Write everything in PL/SQL and Java and you'll get the best and fastest stuff in db technology our time has to offer!

It will be even faster if you run it on Linux.

Alex

Wow!!!!!!!!!!!

Never would of thought 99% of the comments where Pro-Oracle!

Alex, I can't go for linux in this case, It must be all developed under windows (Including the DB Server)  As a matter of fact, both of the Oracle (7 & 8) versions I have are for WinNT ;)

I can't go for PL/SQL and JAVA because this project is time-critical too and I'm no expert in neither of them!  It would take me at least a month to get aquainted with them and that would mean a severe money loss to the company!

I personally liked the Diamond Access solution, but I will not be able to use that one since the company I work for uses Office 2K and diamond access doesn't work for that version!

Middleware sound like a good solution Zif, but I guess if I where to use any MIDAS/MIDDLEWARE solution I'd go for ASTA! All the reviews say it's great!

I did some testing last night:

for i := 1 to 1000000 do
  begin
    table.append;
    table.fieldbyname('Text_Field').asstring := 'Text';
    table.fieldbyname('Number_Field').asInteger := i;
    table.post;
  end;

I tested ODBC, DBISAM, BDE W/Paradox and SQL_Server and much to my surprise the BDE solution was the fastest.  I will definitley not use DBISAM nor ODBC!!!!!!

I code the same with native-API calls for MySQL today and I will also Install oracle to check Direct Oracle Access out!

If any of you have used Direct Oracle Access, could you please tell me how to code the above please (I've never used it) ?


Thank you very much for your input!

Best regards,

Jorge

>BDE solution was the fastest

Just as I said, using native drivers is the best way to use RDBMS from Delphi. Your routine will get even faster if you DO NOT USE TTable component to access Oracle.

Just use something like this:

const
  TEST_INS_ORA = 'INSERT INTO MYORACLETABLE (Text_Field, Number_Field) VALUES ("%s", %d)';
var
  i : integer;
begin
  for i:=1 to 10000 do
  begin
     Query1.Sql.Text := Format(TEST_INS_ORA, ['Text', i];
     Query1.ExecSQL;
  end;
end;

The code above can be up to 100% faster than the TTable-based code. The ratio of improvement depends on a few other factors. As I said, if you use mostly SQL, you'll have a greater improvement on the overal performance of your application. Just do the test with the code I provided and compare the results.

Yours,

Alex

simonet,
Do you think your example would go faster with the parameter properties? (I don't know how to use it.)

>Do you think your example would go faster with the
>parameter properties?

No. Doesn't make much difference, since the data being passed to the RDMBS back-end will be the same. You'll actually be adding the overhead of the component re-reading the SQL statement and putting the parameters in. I think the way I showed is the best way for *small* and simple SQL statements. Now, if we are talking about an SQL statement that is longer, then using parameters is a better deal.

The important thing there is to get rid of the overhead caused by the TTable component on Oracle. As I've, and I repeat, the more you use SQL and the least you use TTable on a Oracle database, the more performance you'll get form it. The same goes for Interbase, DB2, MS SQL and all SQL-based DBMS's.

Alex

I guess this is more than enough!

Thanks a lot to all of you!!!!!!!

Alex, since you where the 1st to suggest Oracle, can you post an answer please?

Again, thank you experts!

best regards,

jorge