Solved

ADO question

Posted on 2001-08-02
19
1,357 Views
Last Modified: 2013-11-20
Hi,
I get some value from database with ADO:

      HRESULT hr;
     ::CoInitialize(NULL);
     _ConnectionPtr pConn("ADODB.Connection");
     _RecordsetPtr pRs;
     hr = pConn->Open("dsn=MySource;uid=sa;pwd=", "", "", adAsyncConnect);
     if(FAILED(hr))
     {
          AfxMessageBox("Error");
          return;
     }
     
     try
     {
          _variant_t vRowsAffected;
         
          pRs = pConn->Execute("select min(myID) from MyTable",
               &vRowsAffected, adCmdText);
         
          _variant_t vtIndex;
          _variant_t vtField;
          vtIndex.vt = VT_I2;
          vtIndex.iVal = 0;
         
          vtField = pRs->Fields->GetItem(vtIndex)->Value;
          if(vtField.vt != VT_NULL);
               TRACE("VALUE = %d\n", vtField.iVal);          
     }
     catch(_com_error &e)
     {
          dump_com_error(e);
     }
     
     ::CoUninitialize();

when I open connection with adAsyncConnect, pConn->Execute will throw an error. With adConnectUnspecified will work OK, but speed is very slow, more slow than ODBC.
My question is why I can?t use Execute() with asynchronously opening connection?
Or Which is the max speed method to get value from database?

Thank you.

0
Comment
Question by:xu2000
  • 10
  • 8
19 Comments
 
LVL 3

Expert Comment

by:cypherljk
ID: 6344885
I has been my experience that you will have to 'experiment' with all hte ad connection specifiers. Some combinations produce the results you expect while others will cause an error or not perform as expected. The ADO docs don't clarify which combinations should be with what other paramters so you'll just have to play around with it.

P.S. the MSDN ADO pages is here: http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/dasdkadooverview.asp?frame=true

My 2 cents...
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6347379
adAsyncConnect is not supported by most database drivers.  Anyway, you can't get any work done until the connection is established...

In the Control Panel, check the settings for MySource.  If it is MS SQL, set the client to use only one transport method (network library).  Otherwise, it will try out a bunch of options before settling on the one it thinks is fastest.  If the DataSource (db ebgine) is on the local computer, use "Named Pipes"

In any case, describe what your current setting are here.

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6357872
It's MSSQL7 and MySource use "Named Pipes". MSSQL support adAsyncConnect, but it seems can't use Execute when async connect.
I have more insteresting in How to get value (do like "select min(myID) from MyTable") from database in max speed. ODBC's speed is 3 times than ADO's(sysnc connect) in my test. I think I haven't fount the best way.

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6358495
>>I have more insteresting in How to get value (do like "select min(myID) from MyTable") from database
in max speed.

In general, the way to speed up that SQL command is to make myID and index.  Then the DbEngine doesn't need to look at each record... just the index.

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6367070
I've created index of myID. In gerenal, MSSQL create index for primary key.
Then, in this status, which way is the fastest?
0
 
LVL 2

Author Comment

by:xu2000
ID: 6367072
I've created index of myID. In gerenal, MSSQL create index for primary key.
Then, in this status, which way is the fastest?

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6367105
hi xu2000,
Now I remember you.  In a previous question, I suggested that you use multiple connections -- but it was not what you wanted to hear, so you deleted the question.

Well, in this question, I will suggest that you not use adAsyncConnect.  There is no purpose to using it, since you cannot obtain a recordset or execute any SQL statments) ntil the connection is fully established.

But I'm quite certain that you will not take my advice.  Instead, you will repeat that you are having problems when you use adAsyncConnect and I will repeat the suggestion not to use it.  Then you will repeat that you are having problems when you use adAsyncConnect.  I will carefully rephrase the suggestion that you not use it.  Then you will delete this question.

So let's just save some time.

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6368272
No, you are wrong, I've said I'm instersting in speed not adAsyncConnect, and I created index after you suggest. In this status, I found ADO slower than ODBC by 3 times like before, then I want to know which way is the fastest. I don't know why that displease you.

In previous question I focus on multithreads, of course multiconnections can reslove block, but I want to figure out one problem I encounter, am I wrong?

My poor English can't express more.
I appreciate your answers and I hope you forget the bad memory.

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6375327
How are you timing this.  If you are including the time to open the connection, then dont.  Insted, just time how long it takes to get the desired value.  You can keep the connection around and reuse it.

-- Dan
0
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

 
LVL 2

Author Comment

by:xu2000
ID: 6375757
Yes, of course I time get value. Not include the time to connect. I use Rational Quantify to time. I've heard that ODBC is the slowest way to get value, but in test, it is faster than ADO, so I have the question.

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6375896
how long does it take to make the connection?

How much time to execute the query?

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6387623
Function             Calls          propagated time
pConn->Open           1                      46,174
pConn->Execute        1000                6,640,862

In ODBC, open(..., "Select ...", ...)
                      1000                1,718,907

The part of getting value is called 1000 times(not include connection time)

the two caller function ADOTest() and ODBCTest()
ODBCTest              1                   2,230,413
ADOTest               1                   7,134,423

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6411010
>>I've heard that ODBC is the slowest way to get value, but in test, it is faster than ADO, so I have the question.

So, it would appear that who ever told you that ODBC was slower was incorrect.

There is one other thing to try.  When you set up the ODBC connection (in the Control Panel), you get to choose the network library data transport mechanism.  In my experience, "Named Pipes" is considerably faster that any other option when the MsSQL server is local.

So, the question is, how does one tell the ADO connection to use Named Pipes so that it gets the same speed advantage?  Take a look at:

Q238949 - HOWTO: Set the SQL Server Network Library in an ADO Connection String

http://support.microsoft.com/support/kb/articles/Q238/9/49.ASP

Then delete this question to ensure that you don't spend any valuable points.

-- Dan
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6420443
hi xu2000,

Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 200 total points
ID: 6443864
hi xu2000,

Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6449645
Hi,
Even all use name pipe, there exist performance difference.

I've read the question from AAB: http://www.experts-exchange.com/jsp/qShow.jsp?ta=mfc&qid=20174059, but still no one can answer it, I hope can get more clear answer.

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6449724
>>I've heard that ODBC is the slowest way to get value, but in test, it is faster than ADO, so I have the question.

There is no obvious reason to think that ADO would be faster -- it often acts as a wrapper around SQLXxxx calls which use ODBC.  So one might expect ADO to take slightly longer than direct ODBC access.

I would *not* expect to see the large differences that you have recorded.

I suggest that you check all of the options and defaults that are in use when you open the database abd when you pull the recordset.

-- Dan
0
 
LVL 2

Author Comment

by:xu2000
ID: 6449852
>I would *not* expect to see the large differences that you have recorded.
me too.

OK, I'll accept your answer, which grade do you want?

Xu
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6449927
I guess that I did not really help here.  Feel free to delete this question.  If you want to grade it, please use A or B, since a C looks bad on my permanent record.

Thanks

-- Dan
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

22 Experts available now in Live!

Get 1:1 Help Now