At 08:19 AM 28/05/2006, you wrote:

>hello
>
>i'm currently reworking a terminology database application for my
>college degree which - if i get it all well done - is going to be used
>*in real life* by my professor and I'm currently having a speed issue:
>he is probably going to complain about query execution time given the
>fact that
>1st) i did some serious database restructuring (the original database
>had only one table)
>2nd) i am using firebird as a potentially remote host as opposed to a
>local bde+paradox database
>
>i generated a random database with some 300000 entries in the main
>table and had to find that normal queries (like select * from table
>where value = 'abc') take a couple seconds and that the more complex
>ones can take like five minutes to execute as opposed to the current
>version of the application where everything happens within a fraction
>of a second..now i was wondering how much faster a serious database
>server machine would do; if i told him the problem could be fixed with
>an investment of a couple grand in a state-of-the-art backend server
>with fast hd's and stuff that sure wouldn't be a problem as it would
>have a business purpose but i sure wouldn't want to recommend him to
>waste his money on a high speed machine that instead of a
>5-minutes-query-execution time has one of 3,5 minutes..how many times
>faster can a serious server be in comparation to my off-the-shelf 500
>quid desktop pc?

I'm having a lot of trouble here understanding what state your
current database design is in. Are you saying that your "more
complex" queries are not sub-second?

For huge databases, of course there are benefits from better
resources - faster disk access and allowing the server to have all
the RAM it can use. "A 500-quid desktop pc" tells us nothing about
the hardware you're using.

But 300K records is tiddly. With bad indexing and inefficient
queries, sure, even querying 300K could be a dog. If your prof knows
his stuff, he will see straight through the "blame the hardware"
argument. I'm not a prof, but I can see through it already. Don't
waste your cerebral CPU cycles working on the assumption that
throwing hardware at a faulty infrastructure will solve your
performance problems. Take a more academic approach and focus on
analysing why your stuff is so slow and how you can fix it.

Into your impossibly vague problem definition I offer the following comments:

1. If you handed a paradox-style hierarchical structure over to
Firebird and indexed the database on the basis of what you had to do
in paradox to make it relational, you've probably still got some
*more serious* restructuring to address before you start lobbying for
more hardware.

2. If there's money available, lobby to get enough quids to buy a
second-hand, pretty ordinary pc or laptop off eBay, a couple of NICs
and a crossover cable so you can actually test how things are likely
to work in a client/server environment.

3. You don't say what your application environment is, but BDE +
paradox suggests you have Delphi in the picture somewhere. Are you
still using the BDE now that you have switched the database to
Firebird? Tell us more about it.

./heLen