The best database server

I want to develop a delphi application that uses databases with thousands of records. Which is the best database server I can use in Delphi? I tried Microsoft.Jet.OLEDB.4.0 but I don't like the response time and I don't want to use sql server?
Do you know a better database server or other tools for managing data?
sprinkenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BlackTigerXCommented:
that's a religious question, MSSQL, MySQL, Oracle, etc

you use whatever you need use, or whatever you can afford to use

thousands of records can be very few or a lot, it depends on how many thousands you are talking about

but all of the major players (mentioned above) can handle millions of records just fine

if you have, say less than 100,000 you can manage to handle them easily with just a desktop database, such as MSAccess
BlackTigerXCommented:
you have to evaluate all the factors that affect the database, how many users will you have, how many selects, inserts, updates, deletes will you have, etc
sprinkenAuthor Commented:
I have over 700,000 records and many selects and when I make a simple select it takes a few seconds until I get the result. For this reason I put this question, because I saw applications developed in Delphi that manages data very fast. For example, i tried "Where is it" loading the catalogs with hundreds of thousands of records and when I make a search it returns the result pretty fast. I wonder how is managed the data in this program?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

rdytmireCommented:
I use SQL Anywhere, Oracle 10g, and MSDE / SQL Server 2003 in my work.  For small database like the one you have described (and believe me 700,000 records is no big deal by todays standards) may I suggest the SQL Server 2005 Express Edition?  I love MSDE for smaller projects and SSEE looks like a nice replacement for MSDE.  It's fast, easy to use, well documented and did I mention FREE.  Free is a good thing.  You can always move up to bigger and better stuff as you grow your dataset.  Personally, while Oracle makes me a lot of cash, I find it cumbersome to use compared to MS's stuff.  (Using it in 99% of applications is sorta like sand-blasting a soup cracker...too much trouble for what you're trying to accomplish).


Stay away from Paradox, Access, Foxpro....just asking for trouble...Also, while MySQL looks inviting it is not really a true RDBMS...might be worth your time if your app is very small but I tend to stay away from it for various reasons.

Just my 2c of course



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlackTigerXCommented:
I would agree with rdytmire, MSDE is great and is free, however you have the limitant that you cannot have that many concurrent users

about the problems with speed selecting rows, if you post your selects here or in the SQL section, I'm sure someone will help you optimize them, when  you do selects is usually best to use
select whatever fields from YourTable (nolock)
now, you need to know where your bottleneck is, if it's on the database, network or your application
rdytmireCommented:
Actually, the limitiation in MSDE is not a maximum concurent users.  It is maximum concurent transactions.  I think the old limit was 4.   Any more transactions were placed in a queue.  Also I think it will only use 1 processor, no matter how many are available.

I have had 32+ connections to an MSDE engine with no issues at all.

The idea is that if you need to process more than 4 transactions in, oh, 10msec of time then you've probably outgrown MSDE and need to buy a real RDBMS.  

I'm pretty sure Oracle's free offering works in much the same manner.

BlackTigerXCommented:
I believe that is correct
aikimarkCommented:
Another important bit of information we need to know...is the database going to reside on a desktop PC or be used only by one person at a time or be a true server-level product?

I would add to the list already supplied (depending on your environment):
* Interbase -- Borland is currently looking for VARs
* Firebird -- the opensource IB alternative (FREE)
* Advantage -- blazing performance figure claims
* Some of the ISAM-format databases for single users have good performance and a very small footprint, both runtime and install package size.
* DB2 -- universal database from IBM.  This database product started as one of the first RDBs back in the mid-80s.  It's rock solid and also is freely available for personal use while you do development on your PC.  They are pricing themselves below SQL Server which is already below Oracle.

===========================
Other facts:
1. MSAccess isn't really a database 'server' so you will experience network delays bringing the data across the wire.  The number of hops and network speed will affect your response time greatly.
2. MSAccess can be tweaked to provide decent response time, but you need to identify where you might be spending the bulk of your time.  For instance, MSAccess will create indexes on many columns without your knowledge.  Some of these indexes will help performance and others just take up space and slow the optimizer's decision process.
3. MSAccess also slows down when it gets fragmented.  Repair & Compact the database before doing performance testing.
4. Your queries can also affect how the database engine (and its optimizer) will perform.  Different SQL expressions perform better/worse in MSAccess.  This applies to all databases.  Perhaps your query might be tweaked.
5. Database update (Insert, Update, Delete) activity can cause performance problems.  Moreover, these queries aren't traditionally good performers.
6. Layers help.  Since MSAccess isn't a true database server(1), you would need to add a middle tier component to your configuration to simulate a server product.  Likewise, you might benefit from an additional layer on an SSEE/MSDE configuration by separating direct access to the database by multiple application programs.
sprinkenAuthor Commented:
Thank you all. I am beginner in database programming and I appreciate your help.
However my program is not a very complicated one and it doesn't require a network connection. I use databases just for an easy data managing, so that I don't think it is necessary to install a database server such as MSSQL or others on every computer I run my application. I think BDE or Interbase will be enough for my application. If I use BDE what driver do you think is more suited for a simple database?
aikimarkCommented:
sprinken,

Thank you for the points.  However, you should wait until your question has been answered before closing a question thread.

Please help us by answering the following:
1. What kind of data are you storing?
2. What kind(s) of information are you trying to extract from the data?
3. How much does the data change?
4. What version of Delphi are you using now?
5. Do you want to facilitate migration of your application to the .Net framework?
6. Do you have plans to create applications for small devices (PDAs/phones) via the .Net Compact Framework?

==========================
My view of the BDE is that Borland has already planned its funeral.  Most Delphi2006 literature references dbExpress, IBX, and the ADO layers.  I could be wrong, so you might want to open a separate EE question on the future of BDE.

There are some BDE alternatives for desktop applications.
* Absolute Database
* TurboDB (.Net)
* AidAim SQLMemTable
* EasyTable
* Accuracer Database System
* NexusDB 2 Embedded & NexusDB 2 Lite
* kbmMemTable
* VistaDB 2.1
* SQLMemTable
* DBISAM

===============================
Depending on your answers (above), you might be able to roll-your-own database using regular Delphi components.  Look at TClientDataset and classes with a LoadFromFile method.

===============================
In an earlier comment, I mentioned the Advantage database.  They were acquired by Sybase earlier in the year and are part of the iAnywhere division.  I find it  interesting that Sybase/iAnywhere would replace their Watcom database engine with the Advantage database engine.
sprinkenAuthor Commented:
aikimark,
here are the answers to your questions:

1. What kind of data are you storing?
Almost all of the fields in my tables are of text type.

2. What kind(s) of information are you trying to extract from the data?
I extract information only from the text fields. Here is the problem. When I make a select for ex: 'Select * from MyTable where MyField="value"'
the response time is good but when I make a select like
'Select * from MyTable where MyField like "%value%"'
the response time is longer(a few seconds)

3. How much does the data change?
The data is not changed very much. After I insert it, I rarely change or delete some records.

4. What version of Delphi are you using now?
I'm using Delphi 7 Enterprise.

5. Do you want to facilitate migration of your application to the .Net framework?
I don't think it's necessary.

6. Do you have plans to create applications for small devices (PDAs/phones) via the .Net Compact Framework?
No.
aikimarkCommented:
* Your example query highlights one of my earlier points about the effects of the query and the database on performance.  In this case, you are looking for a string anywhere within a text field using wildcard characters.  One thing I should mention at the outset is that MSAccess (Jet) engine expects the asterisk (*) wildcard character.  This conversion might cause some slowdown.
Example:
'Select * from MyTable where MyField like "*value*"'

* Secondly, MSAccess doesn't perform very well on a substring search where you aren't necessarily looking at the beginning or ending characters of the field.

* Since you are searching through a table of variable-length character fields, the database engine has more work to do than look through/past fixed length fields.

* You didn't mention whether these are regular text fields (<=255 characters) or memo fields (up to 2GB).  Regular text fields process much faster.

* Are there any indexes on these (regular text) fields?  If so, the database engine can look at the contents of just the field you are searching instead of looking past every non-searched field in every row.  This is one way that indexes help speed processing.

* If you are looking for blazingly fast speed and it is acceptable to limit the searched text to individual words, you might consider one of the following:
  + implement a Key Words In Context (KWIC) infrastructure, where you create a separate table of words and the record ID where they occur.  You will likely ignore trivial words such as "a", "an", "the", "I", etc.  Define a unique two column index on this KWIC table, so you won't have duplicate entries for the same word/row combination.  You would allow the user to select the words they wanted to search for.  No free-form user typing.

* use the DTsearch engine (http://www.dtsearch.com) as a replacement
sprinkenAuthor Commented:
I tried to use asterisk as wildcard character but it doesn't work. I created indexes on these fields and it executes selects faster.
aikimarkCommented:
<<I tried to use asterisk as wildcard character but it doesn't work.>>
The back-end database must be in MSDE format, since it looks/acts like a SQLServer database.

<<I created indexes on these fields and it executes selects faster.>>
Is this better performance adequate until you find a better solution?

Good luck with this application in 2006.  If you need more assistance, open a new EE question.
sprinkenAuthor Commented:
<<Is this better performance adequate until you find a better solution?>>
I think yes. Thanks for all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.