The most significant issue at the moment is an "out of storage space" error that starts occuring after a while of heavy use. Queries will no longer work. Even a simple select for a single value of a single column and no transactions to be found. The sqlce engine is somehow getting in to a state where it's not freeing up it's resources. I have thoroughly checked to be sure I'm disposing when I should. I suspect we are pushing (and hitting) the limits of the engine with our recent projects.
Another really annoying problem is the way transactions are handled. ie, when you commit, you would (or at least I did) expect the data to be safely on the disk. In fact, its still in ram, waiting to be flushed. The default is a 10 second timeout and I think the best you can do is set it to 1 second. Combine this with the out of storage issue and the very real possibility the mobile device suddenly gets a flat battery and you get some major problems that are difficult to solve. My solution is an entire rework of the DAL to reduce the load on sqlce. This has to be ready for tomorrow :( Of course, there are some old favourites like never insert images into a sqlce database and never use any MS tools to create/edit the database because they can't handle foreign keys. David "If we can hit that bullseye, the rest of the dominoes will fall like a house of cards... checkmate!" -Zapp Brannigan, Futurama On Sun, Dec 5, 2010 at 15:08, Greg Keogh <[email protected]> wrote: >>However, I'm having so much trouble now with SQL CE (I've >>been working all this weekend because of it) that I've recently been >>considering looking at it again. > > Good grief! I considered SQL CE so pleasantly vanilla that I find it hard to > believe you can get into such trouble. Do you have warnings for us?! > >>Any opinions on relative performance, reliability and stability >>compared to SQL CE? > > I haven't pushed it hard enough yet to report anything. I'll report back > after I give it a heavier bash, maybe in several weeks. There are CE vs > SQLite issues I'm worrying about in the back of my mind such as locking, > threading, synch with SQL Server, etc. We'll see... > > Greg > >
