Loading a library with a large amount of data taking 20-30 seconds

[Tim Wilkinson]

Hi all,

I have a library with a large amount of entries (about 8000). Each time I either open the library or save a new entry it takes about 20-30 seconds to open/save.

Is there anyway to improve the speed of opening a library and also saving an entry into a library?

This issue significantly effects usability in the field to point of not using it and going back to pen and paper.

Thanks

Tim

[Bill Crews]

There are several ways to slow down a Memento database...

One is with expressions (JavaScript & Calculation fields) and scripts (triggers).

Even without triggers, expressions can run long enough to slow things down.

With triggers, you can access and loop through any number of libraries before saving an entry or upon opening a library. That can take a lot of time.

Another way is by displaying a link to entry field in the list, whether as entry name, entry description or entry status. Though you may know that none of these values have changed, Memento assumes they might have changed, so the value is recalculated, which makes displaying the list very time-consuming. (This really needs to be fixed!)

And they might combine, like a JavaScript field displayed in the list and referencing a link to entry field.

I'm sure there are other ways, but these are the ones that have affected me.

[Tim Wilkinson]

Thanks Bill.

All entries names are a Link to Entry, so suspect this is the main problem slowing it down.

I'll have to re think this and the library structure.

memento may not be the solution for the application we are trying to use it for.

Tim

[Vicki Wheeler]

Hi

Did you ever manage to speed things up?

Mine has suddenly started running very slow. I think, maybe due to lots of lookup fields. The fields are there to populate templates when copying to Google docs. I opted for this route because I never managed to get entries from linked libraries to end up on my document although I know it's possible. I've read up on this and found examples of how to use link to entry fields but couldn't get it to work. Any ideas?

[Bill Crews]

If anything, link-to-entry fields are more likely to slow down a database than to make it go faster. Lookup fields (and their equivalents as Calculation fields or JavaScript fields) also slow things down a bit, but I think not as much. If you're using Lookup fields, I believe the libraries need to be linked, anyway.

These slowdowns are mainly experienced when building an entries list within a library, as when opening a library. If slowness is on entry save, it's likely due to something else.

Ernst might know about an alternative. My understanding is that he has (almost?) quit using link-to-entry fields and instead uses triggers with direct references between unlinked libraries in the scripts. This might (or might not) speed things up when building an entries list and maybe on saving an entry. Let's see what he has to say on that. Of course, that'll involve a bit of JavaScript coding, but if It's merely field value lookups, a short script template can make things pretty easy for you -- mainly just changing the field names within the copy of the template to your field names.

--
You received this message because you are subscribed to the Google Groups "mementodatabase" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mementodataba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/dcf8a4df-599b-456a-98a9-b592787e64ccn%40googlegroups.com.

[Vicki Wheeler - Music Agent]

Thank you. I have 3 link to entry fields but about 30 lookup fields. These are only to populate google docs so I think can be replaced on the template according to below but I can't figure out exactly how to format it 

'Syntax for Linked fields

If a Library includes Link to entry fields, you can get more information out of them. Ensure that you are using the corect name for this field type. Here are the rules:

Use a Table in Google Docs if you want to create a list of linked entries:

In Google Docs, go to the Insert menu and choose Table. Adjust the number of rows and columns, then put linked fields to the cells.

Use colon between a Library name and a Field name if you want to refer to the linked Library:

{{Library_name:Link_to_entry_Field_name}}

Use dot if you want to refer to another Field for a linked entry:

{{Link_to_entry_Field_name.Another_field}}'

If I could figure this out at least 25 lookup fields could go.

Vicki Wheeler - Music Agent
07966 360887
www.vickiwheeler.co.uk

[Vicki Wheeler - Music Agent]

This definitely seems to be loading rather than saving. Has anyone managed to get fields from other libraries onto a template to use when copying to Google docs? It works perfectly with fields from the main library I just can't work out how to get the ones from the linked libraries in. Hope someone can help. Thanks.

Vicki Wheeler - Music Agent
07966 360887
www.vickiwheeler.co.uk

[Bill Crews]

This is a wild shot, but you seem to be stuck, so here's another angle on this...

It seems unlikely to me that your database design is optimum due to the fact that you have so many Lookup fields and so few other fields. It sounds like looking up something in a library finds things elsewhere.

The old rule of thumb is that a library (table) should contain fields that are functionally dependent on the entry name (primary key). If the fields "belong" to some other library's entry name, they should be located there instead of here and in Memento can be accessed locally via a Link to Entry field, usually to a limited extent, but I've never seen a database where most fields are foreign (in some other library). Maybe you could solve or reduce your problem if you moved some misplaced fields into the libraries they belong in.

Like I said, this is a wild shot. You may have perfectly good reasons for your design. Maybe if you shared with us your design via either a template file or links to the libraries with public permissions for us, we could help then.

[Vicki Wheeler - Music Agent]

Hi, thanks for your reply.

I'm a music agent so have a library of venues with address, method of payment, contact details, one of musicians with their names and address and band details and the main one for the bookings which brings together a venue and a musician on a specific date, the entries in this library all then relate to the specific booking, times, special requirements etc and work through from booking, reminding, following up, requesting payment, invoicing and producing remittances all relating specifically to the individual booking. The fields I need from the other libraries are contact details mainly to go on paperwork which is produced from main bookings library, there are 10 different templates for these documents all currently being populated with some look up fields as opposed to simply drawing the info from the linked library.

In the help file, that I included above it says you can do this but I'm having trouble understanding exactly what I need to do, for example on a booking confirmation I need to fill in a box called Number of Musicians, this is usually to same for each specific band so is recorded in the musician library rather than bookings, meaning I don't have to fill that field in every time. I just need to work out how exactly to get that field onto the template at the 'copy to Google docs' stage.

Vicki Wheeler - Music Agent
07966 360887
www.vickiwheeler.co.uk

[Bill Crews]

What I used to do as a consultant for large businesses & government agencies is to do a "best practice" called an affinity analysis with the client on a marker board (or 2 or 3). Whatever scope of functionality they said they wanted to address, I led the work at the board by initially just listing all the "things" (or terms) they use while talking about their business process. Some of those will later become primary keys & thus will identify tables, and some will become fields within a table. Then I lead them through identifying functional dependencies between them all. That will make apparent the keys versus the other dependent fields.

Mapping to Memento, the functional dependencies will identify entry names versus dependent fields, and each particular functional dependency will identify the entry name & thus the library it should be in.

I'm a singer, and not a professional one, so I have a database including a Songs library, a Performers library (some being bands or groups, & some being individuals). Then, I have Sets (of songs I sing), Albums, & Album tracks. I've thought of including libraries like venues, impresarios (or promoters), recording labels, etc. As an amateur, I'm more of a listener, and leaning toward recording as much as performing. I also have libraries for MediaLinks and Playlists (which relates to Sets).

So, other good libraries to have would be Venues, Promoters, and for me Recording labels. I could send you my Songs database as a template file if you like, and you could quickly create it yourself from that. If you want to see my data, I could send you links (URLs) to the actual libraries in my cloud.

But in terms of functional dependencies, the lyrics, arrangements, & performances go in Songs, Performers are reference data & thus are typically linked to & don't link to others, but they contain a Link back to Performers itself, which maps performers to the bands they've been in. (Yes, you can do that.)

Now I have less than 500 songs in my library, but I do have these libraries pretty heavily linked, and I don't have burdensome delays when opening libraries and no delays at all when saving an entry. If yours are linked similarly to mine, it must be just the number of entries in your libraries that is the culprit. I do have a Medications database, and between linked Caregivers, Prescriptions, Refills, Medications (drugs), and so on, I have some delays in opening libraries. That's the experience that alerted me to the slow performance of entry links.

I remind you that Ernst Moser has said that he tends now to use triggers and other scripting to circumvent these link delays, so you might try sending a private message to him on that, but what I don't like about that idea, beyond the need for scripting, is that the wonderful effectiveness of the libraries list depends on fields being accessible within either libraries or linked libraries. Then, you can sort them, filter them, group them, aggregate them, and chart them right there in the list, which I find extremely useful.

To me, Musicians, Venues, Bookings, all sound fine to me as libraries, with addresses and such depending on what they are addresses of (dependent upon). You might need Customers or Clients, Invoices, etc. It sounds like all the forms you need to fill out for Bookings might be your other place to bog down, with Lookup fields being the likely culprits. But unless the Memento developer can come up with faster linking, database design based on industry best practices, like I was using in my consulting & which I use in my designs, might be a slow process for those with large linked libraries.