Database question
-
First I want to say that I am 100 pages into Beginning Visual C++, so I have basically no exp. All of my programming has been VB, C# and if you are over on the PHP side you'll notice I'm starting that and not very good yet :laugh: Anyways, this is the problem we have where I work. We are moving from Visual Studio 6 to 2010 (slight jump) and are running into some problems with speed. For example I can write a app in VB to read from a database and put the data into a DataSet in about .4 seconds. To do a similar operation in C++ is taking 5 seconds (not .5 but 5). VB Code is as follows to fill the dataset
sqlString = "SELECT * FROM attyesqs"
da = New SqlDataAdapter(sqlString, objConnection.cnn)
da.Fill(ds, "Debts")If you know VB we are talking very simple and it works well. However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower. He is using a DSN and ODBC not being familiar enough with C++ I don't know what part to post here, but the connection he is using is as follows:
CDatabase cd;
CRecordset sourcers(&cd);
CString DSNString = _T("DSN=dsnName;UID=user;PWD=password;"), SQLString = _T("SELECT AttyLastName, AttyFirstName FROM ATTYESQS");I'm actually grabbing more columns in my query but that shouldn't matter much, I think it's only like 2 or 3 more. With VB and C# things have changed greatly in the last 10 years, is there now a better way to do this in C++/MFC? I can try to answer any questions you may have, and as always thank you in advance!
-
First I want to say that I am 100 pages into Beginning Visual C++, so I have basically no exp. All of my programming has been VB, C# and if you are over on the PHP side you'll notice I'm starting that and not very good yet :laugh: Anyways, this is the problem we have where I work. We are moving from Visual Studio 6 to 2010 (slight jump) and are running into some problems with speed. For example I can write a app in VB to read from a database and put the data into a DataSet in about .4 seconds. To do a similar operation in C++ is taking 5 seconds (not .5 but 5). VB Code is as follows to fill the dataset
sqlString = "SELECT * FROM attyesqs"
da = New SqlDataAdapter(sqlString, objConnection.cnn)
da.Fill(ds, "Debts")If you know VB we are talking very simple and it works well. However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower. He is using a DSN and ODBC not being familiar enough with C++ I don't know what part to post here, but the connection he is using is as follows:
CDatabase cd;
CRecordset sourcers(&cd);
CString DSNString = _T("DSN=dsnName;UID=user;PWD=password;"), SQLString = _T("SELECT AttyLastName, AttyFirstName FROM ATTYESQS");I'm actually grabbing more columns in my query but that shouldn't matter much, I think it's only like 2 or 3 more. With VB and C# things have changed greatly in the last 10 years, is there now a better way to do this in C++/MFC? I can try to answer any questions you may have, and as always thank you in advance!
-
ADO .NET is fairly easy, although Visual Studio stops supporting Intellisense for C++/CLI with Visual Studio 2010. LINQ is another option, but it's even more overhead since it's based on ADO .NET.
If I remember right the SQLConnection I'm using in VB is basically ADO? I've been doing some reading and from what I've been finding people are saying that DSNs are "now slower than dirt", is this the case or just a case of them not being used properly? If they dropped intellisense support for ADO am I to believe that this is something that is going to be gone in the near future and replaced with something else? Again new to this side of things so excuse my lack of knowledge. Thanks again.
-
ADO .NET is fairly easy, although Visual Studio stops supporting Intellisense for C++/CLI with Visual Studio 2010. LINQ is another option, but it's even more overhead since it's based on ADO .NET.
I've got a newbie question, you can set up an ADO connection like you would in VB/C# you don't have to use a DSN right?
-
If I remember right the SQLConnection I'm using in VB is basically ADO? I've been doing some reading and from what I've been finding people are saying that DSNs are "now slower than dirt", is this the case or just a case of them not being used properly? If they dropped intellisense support for ADO am I to believe that this is something that is going to be gone in the near future and replaced with something else? Again new to this side of things so excuse my lack of knowledge. Thanks again.
-
First I want to say that I am 100 pages into Beginning Visual C++, so I have basically no exp. All of my programming has been VB, C# and if you are over on the PHP side you'll notice I'm starting that and not very good yet :laugh: Anyways, this is the problem we have where I work. We are moving from Visual Studio 6 to 2010 (slight jump) and are running into some problems with speed. For example I can write a app in VB to read from a database and put the data into a DataSet in about .4 seconds. To do a similar operation in C++ is taking 5 seconds (not .5 but 5). VB Code is as follows to fill the dataset
sqlString = "SELECT * FROM attyesqs"
da = New SqlDataAdapter(sqlString, objConnection.cnn)
da.Fill(ds, "Debts")If you know VB we are talking very simple and it works well. However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower. He is using a DSN and ODBC not being familiar enough with C++ I don't know what part to post here, but the connection he is using is as follows:
CDatabase cd;
CRecordset sourcers(&cd);
CString DSNString = _T("DSN=dsnName;UID=user;PWD=password;"), SQLString = _T("SELECT AttyLastName, AttyFirstName FROM ATTYESQS");I'm actually grabbing more columns in my query but that shouldn't matter much, I think it's only like 2 or 3 more. With VB and C# things have changed greatly in the last 10 years, is there now a better way to do this in C++/MFC? I can try to answer any questions you may have, and as always thank you in advance!
OK - every time you connect to a database - doesn't matter how it occurs, it takes time to establish the connection and verify the user's credentials. That's problem #1. So use pooled connections or keep the connection open. The second problem is that every time you create a new SQL statement for execution, the DBMS has to check the syntax and evaluate an execution plan for that statement. Therefore if you have a 'WHERE' clause in the statement, use a prepared statement (SQLPrepare?? -haven't used ODBC for 10 years). Of course you can get over the second problem by using SQL procedure or function calls.
-
First I want to say that I am 100 pages into Beginning Visual C++, so I have basically no exp. All of my programming has been VB, C# and if you are over on the PHP side you'll notice I'm starting that and not very good yet :laugh: Anyways, this is the problem we have where I work. We are moving from Visual Studio 6 to 2010 (slight jump) and are running into some problems with speed. For example I can write a app in VB to read from a database and put the data into a DataSet in about .4 seconds. To do a similar operation in C++ is taking 5 seconds (not .5 but 5). VB Code is as follows to fill the dataset
sqlString = "SELECT * FROM attyesqs"
da = New SqlDataAdapter(sqlString, objConnection.cnn)
da.Fill(ds, "Debts")If you know VB we are talking very simple and it works well. However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower. He is using a DSN and ODBC not being familiar enough with C++ I don't know what part to post here, but the connection he is using is as follows:
CDatabase cd;
CRecordset sourcers(&cd);
CString DSNString = _T("DSN=dsnName;UID=user;PWD=password;"), SQLString = _T("SELECT AttyLastName, AttyFirstName FROM ATTYESQS");I'm actually grabbing more columns in my query but that shouldn't matter much, I think it's only like 2 or 3 more. With VB and C# things have changed greatly in the last 10 years, is there now a better way to do this in C++/MFC? I can try to answer any questions you may have, and as always thank you in advance!
MacRaider4 wrote:
However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower.
I doubt that your analysis is correct. You are presuming that it is the code itself that is causing the performance problem. I would suspect otherwise. But then there are many details missing from your problem description. For example is the total run time 1 hour, so it is 4.5 seconds longer than 1 hour? Or it is .1 seconds in VB and thus 4.6 seconds in C++? Are you testing a single attempt in both applications? Are you testing on the same box and using the same database? At what point to you time the speed? How do you time the speed? Are you using ODBC in VB? Did you validate the time by running the tests for both applications multiple times?
-
MacRaider4 wrote:
However our other programmer who is trying to get similar performance out of a MFC app is running into the problem of it being 4.5 seconds slower.
I doubt that your analysis is correct. You are presuming that it is the code itself that is causing the performance problem. I would suspect otherwise. But then there are many details missing from your problem description. For example is the total run time 1 hour, so it is 4.5 seconds longer than 1 hour? Or it is .1 seconds in VB and thus 4.6 seconds in C++? Are you testing a single attempt in both applications? Are you testing on the same box and using the same database? At what point to you time the speed? How do you time the speed? Are you using ODBC in VB? Did you validate the time by running the tests for both applications multiple times?
I have changed my VB app to use the same DSN that the C++ app is using and it is still faster. The only thing that is being done is grabbing 4,500 or so records and populating a datagrid. Our C++ programmer is convinced it's something with the network, but if that was the case both should run equally as bad. Both are being tested on the same servers and have been run well over 50 times and the VB one wins out every time. Now being a VB programmer I know this really should not be the case as VB has more overhead than a MFC application thus should be running slower. As far as timing goes, we have both apps getting time stamps before and after the process starts. I don't have the code infront of me, but it is basically startTime -> Open the connection and query the database -> return the results to a recordset -> close the connection, endTime. Now I can't remember for sure if we open the connection then do the startTime or before, but I do know both are the same.
-
I have changed my VB app to use the same DSN that the C++ app is using and it is still faster. The only thing that is being done is grabbing 4,500 or so records and populating a datagrid. Our C++ programmer is convinced it's something with the network, but if that was the case both should run equally as bad. Both are being tested on the same servers and have been run well over 50 times and the VB one wins out every time. Now being a VB programmer I know this really should not be the case as VB has more overhead than a MFC application thus should be running slower. As far as timing goes, we have both apps getting time stamps before and after the process starts. I don't have the code infront of me, but it is basically startTime -> Open the connection and query the database -> return the results to a recordset -> close the connection, endTime. Now I can't remember for sure if we open the connection then do the startTime or before, but I do know both are the same.
MacRaider4 wrote:
I have changed my VB app to use the same DSN that the C++ app is using and it is still faster
By VB you mean in .Net? .Net uses connection pooling. You can modify the url to disable connection pooling. You didn't mention what 4.5 seconds was relative to. However there is no way that establishing a connection on a lan should take 4.5 seconds unless something is wrong with the infrastructure.
MacRaider4 wrote:
As far as timing goes, we have both apps getting time stamps before and after the process starts. I don't have the code infront of me, but it is basically startTime -> Open the connection and query the database -> return the results to a recordset -> close the connection, endTime.
If it is in fact C++ itself then you should be able to create a test app with a simple table structure and exactly reproduce the results. If you cannot do that then it would demonstrate that there is some other problem. If you can do it then posting the exact code here, of the test app, for both languages would be helpful.