Re: A Sql Compact problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

One problem. I can't find any Open method on the SqlCeResultSet. Am I
missing something here?

I'm still using CF 2.0.

Thore

"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> wrote in message
news:ED0B229E-29DF-408C-9AA2-B3A8A01CB7A1@xxxxxxxxxxxxxxxx
Thore,

Assuming the text you're searching for is in a column that is indexed you
can use code like this to find text that begins with the text entered in
the textbox:

rs.Open(ColumnName, new object[] { textBox1.Text }, new object[] {
textBox1.Text+"zzz" });

and this to find an exact match:

rs.Open(ColumnName, new object[] { textBox1.Text }, new object[] {
textBox1.Text});

This approach (along with only enabling the Search button on my form after
3 characters were entered) allowed me to retrieve matches from a table
with 65000 rows on a low-memory device almost instantly, which the normal
SQL approach, which uses the query processor, took 20 minutes or more. I
don't know if this will help in your situation though because the SQL
engine still has to maintain the index as rows are deleted, and I'm
guessing that rebalancing the b-tree could be what is causing the delays
you're seeing. In my case the data was static. So one approach might be to
mark the row as deleted somhow that doesn't change the key field, then
check for the deleted indicator you're using after you've retrieved the
recordset.

--
Ginny Caughey
Device Application Development MVP


"Thore Berntsen" <someone@microsoft,com> wrote in message
news:%23dEUyDrTIHA.5980@xxxxxxxxxxxxxxxxxxxxxxx
I'm interested in how I can use tabledirect for text searches generally.
How it can be used to find an exact match, and how it can be used to find
a parial match.

Thore
"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> wrote in
message news:66C15E6A-09A9-450C-B90D-345575FFBEB3@xxxxxxxxxxxxxxxx
Thore,

Are you searching for an exact match or something else? Sorry but I
don't remember the original query.

--
Ginny Caughey
Device Application Development MVP


"Thore Berntsen" <someone@microsoft,com> wrote in message
news:uk$Tk4KQIHA.4740@xxxxxxxxxxxxxxxxxxxxxxx
I would like to try TableDirect on this search, but I haven't figured
out how I can do a text search this way. Do you have an example?

I think you migth be right about the reorganization. I have tried the
UPDATE STATISTICS command on the index. It seems to help, but I don't
know much about this command so I hesitate a bit to use it.

Thore

"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> wrote in
message news:4A3250F7-8A67-4BEE-9235-2343B78311F1@xxxxxxxxxxxxxxxx
Thore,

I'm glad TableDirect worked for you on the imports. It does indeed use
the index (or at least would in your scenario), but it doesn't use the
query processor so you might get a performance boost if the query
processor is the bottleneck. It could be worth a try, and I'd be
interested in knowing if it does help in this case. If it doesn't
help, then I'd guess SSC is reorganizing the index periodically
(rebalancing the tree, whatever) and that is causing the slowdowns
you're seeing.

I agree the second table won't solve your problem if the problem is
primarily caused by deletes in the big table. I can think up some
other kludges that might or might not help (like marking rows as
deleted in some fashion other than actually deleting them), but
without experimenting I don't really know what will help.

--
Ginny Caughey
Device Application Development MVP


"Thore Berntsen" <someone@microsoft,com> wrote in message
news:uNwas2hPIHA.5360@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for your suggestion. The problem is that it seems to be
deletes that triggers this, and another table will not solve this.
I'm aware of TableDirect, but would that not still use the index?
(Actually it was a talk by you on the MEDC 2006 DVD that finally
convinced me that I should take a look at TableDirect. It made the
import routine to this database 3 times faster. Thanks!)

Thore


"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> wrote in
message news:BFDCCC0C-A82E-43F0-BE40-BCA6D93CF457@xxxxxxxxxxxxxxxx
Thore,

I haven't encountered that issue since the large tables I use tend
to be read only on the device. One strategy you might consider is
having the use add rows to a different much smaller table and search
both if necessary. I don't know if this will solve your performance
issue but it might be worth a try. Another thing to consider if you
aren't doing it already is to use TableDirect for your searches on
the big table since you don't really need to query processor to
figure out how to find the data you're looking for.

--
Ginny Caughey
Device Application Development MVP


"Thore Berntsen" <someone@microsoft,com> wrote in message
news:OFwfYAXPIHA.5360@xxxxxxxxxxxxxxxxxxxxxxx
I have written a quite big retail application for Windows Mobile. It
uses Sql Server Compact Edition. A typical itemregister has up to
40000-50000 items.



I'm very pleased with Sql Server Compact Edition, but has one
problem. The database has an SupplierArticles table. In this table
there is a SupplierArticleNo defined as nvarchar(14). To make
searches fast I have added index to this field.



My problem occurs when I make changes to this table, for example
detetes records. Not always but sometimes. When the customer does
an SupplierArticleNo search it sometimes seems like the application
is hanging. I belived that I had a bug, but after a while I
discovered that if I waited a minute or two the search returned a
result. Then on the subsequent searches evrything was back to
normal. But the users naturally sees this a hang.



I suspect that the delay is because Sql Compact is doing some
maintenance on the database. If I'm right, is there anyway I can
predict when this happens and write code that triggers this
maintenance so I at least can give a message to the user on what is
going on ("Please wait while...") ?



Thore Berntsen

Visma Retail

Norway





Thore














.

Quantcast