The best database server

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

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

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?

I believe that is correct

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?

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.

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.

* 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

I tried to use asterisk as wildcard character but it doesn't work. I created indexes on these fields and it executes selects faster.

<<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.

<<Is this better performance adequate until you find a better solution?>>
I think yes. Thanks for all.