?
Solved

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

Posted on 2001-08-23
16
Medium Priority
?
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 270 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 2 hours left to enroll

764 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