This content has been marked as final. Show 9 replies
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.
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.
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?
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
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.
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.
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.
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.
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 :
What version of Excel?
What version of Windows?
What programs are installed on this computer?
- 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