SP1 List form performance

tedcjohnston

Since upgrading from 4.0 SP1 to 2009 SP1, my customer list form is performing HORRIBLY. We are a retail company so customer lookup performance is critical. The lookup form is sorted on the Searchname key.

We use SQL2005, the classic client. Not sure if this is part of the problem but we use a SQL Collation- Binary sort order, code page 437 (30).

If I change the primary key in the customer table from No. to Searchname,No., the performance is excellent. However, I would rather not change the primary key of the table as, while I am no SQL expert, I realize this would risk allowing customers with duplicate No. fields as long as the Searchname was different.

I need to keep the customer list sorted by the searchname so that customers with similar names are presented to the user together (otherwise we have a worse duplication problem then we have already).

If the suggestion is to change the collation (I've wanted to for years), I need a collation that treats the dash '-' character as a real character. (We have an "intelligent" item number with the first 2 - 3 characters representing the vendor, then a dash, then a string that identifies the item. Default NAV collation ignores the dash so SL-A124 sorts with SLA-123. We need all SL- to sort separately than SLA-.)

Thank you in advance for your help with this.

Ted

ara3n

Hello Ted

Change the Clustered Index to Search Name, No.

Design the table, Click On View->Keys. SHow column and select Clustered field. Check mark the Search Name, No. Key and you should be set.

tedcjohnston

Thanks Rashed,

Tried that and immediately processes stated failing. Would need to go through the code and change each Customer.GET("No.") type call to either a SETRANGE("No.","Something") or to a GET("Search Name","No.").

I made the mistake of changing the first key to the Search Name,No. As the first key is the primary key to NAV (correct?) this caused the GET statements to fail.

By just changing the Search Name key to clustered, I did not get the errors and the speed is there.

Again, thanks Rashed.

Would be interested to know why the search performance was so bad when the view was sorted on a key of Search Name if anyone knows.

ara3n

Clustered index is the index where the data is stored. When using secondary indexes SQL will need to do a lookup to clustered index to find all the rest of the data.
My guess if you use sql profiler you'll notice that sql probably is using clustered index instead of the key you have provided.

kriki

[Topic moved from 'NAV 2009' forum to 'SQL Performance' forum]