|
Hi there...
I'm writing a app in VC++ that needs a database, I'm currently using this ODBC recordsetclass (Using MFC)
I thought that working with recordsets will make the fastest way to eg insert 100.000 entries into the database as quick as possible...
I've tested with a simple MS Access database and it took over 6 minutes to insert 100.000 dummy entries (1 text field, with 255 chars)
Since it takes some time to test'em all, I would ask if anybody know which protocol is the fastes? ODBC/ADO/OLE ????
===================
Lars [Large] Werner
lars@werner.no
===================
|
|
|
|
|
ODBC is generally a bit slower than OLE DB, because there are more layers involved - you talk to the ODBC manager, which talks to a driver, whereas with OLE DB, your program talks directly to the provider.
However, your experience may be different, depending on the driver or provider. One supplier's OLE DB provider might be less well optimised than their ODBC driver.
Using the native OLE DB provider will always be quicker than using MSDASQL, which is an OLE DB provider that wraps the ODBC manager. This allows connections to work - it says nothing about performance.
In this particular case, I suspect that your problem is actually per-statement overhead. If you can perform a batch update or insert, that would probably be better. I'm not sure how to go about this with ODBC - it's structured around executing SQL statements, so you probably need to have it execute a big batch of INSERT statements all chained together as a single logical statement.
For OLE DB, you ask for the IRowsetUpdate interface and use the InsertRow method to insert a new row. Once all rows are inserted, you call the Update method to update a complete batch of rows.
From C++, I would personally avoid ADO. You spend a lot of time working around the VBisms of the interface rather than being productive, IMO.
|
|
|
|
|
Replace CRecordset by CDaoRecordset and CDatabase with CDaoDatabase when working on MS ACCESS (in other words: use DAO for MS ACCESS). It very much faster.
Keep away from ADO. MFC does not yet contain wrapper classes and to my experience it contains some bugs which are hard to overcome with VC.
MS
|
|
|
|
|
Manfred Staiger wrote:
Keep away from ADO. MFC does not yet contain wrapper classes and to my experience it contains some bugs which are hard to overcome with VC.
 I switched from DAO to ADO to get around the multitasking bugs and after using ADO for over 2 years I have yet to find a bug...
John
|
|
|
|
|
well, if you need performance, just do ONE thing... DONT USE ACCESS!!!!!!!! Doh!
Of course the protocol matters, but the most important thing is the database. MySQL is probably the fastest, but its also very limited in terms of SQL advanced features. SQL server is vgood and only gets slow when you use it through MSDE.
Also the key feature to a fast DB acess is a good DB design and avoiding complex queries. If you need complex results, make simple queries and then work it out in the client side. C++ is a very powerfull and fast language, so y(?) overloading the DB server with complex queries, when you can produce faster results using a bit of code when handling results?
In terms of protocol, there are a lot more protocols than those u mentioned. Most of them are only avaiable to a specific DB (Oracle, PostGreSQL, MySQL) but their performance is great cause they are optimized for that DB. ODBC is just a wrapper.
|
|
|
|
|
The idea is to use the lame MS Access at the start, and then later upgrade the system to SQL... I've found the ODBC driver pretty easy to change between them!
At work we use ASP and the recordsets there are pretty quick against the SQL, is that the same as ADO, or is that a direct OLE DB functions?
I also making a hunt for a good grid control... The MS Grid pretty much sux, as I see it... Does anybody have any other controlls that are good for the purpose to show much data?!
What advice would you give me , for selecting the the fastes and best protocol that is easy to use for MFC?! I have tested OLE DB Express or something like that, and I've tested the ADO, and ODBC... The ADO seems pretty quick on the Access for me, is it also quick in the SQL?!
===================
Lars [Large] Werner
lars@werner.no
===================
|
|
|
|
|
Then you absolutely want to use ADO. It will make the transition from access to MSDE and SQL Server very easy. Do you know about MSDE?? It is a free version of SQL server that is limited to 10 clients and 2GB of database space, however I have yet to hit this limitation in any of my projects... As for a Grid. The best one is Chris Maunders Grid Control: http://www.codeproject.com/library/gridprojects.asp[^]
John
|
|
|
|
|
Thnx for the link for the Grid, but does that support for editing the recordset realtime? Like the the MS Grid SetRefDataSource(ADORecordset)? Maybe Virtual Mode (like the ListCtrl) is the only choice?!
I've used the MSDE before, now I'm using the MS SQL Standard edition... But since am probably going to sell my program to users that does not need 100.000 records at start. I've been using M$ Access at start...
I do not "want" to use something, but I'm just asking, since I do not know which system is the best! I've experienced running SQL commands directly through ADO in ASP... But there seems that many other solutions is found for MFC and VC++!
===================
Lars [Large] Werner
lars@werner.no
===================
|
|
|
|
|
Lars [Large] Werner wrote:
Thnx for the link for the Grid, but does that support for editing the recordset realtime? Like the the MS Grid SetRefDataSource(ADORecordset)? Maybe Virtual Mode (like the ListCtrl) is the only choice?!
I use it in virtual mode with all my databases...
John
|
|
|
|
|
OK, and filling it with eg with 100.000 entires in Chris grid makes the program work OK? Or is it quite laggy?
I use a CListCtrl derived class and my own data type, and it sort and show data in below a second! Does the grid do the same?
===================
Lars [Large] Werner
lars@werner.no
===================
|
|
|
|
|
Lars [Large] Werner wrote:
OK, and filling it with eg with 100.000 entires in Chris grid makes the program work OK? Or is it quite laggy?
It is very fast in virtual mode because it only fetches the records that need to be drawn to the screen. I still think it is fast in the normal mode however I have not used 100000 records in a table..
Lars [Large] Werner wrote:
I use a CListCtrl derived class and my own data type, and it sort and show data in below a second! Does the grid do the same?
I have never had any performance problems using the grid. I'm not sure of the particulars but I believe there is a callback mechanism that assists you with the sorting of your data. This allows it to handle your own types.
John
|
|
|
|
|
Miguel Lopes wrote:
MySQL is probably the fastest, but its also very limited in terms of SQL advanced features. SQL server is vgood and only gets slow when you use it through MSDE.
I would have to disagree with some of that. I've run SQL Server 2000 and MySQL on my own server (a P2-400), and the performance of MySQL, while acceptable, is nowhere near as fast as SQL Server 2000 - of course, this was a year ago, so YMMV. Plus that version of MySQL didn't support stored procedures, which was a big turn off for me anyway.
MSDE is slower because it's targetted at a small number of users - it's not intended for enterprise databases
Miguel Lopes wrote:
Also the key feature to a fast DB acess is a good DB design and avoiding complex queries. If you need complex results, make simple queries and then work it out in the client side. C++ is a very powerfull and fast language, so y(?) overloading the DB server with complex queries, when you can produce faster results using a bit of code when handling results?
I agree with the first part of the statement - I prefer that complicated results are calculated as you go along, and then can be pulled out of the database later on. However, if I can't do that, I frequently do the calculations in SQL, because it's good at set data manipulation and transformations, instead of in the client, which is easier to get wrong and (IMO) slower. You also reduce the data you are transferring between client and server, which is often the bottleneck in applications.
My 2p, anyway
--
Ian Darling
|
|
|
|
|
MySQL is fast 'cause its simple. Also its fast cause it also runs in Unix, while SQLServer only runs in windows. Another advantage is the price (free). MSDE (free) has the SQLServer engine, but with limitations built ON PURPOSE. 2GB of maximum DB size, up to 5 clients connections simultaniously and very very slow, so that more demanding projects require SQLServer.
If you want a DB to handle millions of clients, go to Oracle. There is a DB for each purpose.
About client-side calculations, take the "setiathome" example. Of course it depends on the nature of the project.
Overall, i just dont like when people only see Access or SQL Server. Micro$oft isnt the only software company in the world.
|
|
|
|
|
The TPC-C benchmarks are quite useful here - if you want Price/Performance statistics, then Microsoft dominate the results completely:
http://www.tpc.org/tpcc/results/tpcc_price_perf_results.asp[^]
If money is no object, then IBM, Oracle and Microsoft are your guys:
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp[^]
Of course, there aren't any MySQL results there (because nobody has yet run MySQL on their servers and submitted the scores), but there are plenty of Unices represented using Oracle and DB2.
One of the largest databases on the planet runs SQL Server and gets a lot of use: http://terraserver-usa.com/[^]
That's not to knock MySQL at all - it's eminently suitable for a lot of database tasks, and has the advantage of being free. I just don't think it's as good as some people would like to suggest.
As for setiathome - that's a whole different level of complexity to what I was thinking 
--
Ian Darling
|
|
|
|
|
When Floppy Device's LED is on
How can I make it off immediately?
Appreciate any help!
|
|
|
|
|
The LED cannot be controlled by a programmer.
// Afterall, I realized that even my comment lines have bugs
When one cannot invent, one must at least improve (in bed).-My latest fortune cookie
|
|
|
|
|
Can I stop reading when the floppy device is reading a file?
By the way, which action can be confirmed
that is acting on the Floppy?
|
|
|
|
|
I truly don't know. I never had to deal with those kinds of questions. Sorry, I can't help you.
// Afterall, I realized that even my comment lines have bugs
When one cannot invent, one must at least improve (in bed).-My latest fortune cookie
|
|
|
|
|
Although you can't help me I still appreciate your replies very much!
Not everything we can do
I see
Thank you
|
|
|
|
|
You're welcome.
// Afterall, I realized that even my comment lines have bugs
When one cannot invent, one must at least improve (in bed).-My latest fortune cookie
|
|
|
|
|
Are you asking if you can terminate a read operation when it is half-finished?
If so, you can use overlapped I/O and use CancelIo() to cancel it before it completes.
Why do you need to do this? It's not very common reading from files; more common reading from serial ports and "slow" devices.
Ryan "Punctuality is only a virtue for those who aren't smart enough to think of good excuses for being late" John Nichol "Point Of Impact"
|
|
|
|
|
The LED dosn't shut off immediately
At last I change my codes in my loop partition like:
while(true)
{
Sleep(3500);
m_hFloppyDisk = CreateFile(TEXT("A:"),
0,
0,
NULL,
OPEN_EXISTING,
FILE_ATTRIBUTE_NORMAL,
NULL);
}
The result is that LED can go out after about every four seconds' being on
which seems like blinking to some extend.
Not very like.
|
|
|
|
|
You can't make the LED stay on longer or shorter. The timeout is set to 2 seconds by default in hardware, and the only way you can change it is to reprogram the floppy drive controller. Windows does not support this, so there's no way you can do it, short of writing a new floppy disk driver.
Ryan "Punctuality is only a virtue for those who aren't smart enough to think of good excuses for being late" John Nichol "Point Of Impact"
|
|
|
|
|
Thank you very much!
Well, I am feeling I have to learn more knowledge
or I can't make a living any longer
Yes, I have known what you have said
Thank you again!
|
|
|
|
|
I've created a new dialog menu box and one drop-down menu item. I want to enter text information into the program which will appear in a new screen when the user clicks the menu item I just created(I hope I said that clearly). My questions:
1. Should I create a Document class to accomplish this?
2. I noticed that VC++ 6.0 gives the option to create different types of documents(Power point, access, Word, etc.). I am a bit confused on this. Is the how to enter text into my new document class, by entering info into a Word document? What is the standard way that programmers enter the text and pictures into their document class?
"The man who reads nothing is better educated than the man who reads nothing but newspapers."- Thomas Jefferson
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
