SQL Anywhere ultralite - problem with sentence LIKE en ultralite

Author

problem with sentence LIKE en ultralite

Luis Ponce Ibarra - Hotmail

2006-02-15, 8:23 pm

I am using sybase anywhere 9.0.2.3249 and codewarrior 8.1
I am carrying out query using like for example
select * from item where name like '% cas% '
and it takes a long time too much presenting the information
Somebody could indicate like I can improve the time of answer.
Making a pursuit has detected that when one makes the open doesn't take a
long time, he/she takes a long time when he/she carries out the first next.

David Fishburn

2006-02-16, 9:23 am

"Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in
news:43f391e2$1@foru
ms-2-dub of sybase.public.sqlanywhere.ultralite:

LPI-H> I am using sybase anywhere 9.0.2.3249 and codewarrior 8.1
LPI-H> I am carrying out query using like for example
LPI-H> select * from item where name like '% cas% '
LPI-H> and it takes a long time too much presenting the information
LPI-H> Somebody could indicate like I can improve the time of answer.
LPI-H> Making a pursuit has detected that when one makes the open doesn't
LPI-H> take a long time, he/she takes a long time when he/she carries out
the
LPI-H> first next.

That is the nature of the query.
where name like '% cas% '

Since you have % signs before and after your condition UL has no choice
but to check each and every row in the database to see if it can find
that string somewhere in the name. This would be just as slow in any
other database.

If you had dome something like:
where name like ' cas% '

Then it could use an index (and be fast).
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Luis Ponce Ibarra - Hotmail

2006-02-16, 1:23 pm

using
where yam like 'cas% '
it is useful when one knows with which the chain begins

But when the part of the string that I know this not to the beginning, leave
the necessity to use
where yam like '%cas% '
And this it is the case that we have, in a table of 4000 row -table of
item-.

They could seek advice some way to manage the case that I have just
mentioned them

"David Fishburn" <fishburn_spam@off.ianywhere.com> escribió en el mensaje
news:Xns976C635E2825
Bfishburnsybasecom@1
27.0.0.1...
> "Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in
> news:43f391e2$1@foru
ms-2-dub of sybase.public.sqlanywhere.ultralite:
>
> LPI-H> I am using sybase anywhere 9.0.2.3249 and codewarrior 8.1
> LPI-H> I am carrying out query using like for example
> LPI-H> select * from item where name like '% cas% '
> LPI-H> and it takes a long time too much presenting the information
> LPI-H> Somebody could indicate like I can improve the time of answer.
> LPI-H> Making a pursuit has detected that when one makes the open doesn't
> LPI-H> take a long time, he/she takes a long time when he/she carries out
> the
> LPI-H> first next.
>
> That is the nature of the query.
> where name like '% cas% '
>
> Since you have % signs before and after your condition UL has no choice
> but to check each and every row in the database to see if it can find
> that string somewhere in the name. This would be just as slow in any
> other database.
>
> If you had dome something like:
> where name like ' cas% '
>
> Then it could use an index (and be fast).
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

Alberto Silva

2006-02-16, 1:23 pm

Hola,
You may consider having a check box or a radio button to allow the user to
search for 'starts with' or 'contains' the text to be searched.

Saludos,

Alberto Silva
MS Mobile Devices MVP
http://msmvps.com/AlbertoSilva
[color=darkred]
> using
> where yam like 'cas% '
> it is useful when one knows with which the chain begins
> But when the part of the string that I know this not to the beginning,
> leave
> the necessity to use
> where yam like '%cas% '
> And this it is the case that we have, in a table of 4000 row -table of
> item-.
> They could seek advice some way to manage the case that I have just
> mentioned them
>
> "David Fishburn" <fishburn_spam@off.ianywhere.com> escribió en el
> mensaje news:Xns976C635E2825
Bfishburnsybasecom@1
27.0.0.1...
>

Greg Fenton

2006-02-17, 7:24 am

Luis Ponce Ibarra - Hotmail wrote:
> But when the part of the string that I know this not to the beginning, leave
> the necessity to use
> where yam like '%cas% '

The problem is that indexes are not built on arbitrary substrings, only
on string prefixes. To do such would cause indexes to grow in size at
an incredible rate.

I cannot think of any way to avoid a full scan of all 4000 values if you
cannot predetermine the search patterns users will use.

You could start building a word (or prefix) "index" in a separate table,
but that could grow very large and again would limit the type of search
you could perform.

What you are looking for is a "full text search", but neither UL nor ASA
support full text searches (and the RDBMSes have limited capabilities).

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links