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!
Course Of The Month
9 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ADO question
Posted on 2001-08-02
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.
[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
10-
8
19 Comments
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...
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...
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
Featured Post
At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction:
Displaying information on the statusbar.
Continuing from the third article about sudoku.
Open the project in visual studio.
Status bar – let’s display the timestamp there. We need to get the timestamp from the document s…
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…
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.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 7 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.