C / C++ / MFC

21-Jul-03 1:04

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

Mike Dimmick21-Jul-03 2:04

Mike Dimmick

21-Jul-03 2:04

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.

Manfred Staiger21-Jul-03 2:07

Manfred Staiger

21-Jul-03 2:07

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

John M. Drescher21-Jul-03 3:50

21-Jul-03 3:50

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.

Smile | :)

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

Miguel Lopes21-Jul-03 2:29

Miguel Lopes

21-Jul-03 2:29

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.

Lars [Large] Werner21-Jul-03 3:32

21-Jul-03 3:32

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! Smile | :)

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?! Smile | :)

===================
Lars [Large] Werner
lars@werner.no
===================

John M. Drescher21-Jul-03 3:56

21-Jul-03 3:56

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

Lars [Large] Werner21-Jul-03 4:13

21-Jul-03 4:13

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! Smile | :)

I've experienced running SQL commands directly through ADO in ASP... But there seems that many other solutions is found for MFC and VC++! Smile | :)

===================
Lars [Large] Werner
lars@werner.no
===================

John M. Drescher21-Jul-03 4:28

21-Jul-03 4:28

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

Lars [Large] Werner21-Jul-03 4:35

21-Jul-03 4:35

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! Smile | :)

Does the grid do the same?

===================
Lars [Large] Werner
lars@werner.no
===================

John M. Drescher21-Jul-03 4:46

21-Jul-03 4:46

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

Ian Darling21-Jul-03 5:17

Ian Darling

21-Jul-03 5:17

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

Miguel Lopes21-Jul-03 5:36

Miguel Lopes

21-Jul-03 5:36

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.

How to shut off Floppy's LED rapidly

Ian Darling21-Jul-03 5:56

Ian Darling

21-Jul-03 5:56

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 Big Grin | :-D

--
Ian Darling

FlyingDancer20-Jul-03 22:00

FlyingDancer

20-Jul-03 22:00

When Floppy Device's LED is on
How can I make it off immediately?

Appreciate any help!

Toni7820-Jul-03 22:28

Toni78

20-Jul-03 22:28

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

FlyingDancer20-Jul-03 22:51

FlyingDancer

20-Jul-03 22:51

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?
Smile | :)

Toni7820-Jul-03 23:01

Toni78

20-Jul-03 23:01

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

FlyingDancer20-Jul-03 23:09

FlyingDancer

20-Jul-03 23:09

Although you can't help me I still appreciate your replies very much!
Not everything we can do
I see

Thank you