[sqlite] Select via Wi-fi very slow

[Kevin Youren]

Emany,

this has possibly already been done, but have you got an index on the table
for the column called "description"?

I would suggest a timed test on both the Symbol device and the computer
before and after the index is added.

Also, the Symbol may only have 64Mb of RAM, which probably would slow it
down.

There is a method, unfortunately not available is Sqlite, called a Stored
Procedure. A Stored Procedure is especially useful in your case because you
move the database access from the Symbol to a more powerful computer, and
reduce the back and forth WiFi traffic. The SQL would be sent from the
Symbol to the computer hosting the database, the query would executed on the
host computer and the result sent back to the Symbol.

If possible, a workaround or emulation of a Stored Procedure could be done.
You could send the SQL to a program on the host, and that program would
interrogate the database and return the result. It would need a reasonable
level of skill to implement.

regards,

Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select via Wi-fi very slow

[Tim Romano]

N.B. Queries with LIKE will not use an index if the particular
implementation of SQLite overrides LIKE. The .NET implementation I'm
familiar with has done so; the OP's may have done so too. However, GLOB was
left intact and does make use of an index on "starts with" and "equals"
substring searches.

GLOB is case-sensitive.


select * from products  where description GLOB 'shirt*'


Note the asterisk wildcard instead of the percent-symbol.


Regards
Tim Romano
Swarthmore PA




Regards
Tim Romano

On Tue, May 11, 2010 at 5:50 AM, Pavel Ivanov  wrote:

> > Sometimes search found 200 records.  When I do a query via wi-fi takes 1
> > minute.
> > How can I decrease this time?
>
> Time taken to search for the records does not depend on how many
> records found. It depends on how many records were searched through.
> Most probably for your query no indexes are used, so the whole table
> is scanned through. And that means that the whole database is copied
> to your device via WiFi, which apparently is slow.
>
> To decrease the amount of data transfered to the device you can use
> indexes. For this particular query you can create index like this:
>
> CREATE INDEX Product_Ind on Product
> (description COLLATE NOCASE);
>
>
> Pavel
>
> On Mon, May 10, 2010 at 6:31 PM, Ernany  wrote:
> > Hello,
> >
> > I'll try to explain my problem:
> >
> > I have a Symbol MC3090 Data Collector with VB.Net 2005. I have a database
> > with 80,000 records on the computer.
> >
> > For example: I search all words that begin with "shirt" and show in the
> Grid
> > Collector.
> > Sometimes search found 200 records.  When I do a query via wi-fi takes 1
> > minute.
> > How can I decrease this time?
> >
> > On the computer the same search takes a few seconds ...
> >
> >
> >  Public ConnStringDados As String = "Data Source=" & Address & "\" &
> NameDB
> > & ";Version=3;Compress=True;Synchronous=Off;Cache Size=8000;"
> >
> >
> > My select:
> >
> > "SELECT codigo, description FROM Product WHERE description Like '" & Word
> > _Search & "%'"
> >
> >
> > Thanks,
> >
> > Ernany
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select via Wi-fi very slow

[Pavel Ivanov]

> Sometimes search found 200 records.  When I do a query via wi-fi takes 1
> minute.
> How can I decrease this time?

Time taken to search for the records does not depend on how many
records found. It depends on how many records were searched through.
Most probably for your query no indexes are used, so the whole table
is scanned through. And that means that the whole database is copied
to your device via WiFi, which apparently is slow.

To decrease the amount of data transfered to the device you can use
indexes. For this particular query you can create index like this:

CREATE INDEX Product_Ind on Product
(description COLLATE NOCASE);


Pavel

On Mon, May 10, 2010 at 6:31 PM, Ernany  wrote:
> Hello,
>
> I'll try to explain my problem:
>
> I have a Symbol MC3090 Data Collector with VB.Net 2005. I have a database
> with 80,000 records on the computer.
>
> For example: I search all words that begin with "shirt" and show in the Grid
> Collector.
> Sometimes search found 200 records.  When I do a query via wi-fi takes 1
> minute.
> How can I decrease this time?
>
> On the computer the same search takes a few seconds ...
>
>
>  Public ConnStringDados As String = "Data Source=" & Address & "\" & NameDB
> & ";Version=3;Compress=True;Synchronous=Off;Cache Size=8000;"
>
>
> My select:
>
> "SELECT codigo, description FROM Product WHERE description Like '" & Word
> _Search & "%'"
>
>
> Thanks,
>
> Ernany
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Select via Wi-fi very slow

[Ernany]

Hello,

I'll try to explain my problem:

I have a Symbol MC3090 Data Collector with VB.Net 2005. I have a database
with 80,000 records on the computer.

For example: I search all words that begin with "shirt" and show in the Grid
Collector.
Sometimes search found 200 records.  When I do a query via wi-fi takes 1
minute.
How can I decrease this time?

On the computer the same search takes a few seconds ...


 Public ConnStringDados As String = "Data Source=" & Address & "\" & NameDB
& ";Version=3;Compress=True;Synchronous=Off;Cache Size=8000;"


My select:

"SELECT codigo, description FROM Product WHERE description Like '" & Word
_Search & "%'"


Thanks,

Ernany
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users