 |
 | 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 / February 2008Sql ce server process
I posted this on the compact framework board, as the reasoning behind the direction described here is compact framework - related, but I though there might be an expert over here on this board that might be more familiar with possible past attempts in this direction.... We are running out of VM in our main application, and among other things, I am considering how we might break functionalities across groups of native dll's that have to load for those functionalities. An obvious break line involves sqlmobile (3.x). I threw together a quick proof of concept for basically creating a database engine process that would perform all sqlmobile access, with a TCP interface for issuing commands and getting results wrapped in XML (we already do something similar in a process that performs all of our replication - conceivably, this process would grow up to be a sqlmobile engine that handles both queries and replication). I know that there may be something we can do that will be more efficient than XML, but our result sets are generally small - probably the biggest one is 18 rows by about 30 columns. Hopefully an added benefit would be that we might even be able to put together an algorithm for caching a few query plans...we have been unable to do this for some time, due to VM constraints. I can't imagine that this is as avenue that has not been explored, and am just wondering if anyone has any comments on the approach - is this a bad idea? It seems like if this were a viable option, that it would already exist out there somewhere, probably even provided by Microsoft. Maybe they were just afraid people would try to use it in a more robust way than it was intended...? Hello brandon, > I posted this on the compact framework board, as the reasoning behind > the direction described here is compact framework - related, but I [quoted text clipped - 24 lines] > Microsoft. Maybe they were just afraid people would try to use it in > a more robust way than it was intended...? SQL Server CE has a pretty small footprint for all the features it provides. What is it that makes you run out of VM space? Doe the devices have a very limited amount of memory? Are you leaking memory somewhere? Is you application very memory consuming? Can you optimize some of your algorithms or UI parts to free up some of the memory in use? Only after exhausting all these questions I'd start thinking of writing my own storage engine. No matter how simple, efficient en robust you can make it. The main reason for that is that if one of the issues mentioned above isyour actual problem, chances are that your new storage engine will only add to that. -- Jesse Houwing jesse.houwing at sogeti.n Thanks for the reply. These are all things we are considering before we will go to these lengths. Pretty much when you boot, you have about 13M - 17M of VM per process to use, minus any native dll's that each process uses outside of what's loaded for the OS + drivers(sqlmobile, libraries required to support the barcode scanner, speech engine, etc). The process in question uses sqlmobile and the barcode scanner, along with a few pInvokes, etc, has about 13 threads running and has a robust multi-screen UI (implemented as full-screen user controls that take up the whole form - and there is only one form). In total there is about 4M - 5M of VM eaten up by native dll's that the process has a reference to. Almost 2M of that is sqlmobile, so the thought is that if these dll's are no longer referenced in this process, that's 2M that can be allocated for other things, plus whatever VM is used to support the open connection at runtime, and all of the extra JIT'd code at the bottom of the slot. We have about 20M - 30M of free physical memory, so this does not seem to be our limitation. The only dll's that the sql engine would reference would be those required by the framework, anything required for the tcp interface, and sqlmobile, so it should have a solid 10M - 14M of VM to work with solely for interfacing with sql, not having to share any of that with an interface or anything else, and with only two or three other simple threads running. > Hello brandon, > [quoted text clipped - 42 lines] > Jesse Houwing > jesse.houwing at sogeti.nl Brandon, It's not necessarily a bad idea, but I'm not sure if it's a great one either, and it might be more complicated to implement than you expect. The main problem I see with that approach is that it requires you to have a reliable connection all the time or you're completely out of business. I don't know what type of hardware you're targeting, but SQL Compact is in ROM beginning with Windows Mobile 6, so the other 'problem' might be that newer devices solve this problem for you, or at least this solution might not actually solve your problem And a third issue might be serializing access to data if more than one device might be using this service. Making that robust could be more trouble than it's worth, and making it performant under load might be impractical. As for why somebody hasn't done this already - Microsoft has been focusing more on the occasionally connected scenario with Sync Services for ADO.Net. The smart device piece of that isn't available yet, but Sync Framework is certainly the direction they're moving in. Steve Lasker even has a blog post describing a "stored procedure" kind of approach to SQL Compact: http://blogs.msdn.com/stevelasker/archive/2008/02/11/stored-procedures-and-sql-s erver-compact-the-great-debate.aspx Definitely worth a read since it addresses working with queries in a uniform way, although I don't think it addresses the specific issue you're trying to solve.  SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management >I posted this on the compact framework board, as the reasoning behind the > direction described here is compact framework - related, but I though [quoted text clipped - 34 lines] > was > intended...? Target platform is currently WM2003, and it is understood that future platforms may have sql in ROM - which you are correct would make this solution a moot point. We are unlikely to target another platform until WM7 is out, which takes the whole VM and process count limitation issues out of the picture, but the devices we are deploying on today are expected to be around for the next 5 years or so, so we have to consider ways to continue to add functionality in this constrained environment. As for the reliable connection - the engine, running on a particular device, would only be interfaced by a process (or processes) running local on that device - we would not in general use this to interact with the database remotely, though this may end up being handy in certain support situations. My plan is not to try to use sql in any more robust of a fassion than was designed - only to extract that functionality into a separate process where it has more freedom to roam in it's own VM slot. I do not expect to put a load on the database that is any more heavy than the load that exists today, when the application uses the sqlmobile API in-process (actually probably less load, since the new process should have some free VM to cache query plans). We are definitely focused on the occasionally connected end of things - our devices are in vehicles roaming the country on GPRS... This is one of the reasons we exported all of the replication logic into it's own process in order to handle a lot of the retry logic without bogging down the user's interface. > Brandon, > [quoted text clipped - 58 lines] > > was > > intended...? Brandon, It sounds like you've done a lot of thinking about this. I definitely understand about needing to support those WM2003 devices. Have you tried running 2 processes at the same time, one that uses the SQL Mobile engine and one that doesn't to see if that's a solution? I'm skeptical, but then I haven't actually done the tests. The main thing I'm aware of with the unmanaged SQL Compact DLLs is that you need to get them loaded early in the app's execution to make sure there is space for them. The managed code parts can swap in and out, but if there's not enough memory left when you attempt to load the engine, then you do have a problem. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management > Target platform is currently WM2003, and it is understood that future > platforms may have sql in ROM - which you are correct would make this [quoted text clipped - 115 lines] >> > was >> > intended...? Thanks again for the post - We are certainly aware of the dll load issue, and have strategies in place to ensure that this is not a problem. We have a startup process that uses LoadLibrary/FreeLibrary to strategically order all of the dll's that will be used by the applications configured on that device to minimize fragmentation, and then each individual process uses LoadLibrary to maintain a static reference to all of the dll's that it will use in order to prevent VirtualAlloc from allocating heap/stack in the space that the needed dll's map to, causing the dll to fail to load when it is needed. When we have problems we are actually catching a SqlException that says "not enough storage" (can get more details if interested) when we execute the command (I have found many of the error messages to be misleading when you are running up against memory limitations...I used dumpmem to look at the VM space of the process as this was occurring, and saw that there were no contiguous blocks in VM that were large enough for the minimum allocation size (188K)). I'm not sure exactly what you are getting at with the 2 processes...we actually have about 6 long-running processes - two native processes that comprise the turn-by-turn navigation software that uses the speech engine, a .net process that maintains network connectivit/least cost routing, a .net process that locks the device down and only gives the user access to what they need acces to, our .net replication process, a .net process that handles serial communications with GPS devices, and our .net end-user application that is using sqlmobile and is on the virge of disaster. Most of these process seem to have plenty of free VM, just not the end-user application that uses sqlmobile. > Brandon, > [quoted text clipped - 127 lines] > >> > was > >> > intended...? Brandon, It seems I misunderstood what you were doing. You do have a lot going on, but don't let me discourage you from trying some new tricks. To answer your original question about is anybody else doing this, maybe someone will reply. I just don't recall seeing any other messages attempting to do what you're considering. Please let us all know if you find a soltuion that you're happy with. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Scalehouse and Billing Software for Waste Management > Thanks again for the post - > [quoted text clipped - 199 lines] >> >> > was >> >> > intended...? |
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.