0

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?

|improve this question
  • 3
    Did you profile the web app? – Oded Oct 28 '11 at 13:28
  • I'm using Delphi 2006 for my project. I dont know if there is any support for Profiling. Let me look into that and report back. – Khuram Malik Oct 28 '11 at 13:38
  • 1
    stackoverflow.com/questions/368938/delphi-profiling-tools – P.Brian.Mackey Oct 28 '11 at 13:39
  • 238s (less 1s for your query and 1s the page with cached data) is insane! How much data is in the json object being returned? Personally, I wouldn't do the json serialisation in the DB, but there has to be something seriously weird for this sort of thing to take that long... – FinnNk Oct 28 '11 at 13:48
  • I think there is something wrong with your code, not a technology/infrastructure limitation and the various components of your system need to be profiled. That aside, it may be useful to set the Idle Time-out of your Application Pools in IIS to 0 minutes. This will make it clearer what in that time is actually processing and what is just an IIS thread starting up. – AlexC Oct 28 '11 at 13:56
2

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.

|improve this answer
  • Thanks @Iknowkungfoo . We've been serializing the json by effectively concatenating strings together. The SO post you link to is interesting but im unsure of how to implement that in Delphi.net since our project is in Delphi. I appreciate the lead on this however. – Khuram Malik Oct 31 '11 at 8:56
  • I eventually solved the problem last night based on the information you gave me above. It became quite clear that the serialization in .net was the actual problem so what i did was i used .net to invoke the SQL Command Line utiltily to output the serialized json from the database directly into a file. That process takes less than 1 second and from there i'm using .net to pass this data source onto the client. This whole process now has a roundtrip of less than 2 seconds. Problem mostly solved. Thanks again for your assistance. – Khuram Malik Nov 1 '11 at 9:40
2

In addition to Oded's great suggestion, take the time to boil down the problem. Right now you have IIS, SQL and the rest of your technology stack included in the problem. But, you already discovered that the issue only occurs during serialization. So you need to remove all the fluff.

Write a unit test that only performs serialization using Mock data. The mock data needs to represent the DB values in size and type (copy/paste the results into a string and parse it into a data structure).

Add to the test until you can reproduce the problem. If a Mock doesn't work, then hook directly into the DB. Then add another piece...etc. Then take away from the test until you narrow down the issue to only the technology involved.

If at that point you still can't figure it out, then you will have enough details and code to post a SO question to find the answer to the performance issue.

|improve this answer
0

I suggest you try something like VRTA to figure out where your bottleneck is. It may show that the server processing took all this time or that it is processing JSON in the browser.

|improve this answer
  • Thanks but it's definitely not a roundtrip related issue because even when viewing how long it takes to construct the json file on the server itself that takes a very long time and the processor usage goes way up to around 90% – Khuram Malik Oct 28 '11 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.