Board index » delphi » BDE IntrBase Searches Slow on international character sets

Toni MArti

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

BDE IntrBase Searches Slow on international character sets

Please, a need help.

I'm working with Delphi 3 and the BDE and Interbase 4.2.

I create a Database with Default Charset WIN1252 or ISO... for Spanish
sorting.

I create a table CUSTOMERS with a few columns and a primary key...

I create indexes for the columns defined as COLLATE ES_ES or COLLATE
PDW_SPAN

When I Execute:

Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it seems
that searchs sequentialy because it takes a lot of time (20 seconds).

But if I execute Through IWSQL:

SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

IT'S VERY QUICK!!!

I tried to change the alias Language driver to Spanish ANSI and others....
If I select NULL alias language driver the characters dont display correctly
and I have errors.

When I use a database created with CHARACTER SET NONE and using the locate
procedure, the result becomes immediate.

I would be very gratefull if somebody could help me,

   Yours faithfully,

            Toni Martir, Programming services, Tecni-Web

 
Jean Ferreir

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


Toni,

Are you using TTable or TQuery components to locate your records ??

If is TQuery then the problem isn't language driver. When you do a select
statement in Query component, that will to return a result set with your
40.000 records on demand, but when you call a locate function in TQuery, the
BDE must fetch all 40.000 records from database, because the locate comand
will to execute a locate in result set, therefore the performance for this
operation is very slow.
Try to work with small result set and this problem is minimized.

Hope this help,

Jean Ferreira
======================
Technology Consultant
Borland Developer Certified in Delphi
j...@adm.com.br

Quote
Toni MArtir wrote in message <6hkr6g$o...@forums.borland.com>...

>Please, a need help.

>I'm working with Delphi 3 and the BDE and Interbase 4.2.

>I create a Database with Default Charset WIN1252 or ISO... for Spanish
>sorting.

>I create a table CUSTOMERS with a few columns and a primary key...

>I create indexes for the columns defined as COLLATE ES_ES or COLLATE
>PDW_SPAN

>When I Execute:

>Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it seems
>that searchs sequentialy because it takes a lot of time (20 seconds).

>But if I execute Through IWSQL:

>SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

>IT'S VERY QUICK!!!

>I tried to change the alias Language driver to Spanish ANSI and others....
>If I select NULL alias language driver the characters dont display
correctly
>and I have errors.

>When I use a database created with CHARACTER SET NONE and using the locate
>procedure, the result becomes immediate.

>I would be very gratefull if somebody could help me,

>   Yours faithfully,

>            Toni Martir, Programming services, Tecni-Web

Scott Frolich [Borland

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


Quote
Toni MArtir wrote:

> Please, a need help.

> I'm working with Delphi 3 and the BDE and Interbase 4.2.

> I create a Database with Default Charset WIN1252 or ISO... for Spanish
> sorting.

> I create a table CUSTOMERS with a few columns and a primary key...

> I create indexes for the columns defined as COLLATE ES_ES or COLLATE
> PDW_SPAN

> When I Execute:

> Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it seems
> that searchs sequentialy because it takes a lot of time (20 seconds).

> But if I execute Through IWSQL:

> SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

> IT'S VERY QUICK!!!

> I tried to change the alias Language driver to Spanish ANSI and others....
> If I select NULL alias language driver the characters dont display correctly
> and I have errors.

> When I use a database created with CHARACTER SET NONE and using the locate
> procedure, the result becomes immediate.

> I would be very gratefull if somebody could help me,

>    Yours faithfully,

>             Toni Martir, Programming services, Tecni-Web

Hello,
  As soon as you do a Locate, the BDE will need to fetch the ENTIRE
result set from the server because Delphi will want to use a filter on
the data.  The best way is to find your record with your own SQL
statment.

 Scott
--
BDE Support:
 http://www.borland.com/devsupport/bde
Delphi Support:
 http://www.borland.com/devsupport/delphi
Common Delphi and BDE Questions and Answers:
 http://www.borland.com/devsupport/delphi/qanda/

Jim Carpente

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


I think you need to restate the problem.

Is you problem with the fact that locate is slow on large tables and
queries are faster?

 or

You can not set the language driver correctly?

Quote
Toni MArtir wrote:

> Please, a need help.

> I'm working with Delphi 3 and the BDE and Interbase 4.2.

> I create a Database with Default Charset WIN1252 or ISO... for Spanish
> sorting.

> I create a table CUSTOMERS with a few columns and a primary key...

> I create indexes for the columns defined as COLLATE ES_ES or COLLATE
> PDW_SPAN

> When I Execute:

> Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it seems
> that searchs sequentialy because it takes a lot of time (20 seconds).

> But if I execute Through IWSQL:

> SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

> IT'S VERY QUICK!!!

> I tried to change the alias Language driver to Spanish ANSI and others....
> If I select NULL alias language driver the characters dont display correctly
> and I have errors.

> When I use a database created with CHARACTER SET NONE and using the locate
> procedure, the result becomes immediate.

> I would be very gratefull if somebody could help me,

>    Yours faithfully,

>             Toni Martir, Programming services, Tecni-Web

Toni Márti

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


But My Customers component is a TTAble not a TQuery.

Only when I especify a character set the locate methot is slow.

Scott Frolich [Borland] escribi en mensaje
<353E21A8.5...@corp.borland.com>...

Quote
>Toni MArtir wrote:

>> Please, a need help.

>> I'm working with Delphi 3 and the BDE and Interbase 4.2.

>> I create a Database with Default Charset WIN1252 or ISO... for Spanish
>> sorting.

>> I create a table CUSTOMERS with a few columns and a primary key...

>> I create indexes for the columns defined as COLLATE ES_ES or COLLATE
>> PDW_SPAN

>> When I Execute:

>> Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it
seems
>> that searchs sequentialy because it takes a lot of time (20 seconds).

>> But if I execute Through IWSQL:

>> SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

>> IT'S VERY QUICK!!!

>> I tried to change the alias Language driver to Spanish ANSI and
others....
>> If I select NULL alias language driver the characters dont display
correctly
>> and I have errors.

>> When I use a database created with CHARACTER SET NONE and using the
locate
>> procedure, the result becomes immediate.

>> I would be very gratefull if somebody could help me,

>>    Yours faithfully,

>>             Toni Martir, Programming services, Tecni-Web
>Hello,
>  As soon as you do a Locate, the BDE will need to fetch the ENTIRE
>result set from the server because Delphi will want to use a filter on
>the data.  The best way is to find your record with your own SQL
>statment.

> Scott
>--
>BDE Support:
> http://www.borland.com/devsupport/bde
>Delphi Support:
> http://www.borland.com/devsupport/delphi
>Common Delphi and BDE Questions and Answers:
> http://www.borland.com/devsupport/delphi/qanda/

Scott Frolich [Inprise

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


Quote
Toni Mrtir wrote:

> But My Customers component is a TTAble not a TQuery.

> Only when I especify a character set the locate methot is slow.

> Scott Frolich [Borland] escribi en mensaje
> <353E21A8.5...@corp.borland.com>...
> >Toni MArtir wrote:

> >> Please, a need help.

> >> I'm working with Delphi 3 and the BDE and Interbase 4.2.

> >> I create a Database with Default Charset WIN1252 or ISO... for Spanish
> >> sorting.

> >> I create a table CUSTOMERS with a few columns and a primary key...

> >> I create indexes for the columns defined as COLLATE ES_ES or COLLATE
> >> PDW_SPAN

> >> When I Execute:

> >> Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it
> seems
> >> that searchs sequentialy because it takes a lot of time (20 seconds).

> >> But if I execute Through IWSQL:

> >> SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

> >> IT'S VERY QUICK!!!

> >> I tried to change the alias Language driver to Spanish ANSI and
> others....
> >> If I select NULL alias language driver the characters dont display
> correctly
> >> and I have errors.

> >> When I use a database created with CHARACTER SET NONE and using the
> locate
> >> procedure, the result becomes immediate.

> >> I would be very gratefull if somebody could help me,

> >>    Yours faithfully,

> >>             Toni Martir, Programming services, Tecni-Web
> >Hello,
> >  As soon as you do a Locate, the BDE will need to fetch the ENTIRE
> >result set from the server because Delphi will want to use a filter on
> >the data.  The best way is to find your record with your own SQL
> >statment.

> > Scott
> >--
> >BDE Support:
> > http://www.borland.com/devsupport/bde
> >Delphi Support:
> > http://www.borland.com/devsupport/delphi
> >Common Delphi and BDE Questions and Answers:
> > http://www.borland.com/devsupport/delphi/qanda/

Hello,
  try TTable.FindKey instead

Scott
--
BDE Support:
 http://www.inprise.com/devsupport/bde
Delphi Support:
 http://www.inprise.com/devsupport/delphi
Common Delphi and BDE Questions and Answers:
 http://www.inprise.com/devsupport/delphi/qanda/

Toni Márti

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:BDE IntrBase Searches Slow on international character sets


Scott Frolich [Inprise] escribi en mensaje
<35609D52.7...@corp.inprise.com>...

Quote
>Toni Mrtir wrote:

>> But My Customers component is a TTAble not a TQuery.

>> Only when I especify a character set the locate methot is slow.

>> Scott Frolich [Borland] escribi en mensaje
>> <353E21A8.5...@corp.borland.com>...
>> >Toni MArtir wrote:

>> >> Please, a need help.

>> >> I'm working with Delphi 3 and the BDE and Interbase 4.2.

>> >> I create a Database with Default Charset WIN1252 or ISO... for Spanish
>> >> sorting.

>> >> I create a table CUSTOMERS with a few columns and a primary key...

>> >> I create indexes for the columns defined as COLLATE ES_ES or COLLATE
>> >> PDW_SPAN

>> >> When I Execute:

>> >> Customers.Locate('NAME','CAL',[iopartialkey]) with 40.000 records, it
>> seems
>> >> that searchs sequentialy because it takes a lot of time (20 seconds).

>> >> But if I execute Through IWSQL:

>> >> SELECT NAME FROM CUSTOMERS WHERE  (NAME LIKE "CAL%")

>> >> IT'S VERY QUICK!!!

>> >> I tried to change the alias Language driver to Spanish ANSI and
>> others....
>> >> If I select NULL alias language driver the characters dont display
>> correctly
>> >> and I have errors.

>> >> When I use a database created with CHARACTER SET NONE and using the
>> locate
>> >> procedure, the result becomes immediate.

>> >> I would be very gratefull if somebody could help me,

>> >>    Yours faithfully,

>> >>             Toni Martir, Programming services, Tecni-Web
>> >Hello,
>> >  As soon as you do a Locate, the BDE will need to fetch the ENTIRE
>> >result set from the server because Delphi will want to use a filter on
>> >the data.  The best way is to find your record with your own SQL
>> >statment.

>> > Scott
>> >--
>> >BDE Support:
>> > http://www.borland.com/devsupport/bde
>> >Delphi Support:
>> > http://www.borland.com/devsupport/delphi
>> >Common Delphi and BDE Questions and Answers:
>> > http://www.borland.com/devsupport/delphi/qanda/
>Hello,
>  try TTable.FindKey instead

>Scott
>--
>BDE Support:
> http://www.inprise.com/devsupport/bde
>Delphi Support:
> http://www.inprise.com/devsupport/delphi
>Common Delphi and BDE Questions and Answers:
> http://www.inprise.com/devsupport/delphi/qanda/

The TTable.FindKey method is slow too.

I thing the problem is in the BDE or in the SQL Interbase link because I
create the same table
in MS SQL Server, and Access and the search is fast.

This is not the only problem I have. I have a problem changing the index.

Example:

With a Interbase 4.2 table of 40.000 records with an index in FIRST_NAME
Field.

Table1.IndexFieldNames:='Customer_Code'
Table1.Open;

// Fast
// But If I execute

Table1.IndexFieldNames:='FIRST_NAME'

// It takes about 20 seconds

I step through the source code of DB.PAS unit and Find The slow instruction,
after changing the index GetPriorRecord function of BDE engine CALL takes
about 19 seconds.

When I Execute a Table.Locate procedure the slow instruction is the same. If
I delete this instruction from source code the Locate procedure is fast, but
if I Execute Table.Prior it takes the 19 seconds.

The question is that if I Create the database using CHARACTER SET NONE
(without COLLATION ORDERS) all works fast, and when I use another database
server, is fast too.

There is a bug in the SQL Link for Interbase? or my local language
configuration is incorrect.

I Appreciate your Help very, very, very much, so if I can help you anyway I
do.

Excuse me for my bad english.

Other Threads

1. BDE Searches Slow in international character sets

2. BDE And Interbase slow on international character sets

3. BDE and Interbase Slow in international character set

4. Posting with TNMNNTP in international character sets

5. TIP: BDE 2.52, Corrupted indexes, international characters, D1

6. Problem with BDE 4.51 and International Characters

7. Bug: BDE 2.52 and international characters

8. Character set for eurosign/change the default character set

9. Accented characters in search field / soundex search

10. The GB Character Set for BDE