Solved

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

Posted on 2001-08-23
16
464 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…

813 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

11 Experts available now in Live!

Get 1:1 Help Now