Basically, you would have to script the following using a mirror table local to the DB:
- import new records to the local and visa versa
- compare last modified dates on all existing records then copy or import the data to replace existing in the appropriate other table.
Obviously other uses of the data for the purposes of FM would access the mirrored internal table.
Is the Oracle component required, IE can you ditch it and just use filemaker full-time?
If you leave the model you currently have of accessing ODBC data directly, then you will also lose having the data source updated in one place for all users in real time.
Say your filemaker server fails to perform a scheduled sync and it's not discovered for three days, then the data you have in filemaker is out of date by three days (or vice versa). You'll also need to think about building in failsafes and error checking, IE did it finish syncing? How can I check and make sure things completed? In the end, you will still need to access the oracle database once in a while to verify it's synced with filemaker.
I would look into what is slowing down your connection to oracle. I've never used it as an ODBC source, but I have used SQL server as a data source, and addressed speed issues with workarounds and compromises.
What you are saying is correct.
But the major problem what i am facing was the performance.
While connecting to Oracle through ODBC, system performance is very bad. It takes 10 minutes to load 7500 records to a portal during sorting.
Also, i have reports to save in desktop. It is taking much time (more than 30 min to load 7500 records).
Any other ways to increase the performance here?
FM server is located at one place, Oracle server is located at another place. FM Clients they are working is in some other place.
Using Oracle as an ESS table is not great in my experience. It depends on the entire Oracle system itself but from my experience with connecting to Peoplesoft, it was a nightmare. Others had similar issues.
The best option was to import the data periodicly. If you have access to control Oracle you can try and create a view or dump out the table data into its own db seperate from other tables and have your DSN point to that.
You might need an oracle expert here, which I am not. But based on the multiple-location situation, I wouldn't be suprised if you're suffering from massive amounts of poor WAN performance.
I believe filemaker caches all record data from ESS while drawing the display, so if you've got 7500 records, it's possible that it's loading all of that data (especially if you're running calculations on it), during it's load/display request.
To confirm that it's a WAN issue, I'd try switching your configuration to a LAN to check to see if everything performs better on a local network. If that's the case, there might not be anything you can do for better WAN performance aside from some offline data sync which you suggested.
There might be a faster option to mirror the oracle database on your filemaker server, and then access the data from there via a system DSN. (have the filemaker server take care of the data, instead of a user machine). I'm not sure how Oracle handles database mirroring though.
Are you using ODBC Imports? Or are you using ESS? Where are these servers located? What kind of network connections are being used between them? What kind of data are you moving around?
I use FM and Oracle together extensively. My experience has been that ODBC Imports from Oracle are actually quite fast, so my guess is that Mike is guessing in the right direction and that your real issue is network related. There are a dozen ways to approach these types of issues, but we need a bit more information about your environment and application. How often are you updating? How sensative is the timing? etc.
ESS performance can be problematic, but if you have some control over making changes to the Oracle database and dealing with user level security, it can be made to perform fairly well. Because Filemaker does not give you any real means of performance tuning, you have to be creative, but if your willing to do some extra work, the results are worth the effort.
Hi Lee, this was the same issues that we spoke about at Devcon a few years ago. The year you gave your session on using stored procedures with Oracle. That speed issue could never be resolved as we dont have direct control over the Peoplesoft system. I have never had any other "slow" issues with any other databases that I have interfaced with via ESS. Oracle seems to stand alone in that corner.
My experience with Oracle and Peoplesoft mirrors those of Mr. vodka.
Well, I have less experience with using MySQL and SQL Server. But aside from ESS, Oracle is a very fast DB, and definately has one of the most mature and comprehensive Stored Procedure facilities. If you can't mess with the SQL DB, it is problematic. All I can think is that Filemaker has done more work optimizing the other DBs perhaps. I know there are differences in the code used in the Import ODBC functions and the ESS functions, as ODBC Import will handle much larger fields sets, much faster. Have to keep pressing FMI to make improvements in this area with Oracle and ESS in general. ;-)