2 Replies Latest reply on Jan 30, 2011 7:38 PM by howardcrut

    Painful 41 minute load time for Oracle ESS connection.  NEED HELP!

    howardcrut

      Summary

      Painful 41 minute load time for Oracle ESS connection.  NEED HELP!

      Product

      FileMaker Server

      Version

      Filemaker Server Advanced 11 (Version: 11.0.2.217)

      Operating system version

      PC server: 2.9 GHz Intel Pentium Pro/II/III running OS Version: Windows NT 5.2 Service Pack 2

      Description of the issue

      PROBLEM: My server hosted database with an Oracle ESS connection causes my FMP Client to freeze for 41 minutes.  After the 41 minutes of “load time” things run smoothly.  The 41 minutes is a deal breaker for me.  Can someone PLEASE help me understand?  Help me know what I need to do to avoid the load time.

      Steps to reproduce the problem

      DETAILS:
      I’ve got a PC server running FMSA11 with a hosted database that connects via ODBC to mysql and it works wonderfully.  My problem is when I add an ODBC connection to Oracle.

      Once I add any ESS Oracle table to the relationship graph and hit “OK” on the Manage Database pop up screen, the Filemaker Client freeze up for 41 minutes, only the spinning beach ball.  It does not matter if the table I add to the relationship graph has only two records consisting of two text fields.

      After it finally unfreezes, then everything works beautifully.  All the Oracle data is displayed.  Even when I exit the database, everything works fine the next time I launch it, until, of course I go to a layout with ANY field that requires the connection to the Oracle tables.  At that point, I have to pull out my stopwatch/timer to come back in 41 minutes when I can use Filemaker again.  Nothing is accessible w/ Filemaker at this point.  Another words, it is not just freezing that single window, or that single database, but the entire application is temporarily frozen until it loads everything it is trying to load.

      The Oracle box has several PeopleSoft and Oracle databases which I assume consists of hundreds of Oracle tables.  I am not the Oracle DBA.  I don’t even know what any of the other Oracle databases/tables are being used for.  My username/password credentials only permit me to access my single Oracle database which contains about 25 Oracles tables.

      If I include even one Oracle table (does not matter which table) in the relationship graph pulling from an Oracle ESS using ODBC, then it causes the same delay of about 41 minutes.

      I can’t confirm it, but my guess is that since the data I need to access is on an Oracle server that contains so much other data, then it will severely cripple me.  If I had the luxury of having the Oracle data I need to sit on a separate server with nothing else, my guess is that it would be quite fast, but in my case, this is not a possible solution.

      After searching for way to long to try to find a solution, I came across:
      http://forums.filemaker.com/posts/436b8f977f
      where posted 11/25/09 by FluffyBear, ”Filemaker's odbc access is inefficient, that's why it is slow...  It's not the odbc layer, it's just FM make really bad calls. Until they program more efficient ways of handling the data, you're not going to see any improvement.  If you want to know what the thing does, run a query log on your oracle server and see all the extra stuffs that FM do to fetch a single result and you'll know why it's slow.”

      If I connect to the Oracle server from my Filemaker CLIENT on my Mac using the latest Actual ODBC Oracle driver using the same credentials accessing the same tables, then I get the same delay.

      PLEASE HELP!!!
      -Howard

      Expected result

      Expect to get 0-1 minute load time.

      Actual result

      Load time takes 41+ minutes!

      Exact text of any error message(s) that appear

      There are no error messages.

      Configuration information

      OTHER BACKGROUND INFO:
      I’m using Filemaker Server Advanced 11 (Version: 11.0.2.217)
      PC server: 2.9 GHz Intel Pentium Pro/II/III running OS Version: Windows NT 5.2 Service Pack 2
      On the client side, I’m using Filemaker Pro Advanced 11 on a MacBookPro MacOS X 10.6.6
      I’m pretty sure the Oracle version is 10g.
      On the PC server to install the Oracle ODBC drivers, we installed a client under c:\oracle\client.  It is a full client install, so it is bigger.  The ODBC connection uses the tnsnames.ora file.

      Workaround

      The only solution I have found is to pull out a stop watch, come back in 41 minutes, then use the database.  If I have to open Manage Database window, then I'll have to wait another 41 minutes.