SQL Server generate static HTML on a schedule
-
I have a problem that I wonder if you all can help me with. I have a table that could be updated every second, or every ten minutes (or more). I need to get the data from that table to provide real-time representation on a web page. To minimize the round trip to and from the db on the network, I had suggested that we retrieve data from the table and hold onto it in the Application Cache which refreshes itself every x seconds so that if many users are trying to view the data, they all pull from the Application Cache, not from SQL Server. However, it has also been suggested to me (by my supervisor) that we code a Trigger or a Job in SQL Server to generate a static HTML page (either on a schedule, or every time one of the fields in the table is updated) so that all requests for the page get handled by IIS and don't require a trip to the db. I can't imagine that this is a good programming practice... and I don't expect for more than about 50 users at a time (probably more like 10-20). Can anyone provide me with good arguments for either method? Thanks
-
I have a problem that I wonder if you all can help me with. I have a table that could be updated every second, or every ten minutes (or more). I need to get the data from that table to provide real-time representation on a web page. To minimize the round trip to and from the db on the network, I had suggested that we retrieve data from the table and hold onto it in the Application Cache which refreshes itself every x seconds so that if many users are trying to view the data, they all pull from the Application Cache, not from SQL Server. However, it has also been suggested to me (by my supervisor) that we code a Trigger or a Job in SQL Server to generate a static HTML page (either on a schedule, or every time one of the fields in the table is updated) so that all requests for the page get handled by IIS and don't require a trip to the db. I can't imagine that this is a good programming practice... and I don't expect for more than about 50 users at a time (probably more like 10-20). Can anyone provide me with good arguments for either method? Thanks
I have to agree with your supervisor on this one. Always go to the source when you can, otherwise you risk presenting invalid data. I would not build new HTML on every update. I would use a version based system for speed. Example HTML_Table =============== HTMLid int Updater int HTMLData NTEXT Every time you rebuild the HTMLData you set the Updater to zero. UPDATE HTML_Table SET Updater = 0, HTMLData = "whatever" WHERE HTMLid = 1 Every time you update a table that can change your HTML, increment the Updater: UPDATE HTML_Table SET Updater = Updater + 1 WHERE HTMLid = 1 Every time a page needs the HTML: (1) If the updater > 0 the rebuild HTML (2) return HTML This part would best be implemented in a Stored Proc. Remember, SQL Server will cache the last few requests on its own - no need to build your cache system that never knowns when it is out-of-date.