asked on

ADO question

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.

cypherljk

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

DanRollins

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

xu2000

ASKER

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

DanRollins

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

xu2000

ASKER

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

xu2000

ASKER

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

Xu

DanRollins

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

xu2000

ASKER

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

DanRollins

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

xu2000

ASKER

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

DanRollins

how long does it take to make the connection?

How much time to execute the query?

-- Dan

xu2000

ASKER

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

DanRollins

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

DanRollins

hi xu2000,

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

-- Dan

ASKER CERTIFIED SOLUTION

DanRollins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xu2000

ASKER

Hi,
Even all use name pipe, there exist performance difference.

I've read the question from AAB: https://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

DanRollins

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

xu2000

ASKER

>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

DanRollins

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