I have a custom table in Dynamics NAV 2009 (Classic client, SP1, SQL Server option) that has a column for pictures. The entire table is queried to be displayed in a list. If I want to open the table from object designer it takes five minutes to open because of the pictures. If I want to display the list of rows from the table (pictures aren’t needed) it takes roughly the same amount of time.
Are there some good strategies for handling large objects in tables so the queries don’t take such a ridiculous amount of time?
Sorry if I’m being too vague, being new at things is tough!
there’s about 28k rows in this table, each with a picture. And they are stored as BLOBs.
I’ve been trying to wrap my head around using keys, and I’ve actually fixed it a couple times by optimizing the tables and rebuilding the indexes and all the wonderful tips and tricks I’ve read perusing this site and many others, but I don’t fully understand HOW it was fixing it.
On top of that, performance on this table will be improved for about a day or so before taking five minutes to load the form/table.
That blog was incredibly helpful for understanding the performance issue, thank you!
I actually wrote a SQL script that rebuilds indexes but it stopped helping after it fixed everything the first time.
Yes, there are keys defined and, forgive me if I sound like I don’t know what I’m talking about because I don’t, I’m calling the key that is linked the clustered index. What kind of information would you like on the index/tables?
I changed the compression in the column settings in NAV to FALSE, re-organized the index rebuilt the index.
Everything is working just fine, now. But this has happened before. I will walk away victorious and return two days later to see everything hanging again.
I even set a SQL job to rebuild the index EVERY day just to be safe but it didn’t necessarily help.
Rebuilding the index will help but if the form/page is not using the correct key then performance will suffer. To check this - can you list the field that you are filtering & sorting on and the fields on the key you are using. If you have not specified a key then it will use the primary key.
A good example of this (and a common cause of poor performance) is the Item Ledger Entry. The primary key is entry no. but most queries are by item no. so a key change is advisable.
The database size will grow very quickly with storing this amount of data i.e. 28,000 records * Size of picture. Would you not be better storing the pictures on a shared drive and holding the URL in the table or use the standard link function in Nav?
Hey sorry for it taking me so long to get back to you! Teaching myself NAV is quite the pain
What I think the problem is is with the keys/indexes.
I ran Client Monitor and the query that takes forever isn’t even checking for pictures, but it’s still taking forever (5 minutes to run). Is there some format I should display the table information in and the keys for you to look at? I really think this is something fairly easy and I need to redo how the indexes were created, I just don’t know exactly how to go about doing it. I only have a basic understanding of how indexes work.
It only happens when I try to search on a specific field in the list form. I fixed the entire list hanging by creating new indexes and getting rid of the old ones. And the delay only happens when I try to “Find First” or the search goes past the end point.
I feel like I’m being too vague, still, should I post the exact query? I don’t know if I should be posting customized data and such.
