Hello Fabiano,

my results inline, using:

* Firebird 2.5.2 64-bit on Win 7 Prof.
* 8K page size
* 75 buffers
* forced writes = on
* database is on a Samsung 830 256GB SSD !!

> I tried that (actually inserted 50.000 records each table), and here follows the test results:
>
> recreate table test_utf8 (
> column1 varchar(100) character set UTF8 collate unicode_ci_ai);
>
> recreate table test_iso8859_1 (
> column1 varchar(100) character set ISO8859_1 collate pt_br);
>
> -- 1m5s146
> set term !! ;
> execute block
> as
> declare variable i integer;
> begin
> i = 0;
> while (i< 50000) do
> begin
> insert into test_utf8 values (uuid_to_char(gen_uuid()));
> i = i + 1;
> end
> insert into test_utf8 values ('xxx');
> end !!
> set term ; !!

0.333 sec

> -- 1m4s959
> set term !! ;
> execute block
> as
> declare variable i integer;
> begin
> i = 0;
> while (i< 50000) do
> begin
> insert into test_iso8859_1 values (uuid_to_char(gen_uuid()));
> i = i + 1;
> end
> insert into test_iso8859_1 values ('xxx');
> end !!
> set term ; !!

0.302 sec

> -- 0s468
> select * from test_utf8 where column1 = 'xxx';

0.152 sec

> -- 0s561
> select * from test_iso8859_1 where column1 = 'xxx';

0.113 sec

> -- 1s716, 1s653, 1s841
> select first 1 * from test_utf8 order by column1;

0.364 sec

> -- 0s671, 0s717, 0s748
> select first 1 * from test_iso8859_1 order by column1;

0.159 sec


Running the ordered statements through the Trace API, I/O is pretty much
the same:

-------------------------------------------------------------------------------
select first 1 * from test_utf8 order by column1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT ((TEST_UTF8 NATURAL))
1 records fetched
283 ms, 274 read(s), 100553 fetch(es)

-------------------------------------------------------------------------------
select first 1 * from test_iso8859_1 order by column1
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN SORT ((TEST_ISO8859_1 NATURAL))
1 records fetched
105 ms, 263 read(s), 100531 fetch(es)


And having a look in the table stats via gstat:

TEST_ISO8859_1 (138)
Primary pointer page: 230, Index root page: 231
Average record length: 44.99, total records: 50001
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 264, data page slots: 264, average fill: 72%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 263
80 - 99% = 0

TEST_UTF8 (137)
Primary pointer page: 225, Index root page: 228
Average record length: 48.99, total records: 50001
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 275, data page slots: 275, average fill: 73%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 274
80 - 99% = 0


Not much difference space-wise either.


I don't know the engine internals, but it could be, that there is space
for improvements in the in-memory sorting module when dealing with
multi-byte character sets or UTF8 in general. But I can be totally wrong
here. Increasing available RAM for sorting in firebird.conf didn't
improve the situation.

But the overall speed difference between your I/O (regular HDD?) system
and my single SSD disk in a regular desktop PC is dramatic.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

> So, the main performance difference here was ordering the data (2X-3X slower). Other operations seems to have the same performance (althought i didn´t use special characters in the records).
>
> Regards,
>
> Fabiano
>
> --- In firebird-support@yahoogroups.com, "Fabiano"<fabianoaspro@...> wrote:
>>
>> Firebird is extreme slow with UTF-8 character set.
>>
>> Try it:
>> Create the same table with/without UTF-8 encoding, put 5000 records with no
>> indexes then do:
>>
>> 'select * from table order by fieldA
>>
>> select * from table where FieldA='xxxx'
>>
>>
>>
>> Cheers
>>
>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>