Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2001-08-23
16
Medium Priority
?
483 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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.
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…
Suggested Courses

604 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