Hey guys- We have an internal site we're hosting on Winhost with a couple SQL Server databases. When I build a release and push it to the server, it runs fine (fast). However, when I try to run on my local machine (either with Visual Studio's development server or with IIS Express), the database calls are extremely slow (rendering a page with only a couple dozen reads can take up to a minute). Like I said, it works great when it's actually running on the server. It's just when it's on a local machine (we've tried 3 different dev boxes) that we hit this problem. We're accessing the production database directly from the local machine with the exact same connection strings. Any thoughts?
It sounds like the distance between you and our SQL server is vast. Remember the Winhost web server and the Winhost SQL server are on the same network (LAN) so the distance between them is really small.
I have to admit, I'm a bit disappointed by this answer. Granted, I'm not on the same LAN as the database server, but I have a fast connection and it's not requesting all that much data. When I get the page from the server (which has to execute all of the queries, create the html, and send the whole thing back to me) I get the response in under one second (just ran in 947 ms in a quick test). It really seems like there's a huge overhead for executing a query. I don't know enough about SQL Server to know if perhaps there's a setting that I'm missing? Should executing 30 queries really take 30 times longer than executing 1 query? I would there would be a common connection and session that would give economy of scale. At any rate, thanks for the quick response, and feel free to offer any other advice you might have.
A - Run a traceroute to the SQL server and post your results. B - How long does it take the queries to run in management studio? C - Couple of Dozen queries per page? Thats a lot. Also, how complex the queries are will play a huge role.
Hey Chuck, thanks for the quick response. A. Tracing route to s01.Winhost.com [96.31.35.201] over a maximum of 30 hops: 1 1 ms <1 ms <1 ms 192.168.1.1 2 * * * Request timed out. 3 7 ms 20 ms 30 ms ge-9-12-ur02.eagan.mn.minn.comcast.net [68.85.165.221] 4 7 ms 60 ms 33 ms te-2-4-ur01.newport.mn.minn.comcast.net [68.86.232.82] 5 81 ms 7 ms 7 ms te-8-3-ur02.newport.mn.minn.comcast.net [68.87.174.86] 6 10 ms 31 ms 8 ms te-0-2-0-0-ar01.roseville.mn.minn.comcast.net [68.87.174.122] 7 43 ms 34 ms 18 ms pos-0-15-0-0-cr01.350ecermak.il.ibone.comcast.net [68.86.94.81] 8 93 ms 73 ms 19 ms pos-1-3-0-0-pe01.350ecermak.il.ibone.comcast.net [68.86.86.158] 9 18 ms 19 ms 19 ms ber1-tengig3-3.chicagoequinix.savvis.net [208.173.180.41] 10 20 ms 20 ms 29 ms cr1-tengig-0-5-0-0.chicago.savvis.net [204.70.196.21] 11 79 ms 125 ms 74 ms cr1-pos-0-3-3-2.losangeles.savvis.net [204.70.192.145] 12 74 ms 87 ms 75 ms hr2-te-1-0-0.elsegundola1.savvis.net [204.70.203.89] 13 75 ms 77 ms 75 ms hr1-te-1-0-1.elsegundola1.savvis.net [204.70.203.69] 14 75 ms 103 ms 87 ms hr2-te-1-0-0.irvine2oc2.savvis.net [204.70.204.193] 15 75 ms 87 ms 76 ms hr1-te-1-0-1.irvine2oc2.savvis.net [204.70.204.165] 16 137 ms 77 ms 77 ms 64.79.173.6 17 99 ms 89 ms 80 ms 96-31-33-196.hostcollective.com [96.31.33.196] 18 76 ms 77 ms 78 ms 96-31-33-227.hostcollective.com [96.31.33.227] 19 101 ms 75 ms 130 ms s01.Winhost.com [96.31.35.201] Trace complete. B. Doing a simple read query in SSMS is taking between 200 and 250 ms. C. True, but these are quite simple queries. I'm basically all the records from one table (about 20), then doing iterating through each record and doing a JOIN against another table. This could probably be consolidated into one query by hand, but I'm just letting Castle Activerecord handle it. This isn't the end of the world, we've been setting up local development databases for this particular project. It's just one of those little things that we're fine mucking around with the production DB since it's just internal and would be nice to not have to maintain dev environments.
