9 Replies Latest reply on May 31, 2012 4:28 PM by beyerch2

    FDM Mapping Template Import Performance

    676575
      I'll just lead off with this statement: "Performance relies on many things and requires thorough analysis to identifiy the root of the issue."

      With that out of the way, does anyone know of a good way to speed up the import of map files?

      Right now, a file with about 2000 maps takes around 2 minutes to upload.

      I kicked off an upload of around 12k maps and it is still cooking (40 mins later). I can see this because the number of pages for the FDM Location - Dimension is growing ever so slowly when I refresh. Clearly, the loading operation is a server process that will continue regardless of end user activity. At the same time there is another map loading to the same FDM location, but a different dimension. The second file has around 900 maps and it is at about 30 minutes so far with 6 pages (a.k.a 600+ maps) loaded.

      Does anyone know how the map import process is handled? And if there is some form of logging that I can turn off so that this will complete in a reasonable amount of time?

      The FDM application has been built on an Oracle Schema that has been tuned based on the guide. (Or so my IT team says.)
        • 1. Re: FDM Mapping Template Import Performance
          TonyScalese
          By default, Import XLS are imported using SQL insert. I have written custom scripts to use Bulk insert which is FAR faster.

          There are many references to using BULK insert on google that can get you started.
          • 2. Re: FDM Mapping Template Import Performance
            676575
            Tony, my current problem is that in the past I've been able to load 32000 map files on much "slower" hardware and have it complete in around 5-10 minutes.

            I'm looking for a red herring and most certainly want to avoid an elaborate scripting solution. I wasn't even aware that I could batch Map File loads. Data files -- yes but not Map Files.
            • 3. Re: FDM Mapping Template Import Performance
              676575
              Just to give an idea. It's going on about 6 hours now to try and write 12000 maps to one location. I don't care if you copy-paste one by one, it shouldn't take this long.

              The DBA took a look and sees single line inserts going in every once in a while. It appears to be an issue on the application side. Should we kill everything and try again?
              • 4. Re: FDM Mapping Template Import Performance
                TonyScalese
                It's tough to comment on this without being able to see what is going on in the app, log files, event viewer as well as the DB tier.

                You can try killing it. It really won't hurt anything to do so. First I would have all of your users get out of FDM and then kill any instance of upappsvr.exe on the FDM app server.

                Try the import with a smaller data set to see if the problem persists. If it does, then you will need to dig in further which again is tough in a forum setting.

                Edited by: TonyScalese on Aug 1, 2011 7:16 PM
                • 5. Re: FDM Mapping Template Import Performance
                  676575
                  Troubleshooting via message boards is indeed hard and I appreciate your assistance.

                  I was unable to locate upappsvr.exe on the FDM app servers. So I killed the excel-32 processes instead as I know those are part of the xls import. A small file of about 43 maps loaded in 3 minutes when I "import xls" from the workbench.

                  To me this is the problem persisting as this should not take 3 minutes. I tried a larger file afterwards and it again confirmed the excessively long write times.

                  We are running windows server 2008 64 bit. Excel, FDM, Oracle Client are all 32 bit. Could this be causing any problem? Honestly, this is our only issue and it only recently started behaving this way. Everything else runs smoothly. I'm really reaching on this one, I've never been this stumped on FDM before.

                  My initial thought is that something happened to the infrastructure and the 2 of us troubleshooting tonight were not informed.

                  Is it a problem that I was not able to find the upappsvr.exe? Sounds important.

                  Once again, appreciate the help and insight.
                  • 6. Re: FDM Mapping Template Import Performance
                    676575
                    So, I switched to loading with Ledger Link style files.

                    This loaded with the expected timing.

                    The IT team has been instructed to look into Excel and DCOM issues. If I get further resolution, I'll be sure to attach in case others have similar issues in the future.
                    • 7. Re: FDM Mapping Template Import Performance
                      AmitR
                      Hi Richard,

                      How did you convert and switch to ledger link style files. We are having the same issue and can't seem to figure it out.

                      Pls advise

                      Amit
                      • 8. Re: FDM Mapping Template Import Performance
                        user10757003
                        I was on a client site last year where we had the same problem. Their DBA decided it was the Oracle DB indexes which were the problem, which i found strange, as i have loaded maps to other Oracle systems without an issue and was able to load the same files on a mickey mouse SQL server VMware application at consistent speeds regardless of how many times i deleted and reloaded maps.
                        Unfortunately having decided to rebuild the indexes just before maps were loaded, they were not prepared to investigate further as i suspect it might be related to an oracle setting. (Many years ago i had similar issues with another product and found the Oracle DBA had been playing about with performance settings and changed one setting but forgot that there was a companion setting that should also have been changed, causing a job that should take an hour to run, to run all weekend without finishing.
                        Rebuilding the indexes did not provide a complete solution to the problem but they would not change their stance on it being casued by something else.
                        • 9. Re: FDM Mapping Template Import Performance
                          beyerch2
                          I'm more than sure these issues are Excel/Office related. Office automation is tricky and doesn't always work well. Microsoft's official stance is that Office Automation is NOT supported (or recommended) in a server environment due to spotty performance issues. With that said, I've had great success with it (non-Oracle product) in specific scenarios (small # of users, small projects) and HORRIBLE experiences (high traffic website w/ 1,000s of concurrent connections)......

                          Some questions and suggestions :

                          Questions
                          ---
                          What version of Excel?
                          What version of Windows?
                          What programs are installed on this computer?

                          Suggestions
                          ----
                          - Office 2003 seems to work well for me. If you are running a newer version of office, try going back to a previous version.
                          - Disable ALL Add-Ins
                          - Disable Auto correct / spelling check, grammar, etc, etc, etc.
                          - Disable automatic formula calculation.
                          - Disable Office Assistant (if 2003 or older version of windows)
                          - Disable Smart Tags (Pre-Office 2010) and "Actions" in Office 2010
                          - Disable Link checking