Delphi CGIs hanging when too many simulateous DB Connections

The system we are developing uses one database and
can be accessed using BDE from over 20 computers
 simulataneously via Websnap.Each database connection
 uses a different Session.When testing the system with
 21 connections if there is a lot of access to the DB
 all at once (or in a very short timespan) sometimes all the CGI Processes hang.


While the processes are hung trying to open the BDE Administrator produces an error 2A06.

Our customer is currently spitting fire at the
 delay in finding and fixing this problem.

Please any advice would be great , before somebody
 is coming after me with an axe (~_~;;;

Sorry but 75 is all the points I have )-:
brian_sweeneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sfockCommented:
you might have a blocking or deadlocking problem at the database. Wich DB are you using?
0
MotazCommented:
- Did you try to convert it to ISAPI instead?
- Did you close all opened tables using try finally?
- Did you handle all exceptions?

try
  Table1.Open;
  ...
  Table1.Close; // Normal close
except
  on e: exception do
  begin
    Table1.Close; // Error occured
    Response.Content:= E.Message;
  end; // on e:..
end; // try

Motaz
0
brian_sweeneyAuthor Commented:
As Fujitsu is a customer I am using Fujitsu's Symfoware.
Unfortunately there is not much information about this Database on the web.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

brian_sweeneyAuthor Commented:
Thanks for your quick response.
Unfortunately We cannot change to ISAPI, and we cannot change the database.All exceptions are handled and all Database Operations are performed within try..finally clauses.Exceptions are displayed in the browser.
We also tested using an application that launched, accessed the DB and terminated numerous times at the same time as the CGIs were running. After numerous tests the Application and CGIs hung. This was after Application.Terminate was called and the main window closed. Using a log file to debug the application we saw that the DB was successfully closed, the Application Window was destroyed, there was no exception message or any form of sytem dialog box but the the Application Process did not terminate(checked via the task manager). The CGIs terminated eventually after the System CGI Timeout but even then the Application process did not end.

Again it was impossible to open the BDE Administrator application.

The system is Windows 2000. The Delphi Version is 6.

0
sfockCommented:
well it looks like this would be a BDE problem the error code 2A06 maens :Engine not initialized.

let me see if i find some more informations ...
0
sfockCommented:
well i found this article that night be helpful :
http://bdn.borland.com/article/0,1410,27949,00.html
0
brian_sweeneyAuthor Commented:
Thanks but the only problems there , that could possibly pertain to my case are fixed by the Service Pack 2 which we have installed.
0
ziolkoCommented:
as Motaz poinetd out ISAPI would be better solution, but if You still want CGI no rpoblem, in this case it's problem of BDE switch to ADO and multiple users problem will disapear, BDE is not good while more then 16-18 users (different sessions) working at same time.
ziolko.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brian_sweeneyAuthor Commented:
Hi ziolko. Thanks for your reply. Where did you get the information about more than the 16-18 users being not good?
0
ziolkoCommented:
It's not from any kind of documentation but from my experience, I've created web server based on ISAPI in the begining I've used BDE, then tested it for many users executeing quite complex SQL queries and BDE caused many problems so I switched to ADO now software is used constantly by 20-25 users nad I have no problems at all.
ziolko.
0
CuriousDelfCommented:
ADO *IS* a much better solution... faster, smoother, more updates available for it (thus it is allso less buggy). Additionally, you have to realize that unless you are running all of your DB processes as threads, they are probably making the natural program flow wait until they are done. It's simple and stupid, but often overlooked. If you know you have done something long and tedious and about to do something else, just as bad, run Application.ProcessMessages first, so it has the time to deal with answering CGI requests and dealing with any other messages that might have popped up.

Just my 2 cents. Hope it helps.
0
MPountleyCommented:

Remember the BDE is not multi-treaded.

This will cause you problems.

You should not use the BDE in any Web applicattion. There are now other suitable solutions that you can use.

IB Components
and
DBISAM
are very good. DBISAM is one step up from the BDE, but the benefits are big.

Regards,
Mark
0
brian_sweeneyAuthor Commented:
Thanks everyone for answering.Sorry fo taking so long to answer but it took a while to test BDE and all the alternatives. It turned out ISAPI is stable but very slow.
Even when using ISAPI with BDE it wsas necessary to add a Mutex and delay to the CGIs after closing a DB connection so that the CGIs were not accessing the db at the same time.
Although it is more work to change everything now from BDE to ADO (having to retest the entire system again)it seems the best way to go.There is no need for Mutexes or delays.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.