16 Replies Latest reply: Dec 1, 2014 3:03 PM by Nizam IGEN RSS

    Speeding up lookups

    TMERRYMAN _

      I currently have two external lookups for a database in my model.  It takes a long time (15 to 20 minutes approximately) once I open my model to be ready to work in it.  Do you have any tips for speeding the process up?  I have a filter for my database to reduce the size coming into the model.  I can not reduce the lookup files any more.  Is it the row numbers or the file sizes that slows it down?  Here is my breakdown:

       

      Database

      23,000 rows

      31,855 KB

       

      External Lookup 1

      176,200 rows

      6,701 KB

       

      External Lookup 2

      6,300 rows

      900 KB

       

      I would appreciate any tips you can give.

       

      Thanks,

       

      Teresa

        • Speeding up lookups
          Olly Bond

          Hello Teresa,

           

          Are the databases local files (like MDB) or ODBC connections to network data sources? Perhaps you could create separate models to open the external lookups as tables, and time them?

           

          I have a hunch that it's the row count of the first lookup that's causing the long delay. Is there a way you can normalise that data a little, as you can only need 15% of it?

           

          Best wishes,

           

          Olly

            • Speeding up lookups
              TMERRYMAN _

              Hello Teresa,

               

              Are the databases local files (like MDB) or ODBC connections to network data sources? Perhaps you could create separate models to open the external lookups as tables, and time them?

               

              I have a hunch that it's the row count of the first lookup that's causing the long delay. Is there a way you can normalise that data a little, as you can only need 15% of it?

               

              Best wishes,

               

              Olly[/QUOTE]

               

              The database is a local file (xlsx).

               

              I'll try to time them and see if it tells me which is the problem.  As for the first lookup, it is only 3 columns, and I need it all.  Do you think by breaking it up into more than one file (lookup), it would speed anything up?

                • Speeding up lookups
                  Olly Bond

                  Hello Teresa,

                   

                  Is the row count of the database from before or after the filter being applied?

                   

                  With only three fields in lookup 1 I'm surprised at the times you're reporting. Is the data very sensitive or could you email it over to me at olly@greenbar.info[/email] and I'll try to replicate?

                   

                  If the external lookups aren't local, then I suspect the delay is caused by Monarch accessing the external lookups. You might be able to speed it up by having separate projects, one to bring in each lookup and export it simply as as a local MDB file.

                   

                  Then you could run your main project all with local data and I imagine you'd see a dramatic improvement.

                   

                  Best wishes,

                   

                  Olly

                    • Speeding up lookups
                      TMERRYMAN _

                      The row count of the database is before it is filtered.

                       

                      The external lookups are already local xlsx files.

                       

                      I can email them.

                       

                      Hello Teresa,

                       

                      Is the row count of the database from before or after the filter being applied?

                       

                      With only three fields in lookup 1 I'm surprised at the times you're reporting. Is the data very sensitive or could you email it over to me at olly@greenbar.info[/email] and I'll try to replicate?

                       

                      If the external lookups aren't local, then I suspect the delay is caused by Monarch accessing the external lookups. You might be able to speed it up by having separate projects, one to bring in each lookup and export it simply as as a local MDB file.

                       

                      Then you could run your main project all with local data and I imagine you'd see a dramatic improvement.

                       

                      Best wishes,

                       

                      Olly[/QUOTE]

                        • Speeding up lookups
                          Olly Bond

                          Hello Teresa,

                           

                          An email would be great. Just a short point, both the transfer from network drive to local drive, and the conversion of other formats to neat, normalised Access tables with a couple of preparatory Monarch projects, can all be automated with a batch file.

                           

                          I look forward to seeing the data.

                           

                          Best wishes,

                           

                          Olly

                            • Speeding up lookups
                              TMERRYMAN _

                              I would love to do that, but don't know how.  I was actually looking for instructions on Excel with Monarch and trying to find info in the forum on it.

                               

                              Hello Teresa,

                               

                              An email would be great. Just a short point, both the transfer from network drive to local drive, and the conversion of other formats to neat, normalised Access tables with a couple of preparatory Monarch projects, can all be automated with a batch file.

                               

                              I look forward to seeing the data.

                               

                              Best wishes,

                               

                              Olly[/QUOTE]

                                • Speeding up lookups
                                  Olly Bond

                                  Hi Teresa,

                                   

                                  Before Kruncher gets in there, www.********************[/url] offers the free Batch File Generator. But in a nutshell:

                                   

                                  C:\Program Files\...\Monarch.exe "Project.xprj" /pxall[/CODE]

                                   

                                  will call Monarch and open a project and run all the project exports you've defined. There's a whole world of more advanced scripting and programming to explore after that, but for the basics that should meet most needs.

                                   

                                  Best wishes,

                                   

                                  Olly

                                    • Speeding up lookups
                                      Data Kruncher

                                      As to copying the files from the network drive to your C: drive, manually inserting a line or two like this:

                                      copy
                                      server\folder\file.xlsx C:\folder\file.xlsx[/CODE]

                                       

                                      or

                                       

                                      copy Y:\folder\file.xlsx C:\folder\file.xlsx[/CODE]

                                       

                                      before the Monarch line in the .bat file that the Batch File Generator produces would work just fine 99.9% of the time. For the other 0.1%, you'd want to be more careful and ensure that the copy worked properly, but that might[/B] be "over the top" and unnecessary for most applications.

                                        • Speeding up lookups
                                          Data Kruncher

                                          Those copy command examples should probably have this syntax:

                                           

                                          copy /Y
                                          server\folder\file.xlsx C:\folder\file.xlsx[/CODE]

                                           

                                          The /Y parameter forces an overwrite of the destination file without the batch file stopping to prompt you and ask if overwriting the existing file is OK with you.

                                           

                                          Assuming that the network version of the file is the current data, then forcing an overwrite in all cases won't cause problems for you.

                                            • Speeding up lookups
                                              Nigel Winton

                                              Question, do your Monarch models have summaries? If so read on, if not read on anyway it might help later.

                                               

                                              I have a lot of Monarch Projects that do lookups to varying size of files all of them on the network. Most of them are to SQL files but some are Excel and I cannot move any of them to my C Drive.

                                              I found that my original projects had summaries which were using the linked data and this was slowing things down.

                                              My solution, I now have one project that does all of the linking and then exports that data to an Excel file or an Access database.

                                              My second project uses that file as a database import and creates all of the summaries and exports them. The overall time saving is quite a lot. Also you have a base file you can use for other projects without having to do all of the linking again.

                                              Kruncher's Batch File Generator helped in automating the process.

                                               

                                              It might work for you.

                                               

                                              Regards

                                               

                                              Nigel

                                                • Speeding up lookups
                                                  Data Kruncher

                                                  That's an absolutely brilliant idea Nigel. :cool:

                                                   

                                                  Clearly there are cases when moving the data source isn't possible, and your suggested technique, especially when used in conjunction with a batch file or other automation tool, is an easy to implement method to work with, or around, one of the few performance bottlenecks that Monarch can struggle with.

                                                   

                                                  Nice work.

                                                    • Speeding up lookups
                                                      elginreigner _

                                                      Those are very small lookup files though,this sounds like a hardware limitation. I have some projects doing lookups against multiple files 250 to 500MB each with 4 to 6 million records on a networked drive and only takes about 2 minute to open (with summaries).

                                    • Speeding up lookups
                                      Data Kruncher

                                      Make sure that the Excel file is stored on your local hard drive (like C: ) and not on a network drive. While Monarch can use them, they absolutely kill performance when working with large files.

                                       

                                      So if that means that you copy a file from the network to your C: drive before you begin, then that's what you will need to do before beginning your Monarch work for this task every time.

                                        • Speeding up lookups
                                          TMERRYMAN _

                                          Well, maybe that is my problem then; all three files are on the network.  I guess if all else fails, I can copy them to my hard drive.  Thanks for the input!

                                           

                                          Make sure that the Excel file is stored on your local hard drive (like C: ) and not on a network drive. While Monarch can use them, they absolutely kill performance when working with large files.

                                           

                                          So if that means that you copy a file from the network to your C: drive before you begin, then that's what you will need to do before beginning your Monarch work for this task every time.[/QUOTE]

                                    • Re: Speeding up lookups
                                      BShipman _

                                      I am interested in this topic as I am also having issues with this.  I am trying to get a overall sense of how Monarch works so that I can do more troubleshooting.

                                       

                                      In general, should any lookup saved locally run faster?  I have done some tests with my problematic models and I am not always finding this to be the case.

                                       

                                      Also, does it matter where the Monarch program file and models are saved?

                                       

                                      What are the best practices for storing he program, models and lookups?  If a model gets too large (like too many filters, sorts, calculated fields, etc., can performance seriously suffer?  We are finding some cases where Monarch just hangs and will never progress and others where it takes an incredible amount of time.  We have had intermittent issues, but have now had suddenly large problem after moving to a different server within the network.  I feel like something is wrong with the way that we have everything structured and that it is causing a slowdown in accessing information.

                                       

                                      I know that this is a lot, but I need a more theoretical sense of how Monarch stores, uses and structures information.  Thank you to anyone with ideas.