Database Planning

berbanky
berbanky used Ask the Experts™
on
I'm new to C++ but have a lot of experience with databases. I often use ColdFusion and have learned how much faster SQL Server can be than Access or text databases. My program is now in SQL Server and ColdFusion, but I need it to be faster, so I'm rewriting it in C++.

My question is this: in terms of speed and performance, is it going to make a big difference to use SQL Server rather than Access or text via the C++ application? Or is the speed similar no matter what the data source? The database has hundreds of thousands of fields in each of several tables (5-6).

I've seen shareware/freeware C++ apps given out with huge text databases, and they run really fast. Is the difference negligible, or am I going to notice a big difference?

Is there any database format that works especially well with C++?

Is it going to help to have indexes (as it does when accessing data with a slower language like ColdFusion)?

Any other advice or considerations for me?

Thanks, Ben
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
1) In terms of speed and performance, is it going to make a big difference to use SQL Server rather than Access or text via the C++ application?
The difference between SQL Server, Access and text file is well-known for every database programmer. If you have experience in DB programming, you don't neeed explanation according to this point. It is the same difference if you write client application in C++ or other language.
C++ may have some advantage only if you work with text files because you have more control over computer resources.

2) The database has hundreds of thousands of fields in each of several tables (5-6).
For this amount of data you can use both Access and SQL Server. I think it's too much for text files.

3) Is there any database format that works especially well with C++?
I don't know such format. As C++ programmer I have no advantages writing SQL Server or Acceess client code. I always prefer to write this in VB, this gives me oppportunity to concentrate on DB issues and not on ugly implementation details.

4) Is it going to help to have indexes?
Yes, it always helps. Writing faster code in C++ can save, for example, 5% of the time. All other time is wasted on executing of database queries.

5) Any other advice or considerations.
Optimization of database structure and queries usually gives much more than changing language. C++ is not a best choice for database programming. If your program runs under Windows, Visual Basic may be a good choice too.

1) In terms of speed and performance, is it going to make a big difference to use SQL Server rather than Access or text via the C++ application?
this is a misconception. a well implemented solution of using text can be as fast as using SQL Server or even better. the attraction of database servers are that they reduce the code writing time and promise a minimum performance while writing ur own C++ using ur own text depends very much on how u write it. u write it good, it is good, u write it bad, it is bad :(

2) The database has hundreds of thousands of fields in each of several tables (5-6).
as i say, it is not too much for user-written database, remember the time before database servers come into being? people still manage the database themselves.

Commented:
Correction:

The database has hundreds of thousands of fields in each of several tables (5-6).

I thought "hundreds or thousands". Hundreds of thousands - I think it's too much for Access.

Author

Commented:
Thanks a bunch, AlexFM & CoolBreeze.

A couple follow-up things: I dont know VisualBasic, and I'm working on a Mac, so that's not an option for me. I'm writing this to run on Windows AND Mac (eventually), and since there is no SQL Server for Mac, text sounds like a good way to go (though speed on Windows is my priority). This also seems ideal for expense and portability reasons.

Is it difficult to code access to a text database so that it's as fast as SQL Server? Do you know of any good resources for this?

Is it possible to index a text database?
in that case, u might consider using embedded databases which gives the power of SQL server and such, but faster without the inter process communication. consider Berkeley DB:

http://www.sleepycat.com/

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial