List form selecting every field causing slow performance?

Alex_Chow · 2010-02-16

We have a database that is on version 3.7B but the executables are upgraded to NAV2009 SP1.

Everywhere in the system, the performance has increased except for the item card and the item list. The performance on the item card and list has severly decreased in performance, it takes roughly 3-4 seconds when I click on each item from the list. When I push page up or page down, I have to wait about 3-4 seconds as well.

There are only 30,000 items in the system, however, there are 280 fields in the item table with quite a few flowfields.

Initially, I thought the flowfields displayed on the list is causing the problem. I created a list form with only the item number and description, but the performance is the same.

Turning on the client monitor, it seems that the SELECT query is causing the slowdown. It's select every field on the item table, then the following: WHERE "No_">@P1 ORDER BY "No_" OPTION (OPTIMIZE FOR UNKNOWN)

The WHERE "No_"=@P1 ORDER BY "No_" OPTION (OPTIMIZE FOR UNKNOWN) is causing some problems as well.

The customer list doesn't seem to have a problem with the query.

A couple of interesting points:
- The database uses $ as part of the item primary key
- Database is SQL Server 2008 Standard

Any help would be greatly appreciated!

Alex_Chow · 2010-02-16

Ok, I know where the performance problem is.

The problem is caused by the number of Normal fields. I copied and pasted the SELECT statement into the SSMS and ran the query.

If I ran the query as is, it takes 7 seconds to execute. As I start to trim down the number of fields in the SELECT statement, the performance started to be faster and faster.

Anyone know why this is? Does it mean that if you have a lot of add-ons, your performance will always suck?

And why is it getting all the fields on the record when I open the item card? I recall in prior versions, it only grabbed the fields you have on the field?

And why is it grabbing all the records in the WHERE No_ > xxx? When I'm only getting a single item on the item card?

David_Singleton · 2010-02-16

SELECT * is a huge killer in Navision. In SQL there is no way for Navision to tell SQL only to pull the needed fields.

I wrote a Blog about his some time ago http://dynamicsuser.net/blogs/singleton/archive/2009/03/14/select-from-and-how-to-do-it-better-in-nav.aspx

At Convergence a couple of years back, I found that someone in Denmark did read this. There are plans in place to put some sort of functionality like this in Navision, but at the moment the limitations are compatibility with the Native database, and how to cache selected queries. On tables with lots of fields though this will make a huge difference.

David_Singleton · 2010-02-16

Alex Chow wrote:

... I recall in prior versions, it only grabbed the fields you have on the field?
...

No it has always grabbed everything. One of the reasons Navision can't work over a wan.

Alex_Chow · 2010-02-16

It looks like they did change it a bit. Instead of SELECT *, the SELECT statement actually selects all the Normal fields.

So it's a giant SELECT query.

Alex_Chow · 2010-02-16

David Singleton wrote:

Alex Chow wrote:

... I recall in prior versions, it only grabbed the fields you have on the field?
...

No it has always grabbed everything. One of the reasons Navision can't work over a wan.

Sorry, I was thinking flowfields.

stryk · 2010-02-17

Indeed, the problem with SELECT * is that mostly way too many fields are retrieved, remarkably increasing the I/O.
In newer versions you see an explicit SELECT [timestamp], [No_],... only on tables which have a BLOB field defined (e.g. "Item"."Picture"). The idea is obviously NOT to query all fields if not really needed (see David's "SQLRecord" idea).
Currently, even the explicit SELECT queries the BLOBS ...

... and this is often the trouble with the queries you describe. I had similar cases, and here the solution was to disable the BLOB in NAV; e.g. creating dedicated tables as "Item Picture", moving the BLOB to it. In most cases the queries performed remarkably (!) better!
See also http://dynamicsuser.net/blogs/stryk/archive/2009/02/12/blob-fields-with-nav-amp-sql.aspx

Cheers,
Jörg

Alex_Chow · 2010-02-17

Thanks Jorg for the tip!

In the case where Picture is a standard table in the item table, do you recommend just disabling the field? OR deleting it completely?

stryk · 2010-02-17

Well, what we did was this:

1. Create new table "Item Picture" ("Item No." (PK), "Picture")
2. Move all "Pictures" from "Item" to "Item Picture" (using SQL as described in my BLOG, or via NAV CU)
3. Set "Enabled" to FALSE for "Picture" in table "Item"

In those Forms and Reports where "Picture" is used, we changed the code, pointing to the new table.

Hope this helps you a little.
Cheers,
Jörg

Alex_Chow · 2010-02-17

stryk wrote:

3. Set "Enabled" to FALSE for "Picture" in table "Item"

Got it. I just want to make sure setting the enable property to No will cause the select to use the field.

The client does not store pictures on the blog field anyway, they use Record Links.

Alex_Chow · 2010-02-17

Thanks Jorg, the Item List is BLAZING now!

Didn't know a simple BLOG field can cause so much harm.

stryk · 2010-02-17

Cool! 8)
I guess it's the way how BLOBs are stored in the database - not part of the CI. I think that there is at least some pointer to "somewhere" where the BLOB "might" be - even if not used ... and just checking if there's a BLOB or not obviously causes lot of I/O ... :-k

Alex_Chow · 2010-02-17

There were some values in the BLOG field. I had to clear it out manually.

It's uploaded by accident a long time ago.

kriki · 2010-02-19

But be careful with disable BLOB-fields and using webservices : see my blog : http://mibuso.com/blogs/kriki/2010/02/17/am-i-a-detective/

garak · 2010-02-20

be aware if you make only a techn. upgrade to 2009SP. Some C/AL commands are other interpreded then in the older versionsl.
We had the phenomenon with the "Analysis by Dimensions" and here, for example, with the Codeunit 7153 Funktion DimCodeToOption().
The problem was here, that in the older version the first parameter is code now its Text. Now, with tech. upgrade the CASE command checks if uppercase or lowercase (in older version that was not the case). So, u must check also your customized code, if there is some code like this.

regards

Howdy, Stranger!

Quick Links

Categories

List form selecting every field causing slow performance?

Comments