We are currently upgrading our objects to 5.0. During our testing we found major performance problems in any of our big tables (500,000 or more records). Turns out there is a “fix” in 5.0 SPI that basically removes any values in the SQLIndex property for 72 tables. The Changes in NAV 5.0 SPI word document states:
“SQL index removed on 72 tables to avoid extra overhead on the server central processing unit for sorting”
I can’t find a single table that uses the SQLIndex property in 5.0 SPI. In the past (4.0 SPIII) the SQLIndex property has been an important tool for me to tune our database but now I am unsure if it helps or is making things worse. Index usage does not seem to be behaviing the same as it does in 4.0 SPIII. It seems (like in CSIDE) that if we don’t change our sort on a form before filtering we have poor performance. In 4.0 SPIII I never had to change a sort before filtering since SQL would choose the correct index for me.
I forgot to mention that. I have always had index hinting disabled in all versions. Interesting that you brought this up becasue the system behaves like index hinting were on. Besides having IndexHint=No in the config table I have confirmed there is no hinting going on through client monitor. Something else seems to be going on.
I hven’t had a chance to look at SP1 yet, but it would be a shock that they would just clean it out completely. I would probably put the regular 5.0 SQL indexes right back in.
That probably depends on which tables, and which indexes. It’s not an “all or nothing” proposition, there’s usually a reason why a certain index is not performing well. On the other hand, it might be that they’ve changed the way NAV uses SQL Server in SP1, and we need to review how that works.