| Author |
Topic |
|
rogdawg
Starting Member
23 Posts |
 Posted - 2008-05-16 : 16:57:08
|
| I have an interesting problem:I have an ASP.NET web application that uses a Treeview control to display what can potentially be a very large data set. In the past, I would just run a recursive stored procedure in my database that would output the XML which I would save to a file. The Treeview used the XML file as its data source. I did this because it can take so long for the stored procedure to run (10 seconds or more) that, it isn't practical to have the treeview point directly to the stored procedure. This worked well enough because the data didn't change very often. Now, it looks as if the application will be used in a production environment, and I really need to find a way to supply up-to-date data to the treeview in a dynamic way. I have tried creating a view that would provide XML and that would be updated any time the target table is updated but, that has not worked. I have also tried creating a trigger that would output to an XML file any time an edit was made (using the xp_cmdshell functionality) but, that has proven difficult as well. Is there a simpler solution that I am just missing? I just want an up-to-date XML representation of the data that is a result of a recursive function. Thanks for any help you can provide. |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2008-05-16 : 20:17:52
|
| Lets see how you generate the XML. If you can get that fast enough it seems that the other problems will simply go away and you can call it at runtime when the treeview control fires.I recently solved a similar issue by having the application cache the object on the web server. We then created a web service that we call directly from the db (via CLR) to expire the cache whenever a modification to the underlying tables occurred... basically telling the app to come refresh the object.Nathan Skerl |
 |
|
|
rogdawg
Starting Member
23 Posts |
Posted - 2008-05-17 : 07:53:26
|
| Hmmm, that sounds like an interesting option. I haven't used the CLR in SQL yet but, maybe this is a good reason to do that. If I cache the XML result, and just refresh the cashe from the DB anytime an edit is made, that might be the best way to do it. The new project we are doing will have a very small data set for a few weeks, so I can just get the xp_cmdshell approach working and output to a "static" xml file for the time being. Then I can get the caching approach working before we go into heavy production.Thanks! |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2008-05-17 : 12:22:15
|
| If you want, post the XML generation procedure and we can try and optimize that as well. Nathan Skerl |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-05-19 : 02:24:48
|
| ...or you can make the procedure only get a part of the treeview using AJAX-controls or something. For example you can initially get the two top levels of the treeview and then when a user clicks the third level an ajax-control will get the contents dynamically but in real-time. That way your procedure will separate the work in smaller chunks, hence it will work faster.--Lumbago |
|
|
|
rogdawg
Starting Member
23 Posts |
Posted - 2008-05-26 : 11:23:07
|
| thanks for these suggestions.I have considered the option of only loading part of the tree, and then loading the remainder as needed. But, I have not attempted it using Ajax. That might be the best approach. I will test that approach, along with the caching uption, once I get the initial, limited, version of the application complete. Thanks again for your ideas. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-26 : 11:43:10
|
| so why don't you just use create the tree xml with sql server's FOR XML and bind your tree view to it?_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
laskar78
Starting Member
1 Post |
Posted - 2008-07-03 : 15:13:43
|
| This is exactly what i'm trying to do is it possible to see how you did it.Thanks |
|
|
|
|
Efficiently supplying xml data for treeview