I have spent the best part of a month working on the jQuery Calendar plugin.
The setup is such that all the calendar entries are stored in a table inside SQL Server and im using a single "Select As" statement to construct the json data within the SQL query itself.
This has an execution time of less than one second. However, calling the actual final aspx page in the browser requires a wait of over 240 seconds while the data is passed as json to the browser.
I've tried various methods to get around this.
For example, i tried writing the query results to a json file first, and then calling the file in on the final page. This reduces the page load time to around 90 seconds but still not fast enough.
I've also implemented a function whereby if the data hasnt changed the same json file is returned and is not re-generated. When there is no data that has changed on the calendar this reduced the page load time to around 1 second. Im convinced that writing the json data into the file is the bottleneck here..
But the real question is, is this as efficient as things are realistically going to get with IIS and .net? Or is there room for improvement?
I also dont know which aspect to investigate further now. I dont know whether to consider if IIS is causing the hold up, or the way i have put the project together.
Is there a more efficient solution that the one i have put together above?
Best Answer
Given that SQL Server can covert to XML simply by using "AS XML", you'd think they would have added "AS JSON" at least in the Denali release. We took a stab at creating a .NET class to give us that functionality on the database, so we could get JSON back instead of a query object. No matter what was tried, the conversion was unbelievably slow.
Check out this SO post. It shows how to convert your record set into JSON using .NET.