Re: A Sql Compact problem

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