Hi,
I've been having a really hard time with the WHDG performance. I have a parent and child band which are being fed by two sqldatasources into one WebHierarchicalDataSource. The query in the child's datasource is simply "select * from [jobparts]." Every time I hit the expand button, that sqldatasource runs a "select * from [jobparts]" which returns thousands of records and then picks out the one or two rows it needs to display in the child band. (You can see that by watching the sqldatasource_selected event and checking the affectedrows property of e)
So I tried using manual load on demand to load the data in code in the rowisland_populating event when the expand button was hit. Same problem, since the sqldatasource of the child is still connected to my webhierarchicaldatasource, it does the whole select process and THEN it populates the WHDG through my code. So then I changed the query in the child's sqldatasource to something that doesn't return any records but that makes me lose the expand button on the parent band all together so I can never get into the rowisland_populating event at all.
Is there a way to set this up so that the child datasources only select what they need from the database? I was thinking of setting up some crazy custom paging scheme for the chld band queries, but I don't have enough Tylenol in the office to start thinking about that.
Edit: I just figured out a possible way by using the WebHierarchicalDataGrid1_RowIslandDataBinding event and changing the selectcommand of the sqldatasource of the child there. I have to make sure not to change it when the parent grid loads so that the expand buttons will be created. I'm not sure how to get the parent rows datakey in that event though.
Thanks, Ed
The reason the grid hits every row's children is that it has to know whether the row has any children and whether the expansion indicator needs to be rendered.
Just like Dave suggested I can point you to using the manual load on demand, and the IsEmptyParent can be played with to instruct every row if it is supposed to have child rows. Although I'd imsagine you on your own will have to touch the db to figure out that.
If the property is left untouched the indicator will appear on every row in the manual mode, then clicking it will do nothing for childless rows.
This is an important post. I think some of the developers should weigh in on this one. It's extremely limiting of the WHDG to constantly select the entire database.
Yes, and I have used that in other parts of my site. In this case though, I have many thousands of records and the initial load is unacceptable as well as the paging loads. Once, we figure out a good way to do some smart data selecting, I would actually want to load all the data and child data for an entire page at a time.
Either way, the InitialDataBindDepth is a helpful property to be mentioned in this post.
I was just experimenting with this yesterday, so thought I'd chime in... With a combination of EnableDataViewState="true" and InitialDataBindDepth="-1" (or maybe "1" in your case) you can avoid all trips to the databae on child expand. However, the trade-off is that it loads more data on initial load and stores it in the page (presumably in ViewState) which makes for a larger page being sent back and forth across the line. Note that it will still hit the database on a page change if you are using Paging.
The reason that the parentrow is empty is because I'm doing it on the rowislanddatabinding event which means that the child row is not there yet. How can I get some parent row info at that stage in the child band binding process?
In the meantime, what do you guys say in general how to not retrieve all records from the database when expanding child rows?