Sudden performance loss
-
I have a VB6 application which connects to an SQL server 2000 DB. On one particular form the application populates a tree view control from the DB. The total number of records in the table is about 1000. I admit this routine is not particularly efficient as it makes several roundtrips to the server but that is not the problem. The problem is that this routine used to take approx 5 seconds then overnight (a couple of weeks ago) it started taking nearly a minute (this i the same for all of the PC's connected which is between 5 and 10).
I know this is not the application causing this and have tested it with the same data on my network and it takes about 5 seconds. the client insists that the only change was an upgrade of macaffe anti-virus which they have now reversed.
Is there anything else that could be causing this or is there any way to monitor the performance?
Jon
-
Have you used the Windows Performance Monitor to check the state of the server you're using?
What happens when you run the SQL from an editor, such as Query Analyzer?
Sometimes, over enthusiastic anti-virus programs can slow applications to that extent, but to make sure, you need to see how well the SQL works outside the app, and also on a fresh machine with no anti-virus installed (or at least disabled temporarily).
Use perfmon in Windows to check disk IO, processor usage, and anything else you'd like to look at on the server.
-
Is there an index on the data. Run dbcc showcontig on the table to see if your table is overly fragmented. If you are doing a lot of inserts/deletes into the system you could see some dramatic lag time in your queries.
Tom
-
I can think of two possible reason, one mentioned by Tom regarding the indexes set up and the the other reason could be that you are not cleaning up your connections thus eating up connections and memory space - this would have a degrading effect on your performance too.
Hiten
-
Thanks all but i dont think the problem is anywhere related.
The client runs perfmon on the server and says nothing is out of the ordinary (no reason not to believe him, he is an experienced IT manger who cares for several large networks). The table in question has a single index and only 1300 records created over the past year, no deletes are allowed. A reindex is run through a job every week for the DB and takes seconds to run. I have checked the connections and there are never any dead ones.
The problem really is that this massive reduction in performance happened overnight, ie Wednesday everything was fine, Thusday morning everything was bad.
Jon
-
Have you tried running the query through query analyzer and not through the program? What was your performance?
I have seen query's go from 1 sec to 20 min overnight because of a few hundred rows being added. Sql chooses a different access path and the performance goes to down. Run showcontig, run the query with statistics_io on.
Let us know what the results are.
Tom
-
.. I have checked the connections and there are never any dead ones.
How are you doing that? The issue is returning the connection to the connection pool abruptly after you are done with it, as in
Set myConn = Nothing
So where can you check that there are no dead connections?
For a test, I'd make another VB6 form and put only the treeview code in it, in order to isolate the problem. If it still takes a long time to load, then check to see whether you are using a bound control.
If so, unbind it and manually create and open a connection then load the treeview. If still slow, put time logs in to see for sure that it is the loading of the treeview.
All in the interest of trying to solve the real problem. Maybe it's gone slow connecting, for example.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply