Board index » delphi » Access 2.0 much faster querying databases than Delphi?

Access 2.0 much faster querying databases than Delphi?

First problem)
U have been using program made in Access 2.0 to search database of about
100.000 peoples. Usually is known only first part of name (i.e. John to
find John, Johnson, ). Sorting is necessary once by Lastname + Firstname,
second by Firstname + Lastname.
It seems to me, that only Access is so clever that he need two indexes but
each one field used only once per index. I mean, 1st index is only on
Lastname, 2nd is only on Firstname. No matter which one combination of
sorting I need, Access successfully uses and combines both and it works.
With Delphi it seems that I need two indexes, too, but both on both fields.
1st on Lastname+Firstname, 2nd on Firstname+Lastname.

Second problem)
I've found that some queries in Delphi (no matter if using Paradox 7.0 or
Visual dBase format) are incredible slow, compared to Access.

If I want find John, Johnson, etc. in Access SQL I specify:
"SELECT * FROM XY WHERE XY.NAME LIKE "JOH*'".
Doing so, I get all names too fast to measure time.
Using same SQL with Delphi 32-bit (using "%" instead of "*" of course) it
takes about 3 seconds on the same machine.

SQL's "SELECT * FROM XY WHERE XY.NAME = "JOHN'" (using "=" and EXACT known
name) are more equalized in sense of speed, but useless for my purpose. It
seems to me, that Delphi is unable to optimize SQL, containing LIKE
operator not mater if "*" (or "%" in Delphi) is present only at the end of
SQL. Using "*" ("%") at both ends of "SELECT * FROM XY WHERE XY.NAME LIKE
"*JOH*'  are equally slow in Access and in Delphi. This is normal, because
of two "**" ("%%") engine is unable to use indexes.

Such program in Delphi is useless for doing decades of searches daily.

I would prefer using Delphi over unstable VB but with Access MDB format.
However, this format is, I guess, purposely ignored by Borland and ODBC is
terrible slow. If I want buy separately some library to access MDB format
directly it cost at least additional $250.

Any suggestion?

Thank you

 

Re:Access 2.0 much faster querying databases than Delphi?


Quote
Marjan Pregelj wrote:

> First problem)
> U have been using program made in Access 2.0 to search database of about
> 100.000 peoples. Usually is known only first part of name (i.e. John to
> find John, Johnson, ). Sorting is necessary once by Lastname + Firstname,
> second by Firstname + Lastname.
> It seems to me, that only Access is so clever that he need two indexes but
> each one field used only once per index. I mean, 1st index is only on
> Lastname, 2nd is only on Firstname. No matter which one combination of
> sorting I need, Access successfully uses and combines both and it works.
> With Delphi it seems that I need two indexes, too, but both on both fields.
> 1st on Lastname+Firstname, 2nd on Firstname+Lastname.

In DBase tables, you index on any field or combination of fields that
you want,
and those, as a unit, go into one file.  DBase tables are capable of
sub-second
searches for the type of queries that you are doing on tables that
contains
hundreds of thousands of rows.  I suspect that you are not using the
indecies properly.

Quote
> Second problem)
> I've found that some queries in Delphi (no matter if using Paradox 7.0 or
> Visual dBase format) are incredible slow, compared to Access.

Again, your indexes are not set up correctly.  Delphi and DBase will
blow
the doors off of Access.

Quote
> If I want find John, Johnson, etc. in Access SQL I specify:
> "SELECT * FROM XY WHERE XY.NAME LIKE "JOH*'".
> Doing so, I get all names too fast to measure time.
> Using same SQL with Delphi 32-bit (using "%" instead of "*" of course) it
> takes about 3 seconds on the same machine.

> SQL's "SELECT * FROM XY WHERE XY.NAME = "JOHN'" (using "=" and EXACT known
> name) are more equalized in sense of speed, but useless for my purpose. It
> seems to me, that Delphi is unable to optimize SQL, containing LIKE
> operator not mater if "*" (or "%" in Delphi) is present only at the end of
> SQL. Using "*" ("%") at both ends of "SELECT * FROM XY WHERE XY.NAME LIKE
> "*JOH*'  are equally slow in Access and in Delphi. This is normal, because
> of two "**" ("%%") engine is unable to use indexes.

This causes a table scan and will be slow with any table type (excepting
SQL Servers.)

Quote
> I would prefer using Delphi over unstable VB but with Access MDB format.
> However, this format is, I guess, purposely ignored by Borland and ODBC is
> terrible slow. If I want buy separately some library to access MDB format
> directly it cost at least additional $250.

If you are going to use Delphi, I recommend the Paradox tables, if you
need RI, or the
DBase tables if you are doing a simple application.  You WILL NOT suffer
from performance
using these types of tables if you use indexes correctly.  

If you chose to use Access tables, or FoxPro, or ODBC sources,
performance will
significantly suffer with any numbr of rows from 10,000 to 100,000.
Native table
access in Delphi is excellent, and SQL Server access is incredibly
impressive.  Be
sure that when you run your queries that you are using the correct
indexes.

Good Luck,
Jason Perry.

Re:Access 2.0 much faster querying databases than Delphi?


Oliver, if you want to access the full Microsoft database offering using
Delphi take
a look at www.wizzkids.com/ariel.html. The tools you see there were created
exactly
for this reason. The group are all Delphi/C++ engineers who were frustrated
by being
turned away by clients who had heavy investment in MS Access etc. With
Ariel you
can support any database technology you choose.
Regards
Jeffrey Shapiro.

Oliver Townshend <oli...@zip.com.au> wrote in article
<32DE98C7.4...@zip.com.au>...

Quote
> Marjan Pregelj wrote:

>  SNIP

> > I would prefer using Delphi over unstable VB but with Access MDB
> format.
> > However, this format is, I guess, purposely ignored by Borland and
> ODBC is
> > terrible slow. If I want buy separately some library to access MDB
> format
> > directly it cost at least additional $250.

>  Purposely ignored by Borland because Microsoft specifically only made
> it available via ODBC, so they are the people you should complain to
> (and they will probably tell you to use Visual Basic).  But maybe Delphi
> 3.0 will support Access better - who knows?

> Oliver

Re:Access 2.0 much faster querying databases than Delphi?


Quote
Marjan Pregelj wrote:

 SNIP

Quote
> I would prefer using Delphi over unstable VB but with Access MDB
format.
> However, this format is, I guess, purposely ignored by Borland and
ODBC is
> terrible slow. If I want buy separately some library to access MDB
format
> directly it cost at least additional $250.

 Purposely ignored by Borland because Microsoft specifically only made
it available via ODBC, so they are the people you should complain to
(and they will probably tell you to use Visual Basic).  But maybe Delphi
3.0 will support Access better - who knows?

Oliver

Re:Access 2.0 much faster querying databases than Delphi?


In article <32DDA2D4.2...@erols.com>, Jason Perry <jmpe...@erols.com>
writes

Quote
>Marjan Pregelj wrote:

>> First problem)
>> U have been using program made in Access 2.0 to search database of about
>> 100.000 peoples. Usually is known only first part of name (i.e. John to
>> find John, Johnson, ). Sorting is necessary once by Lastname + Firstname,
>> second by Firstname + Lastname.
>> It seems to me, that only Access is so clever that he need two indexes but
>> each one field used only once per index. I mean, 1st index is only on
>> Lastname, 2nd is only on Firstname. No matter which one combination of
>> sorting I need, Access successfully uses and combines both and it works.
>> With Delphi it seems that I need two indexes, too, but both on both fields.
>> 1st on Lastname+Firstname, 2nd on Firstname+Lastname.

>In DBase tables, you index on any field or combination of fields that
>you want,
>and those, as a unit, go into one file.  DBase tables are capable of
>sub-second
>searches for the type of queries that you are doing on tables that
>contains
>hundreds of thousands of rows.  I suspect that you are not using the
>indecies properly.

>> Second problem)
>> I've found that some queries in Delphi (no matter if using Paradox 7.0 or
>> Visual dBase format) are incredible slow, compared to Access.

>Again, your indexes are not set up correctly.  Delphi and DBase will
>blow
>the doors off of Access.

>> If I want find John, Johnson, etc. in Access SQL I specify:
>> "SELECT * FROM XY WHERE XY.NAME LIKE "JOH*'".
>> Doing so, I get all names too fast to measure time.
>> Using same SQL with Delphi 32-bit (using "%" instead of "*" of course) it
>> takes about 3 seconds on the same machine.

What *would* be the correct way to set up indexes for such a query,
using Delphi with either dBase or Access via ODBC?  Perhaps I am missing
something, but I too have found VB/SQL/Access somewhat faster than
Delphi/SQL/dBase or Delphi/SQL/ODBC (I am comparing Delphi 2 with 16-bit
VB 4, querying a 10,000 record table).

Having said which, for this type of single table query on an indexed
field (or fields) with Delphi/dBase, the "SetRange..." functions will
surely give much better performance than SQL.

--
Max Wright

Turnpike evaluation. For information, see http://www.turnpike.com/

Re:Access 2.0 much faster querying databases than Delphi?


In article <01bc03fd$29ec6a20$4f7b2...@ppp06611.hpp.com>,
   "Jeffrey R. Shapiro" <j...@wizzkids.com> wrote:

Quote
>Oliver, if you want to access the full Microsoft database offering using
>Delphi take
>a look at www.wizzkids.com/ariel.html. The tools you see there were created
>exactly
>for this reason. The group are all Delphi/C++ engineers who were frustrated
>by being
>turned away by clients who had heavy investment in MS Access etc. With
>Ariel you
>can support any database technology you choose.
>Regards
>Jeffrey Shapiro.

Jeffery,
       Any chance of some clues as to pricing ?

I've trawled the web site and (unless I missed something), there was no mention
of a price structure, just a thing to fill in and let your sales staff get in touch.

Now, like most programmers and people who do real work ;-), I regard sales staff
as fairly low on the evolutionary chain and would rather they didn't bother me,
hence I'm inhibited from considering your product.

pip pip

Dave Langstaff | d...@augusta.co.uk or d...@aber.ac.uk
Any views expressed are those of the author alone. | Phone:(01970)626001
After-life, After-shave, don't hold with any of it, bleugh!  - Sir Henry

Re:Access 2.0 much faster querying databases than Delphi?


Quote
Dave Langstaff wrote:

> In article <01bc03fd$29ec6a20$4f7b2...@ppp06611.hpp.com>,
>    "Jeffrey R. Shapiro" <j...@wizzkids.com> wrote:
> >Oliver, if you want to access the full Microsoft database offering using
> >Delphi take
> >a look at www.wizzkids.com/ariel.html. The tools you see there were created
> >exactly
> >for this reason. The group are all Delphi/C++ engineers who were frustrated
> >by being
> >turned away by clients who had heavy investment in MS Access etc. With
> >Ariel you
> >can support any database technology you choose.
> >Regards
> >Jeffrey Shapiro.
> Jeffery,
>        Any chance of some clues as to pricing ?

> I've trawled the web site and (unless I missed something), there was no mention
> of a price structure, just a thing to fill in and let your sales staff get in touch.

Besides the newsgroup announce, I also found the same Ariel letter among
my e-mail. I can only say that I got _exactly_ the same feeling when
reading Ariel's home page; no mention about the price. I felt
frustrated to being forced to write an nice e-mail message, "please,
tell me how much does this all cost".
   I got my answer in a couple of days, and if i remember it right, the
start level for price was something like 350$. I'm not sure for that,
not so interested, and there is no place in Web where I could check
it.

My opinion is, that marketing through Internet is more or less to say
"here is my product, and it will cost this much."  Why bother possible
customers to send e-mail letters just to ask for the price.  (Sounds
like an insignificant thing, but I still have some not-positive feelings
against the company mentioned above)

Markku Nevalainen

Re:Access 2.0 much faster querying databases than Delphi?


Quote
>If you chose to use Access tables, or FoxPro, or ODBC sources,
>performance will
>significantly suffer with any numbr of rows from 10,000 to 100,000.
>Native table
>access in Delphi is excellent, and SQL Server access is incredibly
>impressive.  Be
>sure that when you run your queries that you are using the correct
>indexes.

Which SQL Server do you use?. I am looking for a Sql Server to work
with the Windows NT 4.0 and I don't know to use Interbase 4.2 or MS
SQL 6.5. Can you help me?

Thanks.

Other Threads