Re: A Sql Compact problem

Thanks again, but how is the text search done?

Thore
"Ginny Caughey [MVP]" <ginny.caughey.online@xxxxxxxxxxxxxx> wrote in message
news:27CB17B6-2785-4D41-8589-093EFE91B1CC@xxxxxxxxxxxxxxxx
Thore,

Sorry about that. I copied some production code based on amodified
generated SqlCeResultSet. Here's the Open method:

public void Open() {
System.Data.SqlServerCe.SqlCeCommand sqlCeSelectCommand;
// Open a connection to the database
//
sqlCeConnection = new
System.Data.SqlServerCe.SqlCeConnection(this.resultSetConnectionString);
sqlCeConnection.Open();
// Create the command
//
sqlCeSelectCommand = sqlCeConnection.CreateCommand();
sqlCeSelectCommand.CommandText = "Vehicle"; // name of table
sqlCeSelectCommand.CommandType = System.Data.CommandType.TableDirect;
// Generate the ResultSet
//
sqlCeSelectCommand.ExecuteResultSet(this.resultSetOptions, this);
}

--
Ginny Caughey
Device Application Development MVP


"Thore Berntsen" <someone@microsoft,com> wrote in message
news:uS%23zMdtTIHA.3532@xxxxxxxxxxxxxxxxxxxxxxx
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

















.

Loading