Subject Re: Performance
Author Todd Brasseur
Thanks for the reply ... see my comments below:

--- In ib-support@y..., Lester Caine <lester@l...> wrote:
> > Our application has some processes that run for an extended period of
> > time(up to 4 or 5 hours) as there is alot of complicated calculations.
> > These processes are written with Stored Procedures (One main
> > procedure that may call many other procedures.)
> >
> > We are experiencing problems where when these procedures are running,
> > they take over the server. IBServer shows it is taking 99% of the CPU
> > causing all other users to virtually stop when trying to access the
> > server.
> >
> > We experience a similiar problem when users run complicated queries.
> >
> > 1) Is it adviseable to process these types of calculations in Stored
> > Procedures or should they be written in C++?
> Anything that reduces the amount of work that the server
> does will help. You don't say what the processing is, but if
> it can be run on a client machine and only hold up the
> server when writing each result then that would help from a
> load sharing point of view. Some things are great built into
> the server, but from a load sharing point of view, getting
> each client to do complex stuff and just post the answers
> can give much better responce times.

So much for Client Server.

This application is a rewrite of a Clipper / DBF (DOS Application)
Version and an AS/400 version. I thought the benefit of Client Server
was that you could send a request to the server and the server would
do the processing without having to bring all the data across the
network to the client machine. Reducing network traffic would mean
better performance. The Clipper / DBF application could run the same
process in 9 minutes that now takes 3 hours. If we re-wrote the
entire process in C++ then it would allow the Client Machine to do
the work but would defeat the purpose of being Client Server. I was
hoping that writing the process in C++ and running it on the server
would increase performance without having to increase Network Traffic.

The performance of both the Clipper and AS/400 applications was much
better than the Firebird one is.

> > 2) Is there something we can do to ensure the server isn't taken over
> > by Firebird?
> Linux or Windows?
> Linux is much better at 'load sharing' than Windows ( which
> is NOT a good multi tasking OS ), so the problem here is set
> the priorities so that Firebird is kicked out when something
> more important comes up.

Windows. We have approx. 70 clients and they all run windows.

> That does not help with the problem that a complex query can
> lock out other database users, but if the volumn of work
> means that Firebird is hogging most of the processing time,
> then you need another server for the rest of the jobs or a
> faster processor / more memory on the existing one.

The server it is running on is one year old and cost $10,000.

Is it possible to add another server? Can two servers serve the same

> Not a very satisfactory answer, but this is not simply a
> Firebird problem.

I am glad you brought this up. We have clients who are asking us to
switch to SQL Server. Would we have similiar problems? We are
talking about an application that has to handle about 30 users at
most. How do people handle this when they have hundreds and
thousands of users querying a much larger database?

Thanks again.


> --
> Lester Caine
> -----------------------------
> L.S.Caine Electronic Services