Re: Oracle performance with Microsoft Project

Sten Rognes wrote:
> I worked with a MS Project 2000/Oracle environment a couple of years
> ago and it did not take much time with the product and MS support
> techs to figure out that this is an application that really only will
> scale when used with SQL Server. I bet you can spend one millon
> dollars buying more hardware and 6 months tuning your Oracle database
> backend without getting the environment to scale. The application is
> the problem and the vendor has minimal experience and interest in
> changing the app to work efficient Oracle.
>
> Sten
>

I was also involved with an attempt to use Oracle with MS Project 2000 (about 3.5 years ago, IFRC). We finally put a network analyzer on a simple "open project" request. The result was even worse than this thread's previous comments would suggest. The tables were designed for an old version of Sql Server (multiple CHAR columns of 255 rather than one of 2000). But the worst was the embedded SQL logic: as an example, if table A was joined with tables B, C, and D, the trace revealed that table A's primary key was fetched 4 times, just to make sure that it was still there (if it was actually used, it was fetched again).

What we had to do was to create a shell around MS Project (Corporate politics dictated that Project and Oracle both be used). The user would work on a project in MS Project. When it became time to save to the database ("publish the project", "check it in", whatever term you want to use) the shell would create a .csv version of the data. It would then be FTP'd to the Oracle server, where a daemon was running. This task would detect that a project was being uploaded. It would promptly spawn another task which would SQL*Load the .csv to a temp table and run a PL/SQL routine to update the main database.

Subsequent work on the project required sending a request to the shell to unload a .csv version of the project.

This actually worked. Fortunately, corporate politics did not require that anyone actually used this mess, so we never found out how well it would scale (perhaps I should have worded that "how poorly it would scale"). An earlier post suggests that Daniel found a real solution to this problem, and I would be very interested in a sketch of where the cure is.

Phil Singer			psinger1_at_chartermi.invalid
				do the obvious to reply