Solved

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

Posted on 2001-08-23
16
462 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
Comment Utility
Could you post some code?

How do you show your results?
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Need code.. particularily Connection / Command / Recordset strings.
0
 
LVL 4

Expert Comment

by:VincentLawlor
Comment Utility
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
 
LVL 8

Expert Comment

by:DennisBorg
Comment Utility
:ping:
0
 
LVL 5

Author Comment

by:mccainz2
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

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

Expert Comment

by:VincentLawlor
Comment Utility
What about Tracing in ODBC ?

Vin.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the points.

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

Vin.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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…
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…

743 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