ADO performance is slow but query analyzer is blazing
This question was posted earlier by mccainz but the accepted answer turned out not to work so I am posting a more complete version for for him.
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
Here are the 3 ADO executes:
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
BTW:all attributes of the result set are left to the default.