Why are my AJAX calls to a Database Server Slow ONLY From My Site Server?

Question

I hope this is the correct site to post this on. I wasn't sure if I should post here or Server Fault, but seeing as this involves the website perspective, I thought perhaps this community might be a little more accurate, but I'm not 100% on that.

I have been banging my head against the wall for over half a year trying to figure out just what's going on here. I would be ecstatic if I could track down why AJAX calls are slow when going through our Site Server.

I have built a small web-app for the organization I work for and it is pretty much set up like this:

The site itself (WebMatrix IIS Express site) resides on the Site Server, but (with the help of C#) it uses SQL queries to query a (considerably large) database on our Database Server.

The problem is that when my site performs the AJAX (simple jQuery $.ajax() calls) that requires it to query the database, the response takes over 5 seconds, each!

(Chrome Network Details):

(You'll see that some of the responses are really quick. These responses contain no or a lot less data than the other responses. Maybe there's a data limit somewhere that's causing the Site Server to analyze them?)

Now here's the kicker:

On the Development machine, the local machine the site is developed on, cuts out the Site Server, has the same code, and queries the same database, but the lag doesn't persist in this scenario. The responses in this scenario are in the low millisecond, just what I would expect it to be.

Here's what the Chrome Network Details look like from the development machine:

(None even close to 1 second, let alone 5).

Some More Specifics

• When launching this site straight from the Site Server, the lag persists.
• WebMatrix uses SQL Server CE, while the SQL Installed on the Database Server is SQL Server 2005 (I really don't think this makes a difference, as the query itself isn't anything special, plus it's the same code that's used in either scenario).
• The Site Server has been tested to see if the RAM, Processor, and Bandwidth are maxing out, but the truth is that running this web-app doesn't even touch the Site Server's resources. The same has been found for the Database Server, as well.
• The connection to the database is readonly (doubt this matters, just trying to give as much detail as possible).
• We have indexed the database on the Database Server, but it helped, virtually, none at all.
• Even though it is just an Intranet site, I am told that putting the site directly on the Database Server is not an option.
• At the moment, the AJAX requests are not asynchronous, but it should still not take this long (especially considering that it only lags from the Site Server and not from the Development Machine, even though the code is 100% identical in both cases).
• Probably doesn't make any difference, but I am in an ASP.NET WebPages using WebMatrix with C# environment.
• The Operating System on the Site Server is: Windows Server 2008 R2
• The Operating System on the Database Server is: Windows Server 2003

What could make this app work well from my local machine but not from the Site Server? I think the problem has to be the Site Server, given this, but none of its resources are maxing out or anything. It seems to only lag by about 5 seconds per request if the data being returned is over a certain amount (an amount that seems pretty low, honestly).

Truth is, I am hopelessly stuck here. We have tried everything over the past several months (we are having a similar problem with another Intranet site where the AJAX calls lag there, too, we have just lived with it for a while).

I don't know what else to even look into anymore.

In case anybody wants to see some code

jQuery (one of the AJAX requests, they are all just repeats of this with different parameters)

$.ajax({
    url: '/AJAX Pages/Get_Transactions?dep=1004',
    async: false,
    type: 'GET',
    dataType: "json",
    contentType: "application/json",
    success: function (trans) {
        for (var i = 0; i < trans.length; i++) {
            trans[i][0] = getTimeStamp(trans[i][0]);
        }
        jsonObj1004 = trans;
    },
    error: function (jqXHR, textStatus, error) {
        alert("Oops! It appears there has been an AJAX error. The Transaction chart may not work properly. Please try again, by reloading the page.\n\nError Status: " + textStatus + "\nError: " + error);
    }
});

C# Server Side Code (With Razor)

@{
    Layout = "";

    if (IsAjax)
    {
        var db = Database.Open("OkmulgeeCIC");
        Dictionary<string, double> dataList = new Dictionary<string, double>();
        var date = "";
        var previousDate = "";
        double amount = 0;
        string jsonString = "[";
        string queryDep = "SELECT ba_trans_entered AS transDate, (ba_trans_amount * -1) AS transAmount FROM BA_VTRANS WHERE ba_trans_year >= 2011 AND ba_trans_operator = 'E' AND ba_trans_system = 'AP' AND ba_trans_ledger LIKE @0 + '%' ORDER BY ba_trans_entered ASC";
        string dep = Request.QueryString["dep"];

        foreach (var row in db.Query(queryDep, dep))
        {
            date = row.transDate.ToString();
            date = date.Substring(0, date.IndexOf(" "));
            amount = Convert.ToDouble(row.transAmount);

            if (date == previousDate)
            {
                dataList[date] = dataList[date] + amount;
            }
            else
            {
                dataList.Add(date, amount);
            }

            previousDate = date;
        }

        foreach (var item in dataList)
        {
            jsonString += "[";
            jsonString += Json.Encode(item.Key) + ", ";
            jsonString += Json.Encode(item.Value) + "],";
        }

        //jsonString += Json.Encode(date);
        jsonString = jsonString.TrimEnd(',');
        jsonString += "]";
@Html.Raw(jsonString)
    }
    else
    {
        Context.RedirectLocal("~/");
    }
}

ADDITONAL INFO FROM SQL SERVER PROFILER

From Development Machine

From User Machine (lag)

Answer 1

Just looking over your code two things jumped out for me

1) You're not closing your db connection, this is very bad. Either wrap your connection object in a using block (preferred) or add a call to .Close() at the end of your data work

using(var db = Database.Open())
{
     //do work
}

2) Doing string concatenation in a loop like that is a terrible thing to do and very slow. Either use a StringBuilder. Or since you're outputting JSON anyway just bundle your objects into a list or something and pass that to JSON.Encode() (preferred)

Answer 2

It seem to me this problem come from your Site Server but anyway you can try this: 1/ Publish your site to any Internet web server. if it is still slow => your code problem -> Check your code. If not go to 2/ Check your configuration Site Server and Database Server. It might be Firewall or TCP/IP:Port or NETBIOS/Domain name between two Server.

Answer 3

I do not know if this has any relevance to this problem because i cannot see how you are calling your application. But I have multiple times experienced about 5 sec lag on IIS using C# when i use domain names to call other servers (this can also be localhost). Instead the ip should be used.

It could be nice if you tried playing around with this using IP instead of using a domain

Answer 4

I had something similar when working with AjAX and JSF site.

Jquery loading taking excessive time

Since you already have it working from dev machine, it might not be a problem in your case. But to rule out any such scenario, can you develop the page without using jquery ?

Answer 5

I had a similar issue where I would call 20 sprocs using a for loop, they were not large sprocs mind you but a sproc that would return 5 values.

It would work fine but time to time it would almost like lag out and would not be able to load any of the sprocs or a very small amount until timing out completely.

That is when I discovered Parameter Sniffing for SQL Server.

To fix it I added in sproc Parameters equal to the incoming parameters from my C# code.

OLD CODE:

CREATE PROC [dbo].[sp_procname_proc]
(
   @param1 int,
   @param2 int,
   @param3 varchar(5),
   --..... etc .....
)AS
BEGIN
  -- select from db
END

NEW CODE

CREATE PROC [dbo].[sp_procname_proc]
(
   @param1 int,
   @param2 int,
   @param3 varchar(5),
   --..... etc .....
)AS
BEGIN
  @localParam1 INT = @param1
  @localParam2 INT = @param2
  @localParam3 varchar(5) = @param3
  -- select from db using new parameters
END