 |
 | 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 / April 2008Performance problem in sql ce
Hi, I am very disappointed with the sql queries performance in sql ce. In my some queries i have inner joins on 9 tables some tables have more then 150000 records. Db size is around 15 MB. If i run that query with particular where clause (ex RoomNo. = 100) then it gives result in 1 minute otherwise i wait for 15 min. & i did not get results. However i am using proper indexing on SARG still response time is not acceptable. I also tried by putting db on sd card doesn't work. Can any body help me to find out where i am wrong or the limitations of sql ce queries. Even with correct indexes, having lots of joins is a known performance bottleneck. How many is "lots"? I guess it depends on the data. The only workaround I'm aware of is to denormalize your data. Ginny > Hi, > I am very disappointed with the sql queries performance in sql ce. In my [quoted text clipped - 11 lines] > sql > ce queries.  SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Hi Ginny, Thanks for reply. While sync i am keeping required fields in tables. That means all fields that i am keeping i need to show on UI. Suppose i denormalize my 9 tables to 15 then i have to put joins on 15 tables (joins will increase) definitely row size will decrease. But can it improve performance to acceptable limit ? like i am not getting results in even 15 minutes. Or reducing the amount of data is the only solution. Is there any predefined limit of data in sql ce ? > Even with correct indexes, having lots of joins is a known performance > bottleneck. How many is "lots"? I guess it depends on the data. The only [quoted text clipped - 17 lines] > > sql > > ce queries. I haven't seen any published limit because I suspect it's variable. I've had good luck with 4 joins, but 9 is a lot. With the amount of data you need to work with, I like Jin's suggestion of using SqlCeResultSet objects. It would be more work since you'd need to do all the joins programmatically, but you could probably get very good performance going that route, even with the amount of data you have. I'd suggest using TableDirect and specify each index and range to get the best performance. Note that you can databind SqlCeResultSets (actually the default ResultSetView) to your UI elements, but you'd need to provide a current changed event handler(s) and programmatically refresh the "child" resultsets as the most senior parent one changes. When I said denormalize your 9 tables, I was thinking of merging them into maybe 4 tables rathter than splitting them further into 15. I don't know your data so I don't know if that would be a practical option for you or not, and with the large amount of data, you'd still get better performance with SqlCeResultSet. Ginny > Hi Ginny, > Thanks for reply. While sync i am keeping required fields in tables. [quoted text clipped - 31 lines] >> > sql >> > ce queries. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Hi Ginny, Resultset seems to be work. I tried with this SqlCeResultSet rs = CeCommand.ExecuteResultSet(ResultSetOptions.Scrollable); and it takes only 10 sec for query which was taking previously 1 min in CeDataAdapter = new SqlCeDataAdapter(CeCommand); But i want return results in dataset object because i am using dataset every where for businss logic calculations.Can we convert resultset to dataset ? By programatic joins mean creating separate result sets then loop through and filter matching PK & FK fields. please clear and how can we set range in this scenario? In db i have data like rooms table then minibars,model body,grids,sections, gridsensors,sensors,products,productcategory etc. So its normalized.i don't think i can denormalize it beacause merging data will create much redundancy and it would be difficult to extract required data to show on UI. > I haven't seen any published limit because I suspect it's variable. I've had > good luck with 4 joins, but 9 is a lot. [quoted text clipped - 52 lines] > >> > sql > >> > ce queries. Gagan, You specify the range on the command object that you call ExecuteResultSet on. Here's some sample code I use to filter Orders in Northwind based on the customer ID in the customersResultSet to give you an idea: if (sqlTableDirectCommand == null) { sqlTableDirectCommand = conn.CreateCommand(); sqlTableDirectCommand.CommandText = "Orders"; sqlTableDirectCommand.CommandType = System.Data.CommandType.TableDirect; sqlTableDirectCommand.IndexName = "Customer ID"; } // Generate the ResultSet // object[] startValues = new object[] { customersResultSet.Customer_ID }; object[] endValues = new object[] { customersResultSet.Customer_ID }; sqlTableDirectCommand.SetRange(System.Data.SqlServerCe.DbRangeOptions.InclusiveStart System.Data.SqlServerCe.DbRangeOptions.InclusiveEnd, startValues, endValues); sqlTableDirectCommand.ExecuteResultSet(this.resultSetOptions, this); This is indeed more work than just using the query processor to fetch the data you want, but if you want the best performance, then this is how you can get it. Ginny > Hi Ginny, > Resultset seems to be work. I tried with this [quoted text clipped - 88 lines] >> >> > sql >> >> > ce queries. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Hi Ginny, Resultset seems to be work. I tried with this SqlCeResultSet rs = CeCommand.ExecuteResultSet(ResultSetOptions.Scrollable); and it takes only 10 sec for query which was taking previously 1 min in CeDataAdapter = new SqlCeDataAdapter(CeCommand); But i want return results in dataset object because i am using dataset every where for businss logic calculations.Can we convert resultset to dataset ? By programatic joins mean creating separate result sets then loop through and filter matching PK & FK fields. please clear and how can we set range in this scenario? In db i have data like rooms table then minibars,model body,grids,sections, gridsensors,sensors,products,productcategory etc. So its normalized.i don't think i can denormalize it beacause merging data will create much redundancy and it would be difficult to extract required data to show on UI. > I haven't seen any published limit because I suspect it's variable. I've had > good luck with 4 joins, but 9 is a lot. [quoted text clipped - 52 lines] > >> > sql > >> > ce queries. Even though you're using "Proper indexing" (I'm not sure what this means), are you sure that they're being used? Query optimizers aren't perfect and sometimes get confused. Try experimenting with your queries by elminating tables and joins until performance improves. Try running it on the desktop and seeing if you have the same issues there. If so, you can look at the "Execution Plan" to see if it is indeed using an index where is should be. > Hi, > I am very disappointed with the sql queries performance in sql ce. In my [quoted text clipped - 6 lines] > Can any body help me to find out where i am wrong or the limitations of sql > ce queries. 150,000 records sounds a bit too much for mobile application to handle. Couple that with joins on 9 tables, I wouldn't expect good performance at all. Ginny's suggestion of denormalization is a good one if you can afford to make the changes. Another approach might be to avoid the joins as much as possible and go with programmatic joins via the SqlCeResultSet. This approach will at least allow you to have a bit more control over the performance bottlenecks. - Jin Hi Jin, Thanks your suggestion works i tried with this SqlCeResultSet rs = CeCommand.ExecuteResultSet(ResultSetOptions.Scrollable); For query, with where clause i was getting result in 1 min now i am getting result in 10 sec. I can use this on many places to improve performance. Previously i was using CeDataAdapter = new SqlCeDataAdapter(CeCommand); to fill the result in dataset. But the problem is still same for the query which is taking > 15 min. By saying programmatically joins means first create separate result sets for all tables then start filtering or some thing else please clear. Another problem is that i am using dataset every where. After getting results for filtering, sorting and showing on UI. Can we convert result set object to dataset directly or i have to work with resultset only. i am not much familiar with this object and i don't have much time for r&d. > > Hi, > > I am very disappointed with the sql queries performance in sql ce. In my [quoted text clipped - 16 lines] > > - Jin Gagan, Just work with the result set directly - don't bother converting back and forth to a dataset. If you create an updatable result set, you can change the data in the underlying table directly without needing a dataset or data adapter at all. You just set the new values for the columns and call Update. There is also Delete and Insert if you need those methods. There are some pretty good samples in the documentation if you search under SqlCeResultSet. For best perforumance create separate result sets for the other tables using TableDirect, specify the index name for that table and the range, which provides the filtering. This is very fast, even if there are a lot of records, because you are bypassing the query processor entirely and working directly with the indexes. Ginny > Hi Jin, > Thanks your suggestion works i tried with this [quoted text clipped - 43 lines] >> >> - Jin SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management > Hi Jin, > Thanks your suggestion works i tried with this [quoted text clipped - 32 lines] > > > - Jin Working with SqlCeResultSet is pretty much what Ginny described. Although you can use it in similar manner to that of DataSet, it's much easier (and probably faster) if you rely only on methods available in SqlCeResultSet for data operations (like Insert, Delete, Update, etc.). Start with the examples from the documentation and do a search on the internet for other tips. If your existing program is heavily reliant on DataAdapter, it may take quite a bit of changes to move away from that. If not, then it should be ok. Generally speaking, I prefer working with SqlCeResultSet over the DataSet because of the performance and ease of use. To further clarify what I mean by "programmatic join," I'm basically referring to obtaining the values from other table SqlCeResultSet object on a need-basis. For example, if you have the Master table which is being traversed via rsMaster, retrieve the Detail table value via rsDetail only when needed (by performing filtering or even the Seek using FirstEqual option on existing ResultSet object). In one of my application, I have around 10 ResultSet object open simultaneously and retrieve the values I need using the Seek method. It works great and is super fast. Finally, DataAdapter is rather heavy on memory requirements, so moving away from that will improve the memory usage on your mobile device, which is another plus. - Jin Hi Jin, I tried with this approach of opening 9 resultset and loop through using seek and setrange and put all required rows with required columns in one datatable. Then apply where clause to filter out required rows. I can't apply this filter while extracting records from resultset because these filters are based on OR clause on fields belong to different tables. Any way, i am working on 500 rooms db.Now 100 rooms search query with all filters takes 1 minute. It is a significant improvement previously i was not able to get even result. When i fire query to search for all 500 rooms it start throwing out of memory exception. It seems that there is a limit of device i am using Dell Axim X51 with 50MB main memory. In future we are expecting to work on 5000 rooms db seems to be not possible at least for all rooms search criteria. > > Hi Jin, > > Thanks your suggestion works i tried with this [quoted text clipped - 59 lines] > > - Jin If this is how to solve sql performance problems, why bother using a relational database at all? You're better off figuring out why your initial query takes so long. Restructure it or perhaps break it into 2 queries, but to do this is quite rediculous. Hi joker, As per my experience relation database should be there in mobile app otherwise merging tables will create other problems and it will be helpful in easily extracting records also. Breaking the query with reduced inner joins and then start filtering resultsets might work in some scenario but i think in my case it will again go to out of memory exception because the amount of data which i have. Any way, overall it seems that working with less data is practical in mobile applications. > If this is how to solve sql performance problems, why bother using a > relational database at all? > > You're better off figuring out why your initial query takes so long. > Restructure it or perhaps break it into 2 queries, but to do this is > quite rediculous. Why is the initial query that you posted so slow? That's the question you need to answer BEFORE you start figuring out how to make it faster. joker, With a server database I'd agree with you, but on memory constrained devices like Gagan's and the amount of data he's working with, I don't think it's likely he'd get decent performance with that many joins using the query processor. Even with the approach he's using, he's still leveraging the indexes of the database, so that alone is a good reason not to use something like flat files or XML. Ginny > If this is how to solve sql performance problems, why bother using a > relational database at all? > > You're better off figuring out why your initial query takes so long. > Restructure it or perhaps break it into 2 queries, but to do this is > quite rediculous. SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management He may be "leveraging" the indexes, but the end desire is the same. To touch the smallest number of records possible while getting the ones you want in a timely manner. I'm not really suggesting abandoning sqlce (it was sarcasm), I just think he went from one extreem (mega joins in a single query) to the other (perform all the joins in app. code). Hi Joker, I am getting what u want to say by touching smallest number of records possible. But the requirement demands many records in one shot (which we r showing on grid on UI) which depends on many tables with many filter criterias. U can imagine we have one whole screen for just selecting filter criterias based on differnt tables. Initial quey was slow because we also developing similar app in website and i thought that this query which takes less than 1 sec in sql server 2005 will execute in acceptable limit in sql ce. > He may be "leveraging" the indexes, but the end desire is the same. > To touch the smallest number of records possible while getting the [quoted text clipped - 3 lines] > think he went from one extreem (mega joins in a single query) to the > other (perform all the joins in app. code). Gagan, In your very first post you made the statement: "some queries i have inner joins on 9 tables some tables have more then 150000 records. Db size is around 15 MB. If i run that query with particular where clause (ex RoomNo. = 100) then it gives result in 1 minute otherwise i wait for 15 min. & i did not get results" Is 1 minute acceptable? Why does the query without (roomno = 100) not work? too many rows? Ginny, I've no problem with using resultset and tabledirect and I agree that they'll return the records quickest, but that doesn't solve the joining problems, expecially when we've now got 9 result sets (?) to manually join. joker, You're probably right that a hybrid approach might also work, but he'll still get best performance on a memory constrained device using SqlCeResultSet and TableDirect. Ginny > He may be "leveraging" the indexes, but the end desire is the same. > To touch the smallest number of records possible while getting the [quoted text clipped - 3 lines] > think he went from one extreem (mega joins in a single query) to the > other (perform all the joins in app. code). SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Hi Ginny, Did u mean that i can get rid of this out of memory exception ? > joker, > [quoted text clipped - 11 lines] > > think he went from one extreem (mega joins in a single query) to the > > other (perform all the joins in app. code). Gagan, Without seeing your app or your data, I can't say for sure, but I'm guessing the DataTable would be the first place to look. (And I realize you're using that approach since you've designed your app around filtering and databinding to it.) In general, SqlCeResultSet objects are quite small - the main thing that grows with increasing amount of data would be the internal collection of bookmarks. If you make changes to SqlCeResultSet objects, then you also get memory use there to store the before and after changes. I'm sure there's other stuff too, but mainly you are not duplicating the data in the database in the resultset objects if you just read through them. On the other hand, with the datatable, you are copying all the data from the database into the data table, and with a lot of data that will take quite some space. As a general rule with mobile apps, you only want to read in the minimum amount of data that a user needs at a time. This might mean a different UI for a mobile app than you use on the desktop or web app. I understand that you may not want a major redesign, but that might be what you end up doing. For example instead of loading a lot of data and then letting the user select the filter, ask the user for the type of filter first and only then fetch the data. I think this was the point that joker was trying to make, and it's a good one. You should probably also test with the data and temp folder installed on a storage card to see if that will solve your problem, but I don't know if it will help or if that is even something you can consider for your users. Ginny > Hi Ginny, > [quoted text clipped - 15 lines] >> > think he went from one extreem (mega joins in a single query) to the >> > other (perform all the joins in app. code). SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Hi Guys, I found another approach to overcome this issue. Currently i am using WCF proxy for synchronizing master tables from main server in wifi state. I am thinking to search from PPC and perform calculation at main server and then export the final result from there and save it locally. So later while extracting data locally from PPC to show in reports no need of calculation, queries or inner joins. It would be faster also. The only one blocking issue is how to tranfer data in bulk using WCF. Because currently when records grows more than 20000 it starts throw an exception can't allocate to much buffer. I am using looping in this case but it is slow it should be in one shot as per my knowledge we should be able to transfer up to 2 gb data or i am wrong ? Anybody has an idea? > Gagan, > [quoted text clipped - 45 lines] > >> > think he went from one extreem (mega joins in a single query) to the > >> > other (perform all the joins in app. code). Gagan, I don't have an answer for your WCF problem, but following this thread has made me really curious. What kind of device (handheld, screen size) are you using and what is the application (used by whom and where)? I'm just curious, because all the apps I've done are for either handheld ppc or kiosk type applications and I can't imagine trying to work with or display 20,000 records. Hi Joker, Our company is working for a US based project related to travel & hospitality industry due to some business reasons i can't disclose the name of client. but this project will be used in hotel industry. We are not going to display 20000 records at a time. Ya but the search for one report may generate more than 20K rec belong to those rooms who fit in this search criteria for that hotel. while displaying it will be on one room based. and i am woking on Dell Axim X51. Thanks for following this thread and try to help me for clear my doubts related to sql ce. > Gagan, > [quoted text clipped - 7 lines] > handheld ppc or kiosk type applications and I can't imagine trying to > work with or display 20,000 records. Gagan, I don't have an answer for the WCF question either. It seems you'll need to break the data request down into manageable chunks, but not knowing your data, I don't really know exactly how you might do that. Ginny > Hi Guys, > I found another approach to overcome this issue. Currently i am using WCF [quoted text clipped - 77 lines] >> >> > think he went from one extreem (mega joins in a single query) to the >> >> > other (perform all the joins in app. code). SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management |
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.