Re: NVARCHAR
In article <3588AAEE.748D@dati.lv>, Leonid Vorontsov
<Leonids.Voroncovs@dati.lv> writes
>Hi All.
>Sometime we worked with version 7.22 and had performance problem with
>NVARCHAR fields. Answer from tech support was "...There is a theory that
>it could be a bug with VARCHAR which caused the server to always go to
>the SEQUENTIAL SCAN mode. If this is the case, the bug is fixed in
>7.3...". But real situation isn't good. I investigated Informix 7.3
>behaviour and result is there:
>
One of the Informix SQL Manuals talks about optimizing your data model
and mentions than NVARCHAR is slower than VARCHAR is slower than CHAR.
>OPTCOMPIND 0
>
>DB_LOCALE=lv_lv.1257
>CLIENT_LOCALE=lv_lv.1257>
>CREATE TABLE fiz_persona (
> ...
> uzvards VARCHAR(34) NOT NULL,
> uzvards2 NVARCHAR(34) NOT NULL,
> ...
>);>
>CREATE INDEX xie1fiz_persona ON fiz_persona (uzvards);
>CREATE INDEX lvo_uzv2 ON fiz_persona (uzvards2);>
>UPDATE STATISTICS FOR TABLE fiz_persona (uzvards) DROP DISTRIBUTIONS;
>UPDATE STATISTICS FOR TABLE fiz_persona (uzvards2) DROP DISTRIBUTIONS;>
>QUERY: (very good response - 0..4sec)
>------
>SELECT * FROM fiz_persona WHERE uzvards LIKE 'KRONBERGS%'>
>Estimated Cost: 547438
>Estimated # of Rows Returned: 534275
>
>1) informix.fiz_persona: INDEX PATH
>
> (1) Index Keys: uzvards
> Lower Index Filter: informix.fiz_persona.uzvards LIKE
>'KRONBERGS%'
>
>
>QUERY: (very bad response - 50..120sec)
>------
>SELECT * FROM fiz_persona WHERE uzvards2 LIKE 'KRONBERGS%'>
>Estimated Cost: 2737142 (5 times more than above)
>Estimated # of Rows Returned: 534275
>
>1) informix.fiz_persona: INDEX PATH (looks like above, but response
>time...)
>
> (1) Index Keys: uzvards2
> Lower Index Filter: informix.fiz_persona.uzvards2 LIKE
>'KRONBERGS%'
>
>In explain file we can see INDEX PATH, but response time is very bad.
>Does Informix use index? If yes, why performance is so bad.
>Do You have any suggestions?
>
>Leonid. (Leonids.Voroncovs@dati.lv)
--
David Williams
Maintainer of the Informix FAQ
Primary site (Beta Version) http://www.smooth1.demon.co.uk
Official site http://www.iiug.org/techinfo/faq/faq_top.html
I see you standin', Standin' on your own, It's such a lonely place for you, For
you to be If you need a shoulder, Or if you need a friend, I'll be here
standing, Until the bitter end...
So don't chastise me Or think I, I mean you harm...
All I ever wanted Was for you To know that I care