--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
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?

Hi Martin,

Let me be honest here. I would expect a subsecond response from a
Pentium II with 32MB RAM running win95 for a simple select query where
value = blah (depending on the number of records that returns).

While I am not a mind reader, perhaps there are returning 250,000
records that meet the where criteria but I doubt it. I agree with
Helen that any professor will see through a more hardware will make
this better argument. Especially if fellow students get their
solutions to fly on such modest hardware.

Let me compare Paradox to Firebird in client server though. Paradox
will ship the table across the network to the client then do its
stuff. That works fine with a 100 speed network and a 3MB table, but
it is hardly a sensible approach. Paradox does have some use but not
IMO in a client server scenario (from experience).

Firebird works differently, the database engine will analyse your
request, and only ship across the network the records that have been
requested.

Apart from the amount of resources available to it, there is not a
huge difference conceptually between a 500 quid desktop and a database
server. Sure you are more likely to find high speed drives and a tonne
of RAM, redundant everything else and gigabit network cards which may
not make it onto a cheap desktop.

I would rather get to the problem at hand though.

If you show the table structure of your main table, a sample select
and the plan it uses, and demonstrated that you had thought about an
appropriate set of indices to support anticipated queries, we will be
able to offer suggestions of things that might improve performance.

Let me give a quick example. Last monday we noticed one of our servers
seemed pegged on 100%. After analysing what people were doing we
noticed a significant number were in a particular module, and after
about 30 mins analysis, it was clear to me that this module was
running some terrible queries (both unneccessary and lacking
supporting index structures). In my quick and dirty test, I have
observed a 200x increase in performance in this module. You can throw
all the money in the world at hardware, and you will get some return,
but imagine how much better it is if you through money at hardware
with a well written database running on it.

Adam

>
> thanx,
>
> martin
>