Re: Improving CGI/ODBC/Oracle8 Performance

Billy Verreynne wrote:
>
>Sean Black <zoot_at_mcs.net> wrote:
>> It appears that the vast majority
>> of that time is spent in making the connection from the application to
>> Oracle8--once connected, the queries seem to execute very quickly.
>
> Why not try the ISAPI (Microsoft's Internet Server API) or NSAPI (Netscape
> Server API)? ISAPI (not so sure about NSAPI on Unix) is a DLL on NT that is
> dynamically loaded by the web server. When this DLL is loaded, it can
> connect to Oracle and multi-thread web browse "CGI" requests afterwards on
> this connection. I suggest going to the Netscape or Microsoft web sites for
> more info on how these APIs work.

For a variety of reasons (stability, portability, speed of development and debugging) this isn't an option. 8-(

> Another reason why connects may seem so slow, is the time it takes to load
> all the DLLs into memory for a client server connection. When using Oracle
> over ODBC, several DLLs are required and must be loaded by Windows. If that
> application or CGI terminates and the internal usage count of these DLLs
> drop to zero, Windows auto unloads them to free up memory. So the next time
> the CGI runs the DLLs are loaded all over again.

This need to load several DLLs is also true of SQL Server's ODBC driver, though, and its performance is much better. To be honest, when using SQL Server the first execution or two of the application is a bit slow (I've always attributed it to having to read in the application .EXE and the ODBC DLLs from disk into the NT disk cache) after which things execute quickly. The problem we're experiencing with Oracle8 is, even after running the application a couple of times, performance still isn't very good. 8-(

> A simple trick may be to write a little app that simply dynamically loads
> these DLLs at boot time so that they are there ready in memory when the CGI
> runs.

This is an idea we might give a try, though I don't think it will impact performance significantly since after the first couple of executions the DLL files should already be in memory in the disk cache--from there, copying/mapping them into the application's address space should be a fairly quick process, no?

Thanks,

--
Sean Black
zoot_at_mcs.net