Large dynamic field list loads slow

asked on January 13, 2023 • Show version history

We have a large client who is trying to use a database query that returns roughly 500,000 student names into a dynamic field dropdown. We've attempted to use the attributes for maxdropdownlength and Cachefieldvalues but there appears to be an error and limitation within the chrome browser itself causing it to fail when using these attributes. error attached. They therefore are stuck with long load times and we were wondering if we could request a change in functionality.

Currently the lookup happens on focus of the dropdown window, which causes the 500,000 result lookup to happen right away before they begin typing the letters. Is there any other functionality or attribute out there or that could be developed that made it function where the box is typeable until a certain x number of characters, and *then* do the query for results? If it didn't query until after they typed the preceding x characters, they could get around the large lookup that happens on focus.

Let me know if you have any ideas or if we can simply request this as new functionality, thanks!

Cache Quata Exceeded.png (26.47 KB)

| Download

0 0

replied on January 16, 2023

Hi Brandon,

500,000 is a big list! Just out of interest, is this referencing a view or a table in SQL, and how long does it take to load all the rows in SQL itself?

Cheers!

Chris Douglas

1 0

View 3 previous replies

replied on January 17, 2023

Hi Chris,

It's a table in MS SQL Server. I connected to the database via SSMS on the Laserfiche application server and ran, select studentnumber from lf_aux.sr_studentdata. The query took 10 seconds to complete.

It appears that as soon as a user tabs to or tries to click in the studentnumber field in Laserfiche, a query executes. That is, the user can't even start typing in the field until the initial delay resolves. In the web client, the delay is 14 seconds. In the Laserfiche windows client, the delay is 30 seconds.

We are trying to find a way to prevent any lookup (query) from executing until the user has typed at least a couple of numbers in the field.

Let me know if you need additional information.

Thanks,

Robin

0 0

replied on January 17, 2023

Robin, is this a custom database or a 3rd party vendor database that holds the data?

0 0

replied on January 17, 2023

Blake,

It's a custom database created by our Laserfiche VAR. Our VAR created a process to refresh the table from our student information system. I believe the refresh process runs nightly.

Robin

0 0

replied on January 17, 2023

Do you know if there are any indexes that were created for the table? If not, I would take a look at the Query Plan for the queries being executed against the table and see if it suggests creating any indexes. It can significantly decrease the time it takes to execute the query to retrieve them.

0 0

replied on January 18, 2023 • Show version history

Hi Robin,

As a suggestion, maybe you can consider using a pre-filter to avoid returning this huge amount of data.
Something like the first letter of the student surname, then apply the filter to return the result for student having surname starting with this letter.
Thanks.

0 0

replied on January 18, 2023

Hey Brandon,

10 seconds isn't too bad to return the row in that amount of data. What you need to bear in mind is that Laserfiche behind the scenes is doing a select all from that table when you load it into a template field, so that's the timings you want to look at. There unfortunately isn't really a way to change this behaviour.

Do a select * from tablename and see how long that takes to load.

Like the guys are alluding to, you might need to try and segregate this data into smaller chunks for Laserfiche to support in a better way, like Maheshsingh says alphabetically might be a good idea with indexes on those new tables like Blake says. You could link these together via a dynamic field, the parent field being the first letter A-Z, and then child field being the values. That might help speed things up for the user experience.

Unfortunately, you don't have a lot of options with that amount of data, but the above responses are solid suggestions.

Cheers!

Chris

0 0

Question

Question

Large dynamic field list loads slow

Replies

Sign in to reply to this post.