Re: Oracle performance with Microsoft Project

"tom thayer" <tomthayer2_at_yahoo.com> wrote in message news:3228903a.0309040520.72522c3_at_posting.google.com...
> We are running Microsoft Project 2000 against tables in a database via
> ODBC. We have large projects and the performance is terrible when
> saving changes. MSP updates the entire project even if you only change
> a few things. The tables in the database are tied into a 3rd party CRM
> so using MPP files is not an alternative. The database being used is
> Oracle on an HP-UX system with lots of memory. The database is on a
> different machine than MSP. The best save times we get are with the
> Oracle ODBC driver. Even though the save times are long, we note that
> if we use a SQL Server database (also on different machine), the save
> times are < 40% of the save times against Oracle. E.g, a save time
> that took 5 minutes on Oracle only took 2 minutes on SQL Server, or 20
> minutes on Oracle only took 5 minutes on SQL Server. We have done
> performance tuning and looked at Oracle Performance Manager and TOP
> SQL and see that the sql is optimized. Why is the SQL Server path so
> much faster than the Oracle path here?

ODBC, eh? What version of Oracle is the HPUX box running? Is it running Unix or Linux or Windows?

I assume the SQL Server is running on a Microsoft system; are you using ODBC to connect to SQL Server?

Here's my guess at the answers above: I'm guessing that the HP-UX is not running Windows, so you're limited to ODBC. The SQL Server's box is probably running an NT-based Windows O/s (Windows NT 3.5/4 or Win2k or Win2003 server). You're probably using an OLE DB provider when you connect to SQL Server, but the "reliable dinosaur" ODBC for connecting to your Oracle. So I'm pointing towards ODBC as the bottleneck. Try loading Oracle onto a Win2k server, and if it's at least Oracle 8, you can use Oracle's OLE DB Provider. (Don't use the Microsoft OLE DB for Oracle driver, I found it to be unreliable.)

The Oracle system might also be tuned incorrectly. I believe some operating systems could induce paging (swap file usage) depending on the memory asked of it. So you say the HP UX box has a large amount of memory- if the SGA was tuned for a large amount of memory, but the o/s converts that SGA size into a swap file, you could be getting hurt by disc access. Dropping the SGA size might help.

Also, were separate tablespaces used for indexes & tables? Or is everything in one tablespace?

From my limited experience with SQL Server (mainly 6 and 7 and 2000), there's nothing to tune; it handles itself. Therefore an out-of-the-box SQL Server installation might run better initially then an out-of-the-box Oracle installation.

Just some wild guesses...

-Thomas Received on Thu Sep 04 2003 - 13:09:16 CDT