April 6, 2011 at 1:52 am
We've been testing SQL Server 2008, TSQL and writing .NET Forms based applets (mainly grids) to access our data
As with SSMS, or once a .Net applet has been launched, the processing of the query and returning of rows into an applets (or SSMS's) grid are blindingly fast
What is slow is the actual opening of the app or SSMS
Our ICT people are fine with leaving SSMS open all day to get round this problem but wonder what is the best option for our users?
They are used to opening and closing an applet as required but it takes from 5-10 secs to open each time and we want to eliminate this delay
At some stage we may develop a master app containing a navigation interface which is opened in the morning at logon and closed at the end of the day, but this is still some way off
Therefore is there a way that we can get round this delay by opening a connection at startup and keeping it open - perhaps in the notification area of Windows?
Is this a recommended way of doing things? and are there examples of how best to achieve this - or something similar - in .NET with Forms apps?
April 6, 2011 at 2:16 am
I'm confused how leaving SSMS open all day has an impact on the speed a .Net form opens/closes?
Opening connections is (relatively) expensive, but I wouldn't expect if to take 5-10 seconds in a normal environment. Are you sure this is the actual time spent establishing a SQL connection rather than just loading the app? Most applications take 5-10 seconds to actually load and display a GUI in a Windows environment in my experience, so it doesn't seem to be an excessive amount of time to wait if it's fast once loaded.
Most applications will hold a connection to the database while they're open to avoid an excessive amount of connections, but there's not much you can do when the application is closed, other than as you say, prevent it being closed.
Another thing to look at might be to deliver the application as a web app and use connection pooling to hold a handful of connections open at the server level regardless of client behaviour.
April 6, 2011 at 2:46 am
sorry for the confusion
In essence I don't really have concerns about SSMS app taking 5-10 secs for reasons already stated
but I do have concerns about the 5-10 secs taken in loading up other apps
Currently most of our test apps are variations on grids that access Tables or Views and once these apps are launched they are super fast
Are you saying that any/all apps take 5-10 secs? then how come Word 2010 is up and running in less than 1 second, as does Excel 2010?
The footprint of our applets are tiny in comparison to these two behemoths, and when we design an app with an empty grid that does not connect to the database then it is also very fast to launch, so assumed that it was the making of the connection and perhaps checking login security and/or something else that was making these load slowly.
Maybe my assumption is wrong?
April 6, 2011 at 2:59 am
If it's very slow compared to other applications and quick when it doesn't have to establish a connection, then something's up, but 5-10 seconds just for the connection seems very slow indeed - is the SQL Server on the local LAN? How quickly does a connection to the server take in SSMS when SSMS is already fully loaded? This would normally take a fraction of a second.
If it's also slow in SSMS, I would look into network latency and whether the server is under memory or CPU pressure.
April 6, 2011 at 3:20 am
Yes Server is on the local Lan and very under utilised with little load as just being tested by a handful of people and just the odd applet for now
As stated earlier queries run blisteringly fast and no complaints in this department - in fact nothing but fulsome praise here
Once an app is running reloading/refreshing/saving, updating are all either very fast or acceptably so
SSMS takes about 3-4 secs to the 'connect to server' dialog and 1 sec or less after
I suppose I need to do more tests on whether it is the loading of the app rather than connecting to the server which is the bottleneck
Thanks for your help
April 6, 2011 at 5:47 am
I wouldn't compare SSMS to your app. I presume they're doing different things. SSMS is loading all sorts of different bits of data as it opens and connects, stuff your app should not be doing.
I would get into your app code and walk through it and identify exactly where the bottlenecks are. Not simply, "it's slow to open" but what steps is it performing to open, how long does each step take, etc. If you get a .NET profiling tool you should be able to break that down. Take a look here[/url] for an example.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 6, 2011 at 8:10 am
Slowness at application startup is caused by allocation and initialization of various resources. Some will be memory and process related, some network and some authentication, depending upon many factors and just exactly what your application is doing at start up and how much data it is accessing and by what methods. Of course, all of this puts the demands upon the server that the application is running on and is dependent upon what other applications are running there and what resources they are consuming.
If you could enumerate this in detail I could comment further. If it were me, I would write some checkpoints to a log file during application initialization and operation. With sufficient granularity you should be able to tell where the bottleneck is. Well designed .net apps should be lightning fast, if not they need fixing or re-design.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply