Our new SF application has been in full use for over a month. All, for the most part, has been going extremely well. Just recently...perhaps in the last week or so...we have been encountering a ton of SQLConnection errors at the remote end of our T1 line (10 - 20 per day). I have been doing my best to examine each window to best utilize the tips that I have gotten from all of you in the past, but now, I am stuck trying to figure out the origin of these errors (so many windows...so many queries). I assume that I could fire up the SQL Profiler, but there is so much data that comes flying through there, I am not sure how to best filter it to get to the root of the problem. I have a suspicion that there is one window or even one query that is gumming up the works for everyone else. I'd appreciate any tips or tricks for helping me track down my pesky connection problems.



Thanks!!

Bill

Gotta love those transient issues! Using the Profiler, I can see that there are about 1000 user and system queries running against the server every few minutes. That does not seem too excessive to me (although, I do not have any benchmarks for that stat). I have watched that query in the Profiler. Oddly, like a watched pot, it never boils (uh, fails). The connection does not timeout when I am looking. Typical.







I like this idea. Easy to implement and I could set it on my admin menu so that only the IT group can access and test. That will not take too long to setup. Maybe this will shed a little light on this dark corner of my application and network.



Thanks!!

Bill

I created a small Windows Forms app and pointed the database to a web-based SQL Server (controls a DotNetNuke website). The app is very simple. I have basically three forms...the main and two maintenance forms. The main opens without any problem (no security involved, yet). When I open either of the maintenance forms, it takes 20-30 seconds before the form is displayed. There are only a few records (2 or 3) that the app has to retrieve. I am not sure I understand why there is such a delay. The connection tests successfully instantly. I can open a table in Management Studio and get a near instant response. Yet, when I utilize StrataFrame, and in this case the Business Objects, I get delayed response times. I must be doing something fundamentally wrong with the framework. I share this here because of the similar issues with the remote subnet mentioned above. Both of these apps share similar network speeds on different networks. One is T1 and the other is DSL.



Maybe this additional information will provide an "AHA!" moment for someone. To me it is not at all obvious what I am doing wrong. This is something that I now need to address with two different installations.



Thanks, again!

Bill

Bill, there is really not much between SF and the connection. I would be curious to see what your results were if you just created an SqlConnection and used an SqlAdapter to populate a DataTable with the same query you are performing within SF.



The one thing that I know is that there is nothing between the BO and the DAL that would cause anything like this, so there has to be something going on within a query, sproc, or connection. It will have to be one of these things.



Now, if you can provide the database and a sample that reproduces this, I will be more than happy to give it a shot on this side to see if the results are reproducable. Performance issues are always frustrating, but I really can tell you that SF should have nothing to do with this part of the formula.



So some additional questions I would post are:



1. If you use the raw SqlConnection and SqlAdapter to fill a data table, what are the results?

2. What is your query and are you using an intrinsic SF method (i.e. Fill methods)?

3. If you are calling a sproc, what does your SPROC look like and how are you calling the sproc?



I know that the answer lies within these questions as the ONLY time I have ever experienced slowness in SQL (including through SF BOs) is when there was a legitimate query issue (unoptimized ... which I am good at writing those first ) or a permission or connection issue. I have also had issues in the field with a proxy and firewall (and a few times NAT translations) causing the issue. So my point is that the connection between the app and the server and SSMS were not taking the same path.



One more thought, if you are using SSMS it is most likely using named pipes, not TCPIP. If you are connecting through your application, you are most likely connection via TCPIP...thus you can experience differences in performance. Make sure that you have your TCPIP traffic on the server and between the two points setup correctly as well. This can cause slowness also.