Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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;
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(vtInd
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.
I get some value from database with ADO:
HRESULT hr;
::CoInitialize(NULL);
_ConnectionPtr pConn("ADODB.Connection");
_RecordsetPtr pRs;
hr = pConn->Open("dsn=MySource;
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(vtInd
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.
Asked:
10-
8
1 Solution
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
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
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
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
>>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
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
I've created index of myID. In gerenal, MSSQL create index for primary key.
Then, in this status, which way is the fastest?
Then, in this status, which way is the fastest?
I've created index of myID. In gerenal, MSSQL create index for primary key.
Then, in this status, which way is the fastest?
Xu
Then, in this status, which way is the fastest?
Xu
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
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
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
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
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
-- Dan
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
Xu
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
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
>>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
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
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
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
>>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
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
>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
me too.
OK, I'll accept your answer, which grade do you want?
Xu
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Be seen. Boost your question’s priority for more expert views and faster solutions
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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...