Nhibernate Azure Performance

I am working on a web-app with ASP.NET MVC3. We are using nHibernate with SQL Azure. We use default setting of nHibernate.

There are a few observations that I made as part of performance analysis of our app with respect to SQL Azure.

1. Every query takes about 280 ms to execute on Azure. I am not sure if this timing is high or normal. If Anybody has any counters please share. The same query takes about 1 ms to execute on our internal SQL Server.(The PK, FK and indices are same on Azure and local server. In fact data is also same)

2. We have a page to show a search form. It has around 13 fields that need to be po开发者_如何学JAVApulated from DB. When I load that page, the each query takes about 280 ms. Now if I stay idle for about 5 min and then reload the page, the first query takes about 1.8 sec(yes nearly 2 sec) to execute. Rest of queries take same time as noted before. What would cause this spike? (I am totally blind on this. Any hint would be appreciated.)

---

As others have commented above, check if your SqlAzure instances are in the same datacenter as your web/worker roles. If they're in the same datacenter, then the two should communicate via the datacenter's internal network. If not, they'll be connected via much slower links.

The stats you provide above indicate that there's around a 250ms latency for SQL queries. That indicates that there may well be a significant distance between web role and SQL instance.

Ping your SQLAzure instance from your web/wroker role instance. Then, open Resource Monitor and look in the network tab and look for TCP connections from your w3wp.exe to your SQL instance's IP address. You can then see your worker role's averaage TCP connection latency.

As you can see, our worker role's average latency is 10ms or less. If you see a connection latency much above 10ms, chances are that your SQL instance is in a different datacenter.

Unless you have specific reasons as to why you WANT SQL to be in a different datacenter, I STRONGLY encourage you to move your SQL data to an instance in the same datacenter as your worker roles.

HTH.