 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / January 2008A Sql Compact problem
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 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.  SignatureGinny Caughey Device Application Development MVP >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 [quoted text clipped - 25 lines] > > Thore 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 > Thore, > [quoted text clipped - 35 lines] >> >> Thore 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. SignatureGinny Caughey Device Application Development MVP > Thanks for your suggestion. The problem is that it seems to be deletes > that triggers this, and another table will not solve this. [quoted text clipped - 46 lines] >>> >>> Thore 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 > Thore, > [quoted text clipped - 62 lines] >>>> >>>> Thore Thore, Are you searching for an exact match or something else? Sorry but I don't remember the original query. SignatureGinny Caughey Device Application Development MVP >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? [quoted text clipped - 71 lines] >>>>> >>>>> Thore 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 > Thore, > [quoted text clipped - 76 lines] >>>>>> >>>>>> Thore 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. SignatureGinny Caughey Device Application Development MVP > 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 [quoted text clipped - 81 lines] >>>>>>> >>>>>>> Thore I will take a look at this. Thanks! Thore > Thore, > [quoted text clipped - 108 lines] >>>>>>>> >>>>>>>> Thore 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 > Thore, > [quoted text clipped - 108 lines] >>>>>>>> >>>>>>>> Thore 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); } SignatureGinny Caughey Device Application Development MVP > One problem. I can't find any Open method on the SqlCeResultSet. Am I > missing something here? [quoted text clipped - 115 lines] >>>>>>>>> >>>>>>>>> Thore Thanks again, but how is the text search done? Thore > Thore, > [quoted text clipped - 138 lines] >>>>>>>>>> >>>>>>>>>> Thore Thore, You close that resultset and open a new one with the new criteria. SignatureGinny Caughey Device Application Development MVP > Thanks again, but how is the text search done? > [quoted text clipped - 141 lines] >>>>>>>>>>> >>>>>>>>>>> Thore Thore, I think I misunderstood your last question. Perhaps this code shows what you want: // Execute ResultSet using index and range public void Open(string indexName, object[] startValues, object[] endValues) { 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; //==> specify the index file name and range sqlCeSelectCommand.IndexName = indexName; sqlCeSelectCommand.SetRange(System.Data.SqlServerCe.DbRangeOptions.InclusiveStart System.Data.SqlServerCe.DbRangeOptions.InclusiveEnd, startValues, endValues); // Generate the ResultSet // sqlCeSelectCommand.ExecuteResultSet(this.resultSetOptions, this); } SignatureGinny Caughey Device Application Development MVP > Thanks again, but how is the text search done? > [quoted text clipped - 141 lines] >>>>>>>>>>> >>>>>>>>>>> Thore Thank you! > Thore, > [quoted text clipped - 175 lines] >>>>>>>>>>>> >>>>>>>>>>>> Thore > 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 [quoted text clipped - 25 lines] > > Thore Just a thought. Given that you're accessing a relatively large data, it could be due to Garbage Collection kicking in that's causing the delay. As Ginny suggested, TableDirect is the most efficient way to access large table. - Jin I don't think that the GC can be the problem. It is the SSCE who seems to be causing this problem. And that isn't a managed application. Or am I wrong? >> 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 [quoted text clipped - 38 lines] > > - Jin > I don't think that the GC can be the problem. It is the SSCE who seems to be > causing this problem. And that isn't a managed application. Or am I wrong? [quoted text clipped - 41 lines] > > > - Jin You're probably right about SSCE not being a managed app, but GC can certainly kick in if you're accessing/manipulating the data within your managed program. Having said that, you're probably right about the SSCE being the source of this issue. So, your best bet is to go with the TableDirect route. Also, keep the cursor open throughout the application cycle to avoid repeated open/close to reduce the overhead. Using the SqlCeResultSet's Delete method (after establishing the cursor location via the Seek) with this open cursor will most likely help you out assuming it's not the rebalancing that Ginny talked about. FYI, I have around 10 SqlCeResultSet cursors open for my application and it works like a charm. Good luck and let us know how it goes. - Jin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.