Re: ASP & Oracle very slow

matteward_at_aol.com (MattEWard) wrote:

>It seems the time is being spent in
>the web server authenticating to oracle every time it makes a new request.

Yep, you have hit the problem on its head.

>there a way to allow an open connection to remain between the two so the web
>server doesn't have to wait for oracle to authenticate for each request?

Not with ASP to my knowledge.

The interface into Oracle's SQL*Net from ASB VB script entails a couple of DLLs (and/or OCXs). These need to be loaded. DLLs are are only loaded when needed and then only once. An internal counter is kept to determine how many processes are using a DLL. That counter reach zero, out goes the DLL from memory.

If your ASP requests are very slow coming through (one every 30 or so seconds), it quite likely means that each request hit the machine with none of the DLLs still loaded. This means loading the DLLs, establishing the connection, doing the query and terminating. And as no other processes are using the DLLs, they are unloaded too. Only to be loaded again in a little time later time when the next ASP request comes through.

One way around this is to pre-load the DLLs and set their usage count to be higher than the number of processes currently using it. This means that Windows will not unload the DLLs as the usage count will not reach zero (unless you have another app that forces it to be zero and thus force them to unload).

However, this is only part of the problem. You are also paying the overheads of using a scripting language that needs to be interpreted at run time. In addition, you are using ODBC (or OLE) drivers to hook into Oracle. All this adds up to overheads that exist per ASP call.

The bottomline IMHO is that ASP is nice, but far from ideal for using something like Oracle. In fact, Microsoft will be much happier if you use SQL-Server instead and you will also notice a nice improvement in your ASP response times if you do.. Assumming of course you have a little database that is suitable for implementation in SQL-Server.

My solution for the exact same problem at the moment is to make use of custom developed Delphi CGI's that hooks into Oracle. With the HTML producer objects and native Oracle support using Delphi 5, it works pretty fast and is very flexible.

Granted, this is not a solution for a website being hit hard with requests. There are overheads to pay for running external CGI processes vs. internal web processes. One of my next tasks will be to look at useing something like LRWP (Long Running Web Processes) - a kind of CGI that does not terminate, is multithreading capable and can handle multiple "CGI" requests from the web server.

regards,
Billy Received on Tue May 09 2000 - 00:00:00 CDT