Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

What DB should I use?

Posted on 1999-07-27
14
Medium Priority
?
208 Views
Last Modified: 2010-04-04
Hi, I'm developing a Real-Time TCP/IP Receiver and I want to put the data I receive into a database.

My first try was using the usual BDE with paradox tables.  Not surprisingly, after receiving about 2 hours worth of data, I came up with a table of 35 MB in size.  As expected, inserting into the table was VERY slow!!!!!!

The following is a list of components that I have available for this project:

ODBC98, ODBCExpress, Diamond Access, DBIsam, Flash FIller, Halycon, Apollo and Direct Oracle Access.

The following are the database servers (types) that I can use:

DBISAM, Access, MySQL (Windows / Linux), MS-SQL, Oracle, Fox Pro, Paradox, DBase.

Note:  I receive aprox 15 records per second, so I need a FAST solution!

Could you please give me comments on what you would use and why?

Thanks,

Jorge
0
Comment
Question by:jconde2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +5
14 Comments
 

Author Comment

by:jconde2
ID: 1391517
Edited text of question.
0
 
LVL 15

Expert Comment

by:simonet
ID: 1391518
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
0
 
LVL 5

Expert Comment

by:heathprovost
ID: 1391519
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:kretzschmar
ID: 1391520
yup, i am also an oracle fan ;-)
0
 
LVL 10

Expert Comment

by:Lischke
ID: 1391521
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
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1391522
Anybody tried MidWare of F.Piette?
0
 
LVL 2

Expert Comment

by:CalvinDay
ID: 1391523
A tuned (I repeat, tuned) Oracle database cannot be beat.
0
 
LVL 15

Expert Comment

by:simonet
ID: 1391524
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
0
 
LVL 7

Expert Comment

by:jconde
ID: 1391525
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
0
 
LVL 15

Expert Comment

by:simonet
ID: 1391526
>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
0
 
LVL 2

Expert Comment

by:CalvinDay
ID: 1391527
simonet,
Do you think your example would go faster with the parameter properties? (I don't know how to use it.)
0
 
LVL 15

Expert Comment

by:simonet
ID: 1391528
>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
0
 

Author Comment

by:jconde2
ID: 1391529
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
0
 
LVL 15

Accepted Solution

by:
simonet earned 400 total points
ID: 1391530
Thanks, Jorge.

Here's my comment again, now as an answer:

Go Oracle!

Alex
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

670 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