5 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Slow Export of Summaries

    David Musgrove

      I have a large report which is being taken into Monarch v8.1 and then manipulated with additional lookups to Excel sheets and added calculated fields. When the data is taken into a Summary format with a project - it takes several minutes to calculate - which is expected. However - when doing a project export into Excel the data ( roughly 40k lines ) takes approximately 5 minutes per 1000 lines. I would have expected the summary to export in about 10 seconds. It looks as if the program is doing some calculation when exporting which I thought it would not do as its simply dumping the data to another location. Any ideas anyone as my present solution - although working is impractical time-wise?  :confused:

        • Slow Export of Summaries
          Grant Perkins

          Hi David,

           

          It might be worth checking to see what your settings are for using Excel formatting when exporting. If it turned on, turn it off and see what difference it makes.

           

          If it is already turned off then I guess the problem is certainly elsewhere.

           

          As far as I know Monarch tries to use standard MS utilities for creating Excel files in order to obtain the clear benefits of compatibility and advances in functions. I don't often require large exports to Excel so I don't feel I can comment with expertise on this one. However I do know that working with very large Access tables can take some time to process. Nevertheless 5 minutes per 1000 lines does seem a bit extreme.

           

          It is also possible that the nature of your process requires some recalculation despite the pre-calculated summary display seeming to be complete.

           

          If you can let us know the answer to the Excel formatting question (and also which version of Excel you are exporting to and maybe an indocation of the version of Windows you are using) I will see if I can make some time to run a few similar exports and report back on the effect.

           

          Grant

          • Slow Export of Summaries
            David Musgrove

            Grant - thanks for your help. Ive exported 1 million line files to Access no problems at all with the same build.They took less than it takes to run 1000 line ones with the problem one at the moment!.Im using Windows XP Pro with Excel 2003. Formatting was turned on...and with it turned off there is no diference in speed. It must be difficult to ascertain the problem without the source files and models.

             

            Thanks again

             

            David

            • Slow Export of Summaries
              Grant Perkins

              Hi David,

               

              It's interesting that turning formatting off makes no difference - I must confess I have not yet installed the 8.1 upgrade and I seem to recall that V8 was an improvement over previous versions but I would have expected some change with that size of Excel output.

               

              You are right about it being difficult to replicate but I do have some large database files and a complex model or two with lookups and calculations and about 100 fields per record so I may see something.

               

              I'll try it with 8.0 first and see what happens.

               

              Grant

              • Slow Export of Summaries
                RalphB _

                Hi David,

                 

                We had a similar problem when exporting large summaries (40,000 lines) into Excel when using Monarch V7 Pro but that was fixed in the update patch for V7.  I still export large summaries in V8.0 and now with V8.01 Pro with no problem.

                 

                On thing I might suggest is that you might want to check to make sure you have the latest Microsoft Jet 4.0 Database Engine installed.

                • Slow Export of Summaries
                  Grant Perkins

                  Hi David,

                   

                  Apologies for the delay in getting back to you on this but I have now run an experiment with approximate timings using version 8.0 as follows.

                   

                  The source is an Access database created from several tab delimited files. It contains 550,767 records as tested.

                   

                  The model applied displays 64 fields, many of them calculated originally but mostly stored fully calculated in the database. (Not quite as you have things but the nearest I could get at quickly without doing any digging for something more like yours.

                   

                  I ran a Compound filter (using 2 fields, one with a specific value of 4 possibles and the other with a less than value giving 16 possibles) and I am exporting from the resulting table in this case. (Manual Export rather than a project export but I would not expect this to make much if any difference. Could be wrong about that.)

                   

                  From start if the process to the point where the 'Writing records' message appeared took 1min 30 secs.

                   

                  40 K records written came up in approx 2:40

                   

                  48k written in 2:55

                   

                  Excel formatting started after 3:18 elapsed and the total time for the process was 6:20.

                   

                  I then created a summary with a number of key fields that would produce the same record set at the same level of detail - to 48621 data rows to Excel.

                   

                  Opening the record set timed at 6:36

                   

                  Writing records started after 6:46 and ended at 9:40

                   

                  Excel formating started at 9:50 and completed at exactly 13:00 minutes elapsed, resulting in a 19Mb file.

                   

                  This is running on a P4 2Mhz system with 1Gb RAM (iirc) and 1x120 Gb disk partitioned as 2x60Gb (both partitions quite full) plus a 160Gb disk about 45% full part of which is used by Monarch as its work area. The system is running Win XP Home.

                   

                  There are several other applications loaded at the same time, including several instance of Monarch, 2 instances of Excel, IE, Opera, notepad and some explorer tasks.

                   

                  How much of this is relevant to your  situation I don't know. And it is worth observing that my starting point is a pre-loaded and pre-filtered set of data (which processing does take quite a while). But from your comments I think you are already excluding that period of time from the overall process time since you expect it to take a while.

                   

                  The heaviest use of system resource seems to be disk activity on my system. Is it the same on yours?

                   

                  As you said before, not necessarily a directly related test but at least an assessment of an export of a similar scale.

                   

                  I hope this is somehow helpful.

                   

                  Grant