Solved

ADO execute is slow as hell but sql statement executes instantly in enterprise manager...

Posted on 2001-08-23
16
465 Views
Last Modified: 2013-11-23
I have a program which uses an ADO connection object to execute sql commands against  a MSSQL7.0 server. The speed with which the results come back varies from 1 to 40 seconds. I would say it is a congestion problem but I can take the exact same sql statement and execute it in the sql7 enterprise manager on my remote computer (the same one that is running the very slow ADO execute) and the results ALWAYS come back in less than 2 seconds, even during the periods when the ADO execute is taking upwards of 40 seconds. Ive checked the sql server and none of the ADO clients are reporting their process blocked and there are no locks on the tables the sql statement is running against. IDeas? I am close to desperate.

BTW: I put stops around the execute to make sure all the delay is coming from the ADO execute.
0
Comment
Question by:mccainz2
  • 5
  • 4
  • 2
  • +4
16 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 6417655
Could you post some code?

How do you show your results?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6417680
Need code.. particularily Connection / Command / Recordset strings.
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6417858
Just off the top of my head I have seen this type of thing before and it usually stems form either SQL trace or ODBC log being kept on the the client to log activity.
I suspect you may have some sort of tracing program running on the client machine for an ODBC connection.

Vin.

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 8

Expert Comment

by:DennisBorg
ID: 6417950
:ping:
0
 
LVL 5

Author Comment

by:mccainz2
ID: 6417976
Information that is helpful....it it a TCPIP connection rather than a named pipe.  The rub is why should ADO execute the exact same sql statement 10-20 times slower than when the statement is run in the enterprise manager. The delay is on the execute. As Ive stated, I have isolated the ADO execute and every bit of the delay comes from that one line of code. I suppose this question requires a good background in the underpinnings of ADO and MSsql server.

The sql statement is a select with an inner join....no update or insert and NO LOCKS.

 
0
 
LVL 5

Author Comment

by:mccainz2
ID: 6417989
Dennis you STUD! answer this one! Youve bailed me out numerous times already! DOnt make me start whining!

BTW: Vin, still looking into your suggestion.
0
 
LVL 4

Accepted Solution

by:
VincentLawlor earned 90 total points
ID: 6418030
What type of connection are you using

DSN
DSNless

Do you have SQL Profiler running on the server.

If you are using ODBC on the client have a look for SQL.LOG on the client machine this could be the cause of your problems.
Have a look in the ODBC Data Sources applet under the Tracing tab. If you have tracing swithched on turn it off.

Vin.
0
 
LVL 5

Author Comment

by:mccainz2
ID: 6418104
connection is pure TCPIP. 99% of the computers that use the app have no MSsql client installed, Just the sqloledb.dll provider.

Server='IP NUMBR HERE';Network=dbmssocn;
0
 
LVL 5

Author Comment

by:mccainz2
ID: 6418120
BTW: sorry VIn, not sure about sql profiler running on the server, asking the dba now.....
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6418135
What about Tracing in ODBC ?

Vin.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6418165
As everybody else has pointed out the code you use to execute the statement can make all the difference.

If you cannot show the code check the following:
CursorType, CursorLocation, LockType

Does it return a recordset or not?

All of these conditions can cause these performance issues.

Anthony
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6418199
1.     I would recommend to run Profiler
2.     You can convert your statement to a stored procedure.


SQL Server Profiler is a graphical tool that allows you to monitor and collect server events such as:
?     Server connects and disconnects
?     Transact-SQL batches
?     The execution of statements within stored procedures
?     Deadlocks
?     Errors written to the SQL Server error log
This information can be displayed in SQL Server Profiler, stored in trace files, or saved in a Microsoft? SQL Server? table. SQL Server Profiler can read trace files created in SQL Server Profiler or SQL Trace, as well as Transact-SQL scripts.
Examples of event data captured by SQL Server Profiler include:
?     The types (classes) of events being traced (e.g. SQLBatchCompleted).
?     The SQL Server name of the users performing activity in SQL Server.
?     The text of Transact-SQL statements and stored procedures being executed.
?     The duration of Transact-SQL and other types of events.
?     The severity of errors.
0
 
LVL 5

Author Comment

by:mccainz2
ID: 6418361
Congrats VIncent, the points are yours!
Kudo's for grasping the nature of the problem, and my buddy dbdoshi (anuther thread youve done well on) says thanks.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6418469
mccainz2:
YOU have a responsibility to bring YOUR questions to an EXCELLENT resolution.. What question or inadequacy did VincentLawlor miss that this question only recieved a "B"? We Experts like to learn too.. do your part, please give us an explanation as to what was substandard.

0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6418635
mccainz2:

>Dennis you STUD! answer this one! Youve bailed me out
>numerous times already! DOnt make me start whining!

Sorry about that!  :-)   If I could have offered assistance, I would have.

But this one I simply had to sit by the side, and watch and learn.

-Dennis Borg
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6419448
Thanks for the points.

But as the lads have said why a B sniff sniff :-(

Vin.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Passing a Text Box name to a Sub 6 99
how to resize animated Gif image in delphi ? 1 35
Help me. 3 60
How to Get Images From Server using App Tethering 11 40
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

820 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