Solved

ADO performance is slow but query analyzer is blazing

Posted on 2001-08-30
26
379 Views
Last Modified: 2012-05-04
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;" & _
                     "database=fidojoe"

Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)

BTW:all attributes of the result set are left to the default.
0
Comment
Question by:dbdoshi
  • 9
  • 6
  • 3
  • +5
26 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6441674
(EE ate my last comment so I will try again)

Try this to see if any better
conn.Execute sqlstr_create_view Options:=adCmdText + adExecuteNoRecords
Set resultset1 = conn.Execute sqlstr_get_providers_by_DMISID Options:=adCmdText
conn.Execute sqlstr_drop_view Options:=adCmdText + adExecuteNoRecords

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6441677
I supposed the bigger question, is qould it not be better to convert over to a Stored Procedure?  But I am sure you have considered that.

Anthony
0
 
LVL 1

Expert Comment

by:songstre
ID: 6441787
dbdoshi,

Is this a multi tier app or a simple client server app?
0
 
LVL 3

Expert Comment

by:MCummings111400
ID: 6441823
I don't know if anyone has suggested this yet, but try using a DAO/ODBC connection instead of ADO/OLEDB and compare speeds. My expierience has shown that ADO is incredibly slow, and unless there is a specific reason to use it (functionality) I always use DAO/ODBC. If nothing else, it'll prove whether it's the components or a network issue.
0
 
LVL 1

Expert Comment

by:songstre
ID: 6441897
I think that if the app is distributed throughout Germany and Italy, changing the technologies might not be an option (unless they are working on a new release).
0
 
LVL 5

Expert Comment

by:mccainz2
ID: 6442007
My original question and I will be handling comments etc from now on, thanks dipen.
The program is not multitier...it is a simple client server.
I am busy testing ACperkins comments now so please be patient as this thread has my entire attention and I will try anything to resolve this problem.
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6442020
dbhoshi,

You say you havent monkied with the attributes of the ADO result set so they are all set to default.
This might be one of the problems.
The default for:
  CursorType = adOpenUnspecified
while
   LockType = adLockUnspecified

do you need these general cursors or would it are you basicly working with the recordsets as static recordsets.

Also it is better to execute the sql from the ADO Recordset.

Whould you lilke some code?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6442776
dekeldate

I beg to differ, if you are refering to the Open method for the Recordset object then the default for the CursorType is adOpenForwardOnly
And for the LockType it is adLockReadOnly.

I also disagree that "it is better to execute the sql from the ADO recordset"  Conection.Execute is going to give you better performance and is also a good way to make the Errors object available.  However, as I pointed out in a previous post, the best performance can probably be achieved by using a Stored Procedure.

Anthony
0
 
LVL 1

Expert Comment

by:mupchu777
ID: 6442848
Recordsets add overhead as they are expecting data to be retrieved.  Since many of the above commands are of the execute type (perform action but don't return data), it is far more optimal to use the execute command from the database (no need to create unnecessary objects as well as overhead).  Other than that the best solution is a stored procedure as anthony has said at least twice now (and I've reiterated it twice as well -- see a pattern here :) )
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6442876
acperkins

Your right it is always better to use a stored procedure(SP).This said, I don't think a performance problem like what dbdoshi is experiancing would be caused by the differene between a query and an SP.

You say that CursorType is adOpenForwardOnly, And for the LockType it is adLockReadOnly. Would you please give me a reference for that. When I look at my MSDN I get
    Syntax
    object.Open [Source], [ActiveConnection], CursorType As CursorTypeEnum = adOpenUnspecified], [LockType As LockTypeEnum = adLockUnspecified], [Options As Long = -1]

I have found the settings
   CursorType = adOpenStatic
   LockType = adLockReadOnly
to work well for most situations
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6442914
dekeldate
Absolutely. Take a look at the definition of the Open Method (ADO Recordset)  Here is the link:

http://www.microsoft.com/Office/techinfo/productdoc/2000/en/excel/ado210/mdmthrstopen.htm

Also, while you are there take a look at the definition for CursorType and here is the link:
http://www.microsoft.com/Office/techinfo/productdoc/2000/en/excel/ado210/mdcstcursortypeenum.htm

Anthony
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6442964
acperkins
Thanks, I supposed the adOpenUnspecified default to these values in the recordset object.
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6443040
First, is there a reason you are not using stored procedures?

If not, it might be possible to to put all three commands within one stored procedure.

Secondly, this should improve things whatever you use.
For the commands that do not require reordsets use
 cnn.Execute sqlstr_create_view, , ADODB.adExecuteNoRecords
also after finnishing the three commands
 cnn = Nothing
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6443101
dekeldate
>> cnn.Execute sqlstr_create_view, , ADODB.adExecuteNoRecords  <<
Take a look at the first comment.

>> cnn = Nothing <<
This has no effect on performance.

Anthony
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6443126
Oops, sooo sorry about that.

cnn = Nothing
might help if the connections are not released especially when there are lots of users online.
0
 

Expert Comment

by:Dan_A
ID: 6444254
Here's one, Goto to
 www-svca.mercuryinteractive.com/products/loadrunner/

This is the Mercury Site. Mercury has tools to Stress Test all-types of Apps. (e.g. Virual Users) etc. See if you can download demo to pin-point slow performance.

Other than that: I found 2 things that I always need to watch for in my Sybase enviornment

First: Never reuse an existing connection / recordset on a select (e.g. SET your recordset to Nothing (RS = Nothing)
and Connection to nothing (CN = Nothing)

Second: Always do a Showplan on your SQL, Making sure your not table scanning. Sometimes in the Sybase world, Sybase Throws out my index so I need to force it (e.g. SELECT * FROM TABLE (INDEX THE_INDEX)

For what its worth, Hope this helps ....

Dan A.      
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6444306
Second Dan_A's comment.
It might help to set cnn=Nothing between the commands.

something like
connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
                    "User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
                    "database=fidojoe"

set cnn = new ADODB.Connection
cnn.open connection_string
conn.Execute (sqlstr_create_view)
set cnn = Nothing

set cnn = new ADODB.Connection
cnn.open connection_string
Set resultset1 = conn.Execute (sqlstr_get_providers_by_DMISID)
set cnn = Nothing

set cnn = new ADODB.Connection
cnn.open connection_string
conn.Execute (sqlstr_drop_view)
set cnn = Nothing

seems like a lot of extra code but usualy speeds things up.

Of course the best thing would be to put all this into one stored procedure. Also check your showplan as Dan_A's said

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6448522
dekeldate

I believe we are losing focus here, the original question had to do with performance and specifically why SQL Analyzer is consistenly faster than ADO.

First of all, the Connection object should be set to Nothing at the end of the procedure as it ensures that the resources are freed up.  But it makes no sense to do this after every Execute.  How can instanciating an object, Executing and destryoing the object many times in the same procedure make the performance any better?

Anthony
0
 

Author Comment

by:dbdoshi
ID: 6451692
Thanks AC....I was scratching my head over that one also.
0
 
LVL 1

Accepted Solution

by:
dekeldate earned 150 total points
ID: 6451720
Yes I do believe that was a bit off the problem.
dbdoshi
Did you try using the recordset.open? I know I may seem persistant but all the example code I am seeing for things like this use recordset.open when they are returning a recordset.
0
 
LVL 1

Expert Comment

by:songstre
ID: 6454469
It is possible that alot of resources are going to opening the connection? Query analyzer opens the connection when you open a SQL Window. So, when you execute the SQL, your connection has already been made. Are you including the connection time from the client in the execution time?
0
 
LVL 3

Expert Comment

by:MCummings111400
ID: 6471407
Ok. Two more questions for clarification.

1) The percieved 'performance issue' you see is the time diff between executes of 3 identical queries run in SQLEM and from a VB Application.

2)Have you done a SQL Trace to log the runtimes of the three statements from both the SQLEM and your VB app? Does this also show the dicrepancy between run times? (Showplan only works from SQLQA, Profile will do all apps)

3) ok, I lied. How many rows on average are begin returned from the SELECT?

What you might be seeing is the fact that ADO has a lot of overhead in managing the data as it gets returned. thereby creating your 'performance issue'. This should tell us if the lag is in SQL Server or VB/ADO.

0
 

Author Comment

by:dbdoshi
ID: 6471566
I am closing this one out and awarding points even though the answer was not exactly hit upon. Basically, dont use views. Seems like when a large result is returned from 'select from view' (Id say more than 200 rows) the drop view statement which immediately follows the execute seems to break..as in hanging for 10 seconds to 2 days before the view is dropped. Sql server isnt reporting any blocks but it sure acts like the dropview is colliding with the select.
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6471681
Thank you for awarding me the points with an A grade:)

Sorry you haven't quite found a solution:(

Just one question.
Can you use stored procedures?


0
 

Author Comment

by:dbdoshi
ID: 6471701
THe stored procedure will still hang on the drop view statement. I just had to totally move away from views to get ti to work consistently.
0
 
LVL 1

Expert Comment

by:dekeldate
ID: 6475535
glad you found something that works.:)

0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now