Contact Search can take up to 30 minutes

ehartel

In the Contact Search Form, I put the following in the Find What field: @regina|@kim (search area: Contact, contact type: Person, Exact Match not checked)

I turned client monitor on, and 30 minutes later it returned the result set. Client Monitor (had every monitoring option turned on) returned nearly 1 million records.

I then put this string in the Find What: @richard|@linder

The result set was returned in seconds and there were only 34,000 records in client monitor.

Obviously, 30 minutes is unacceptable for a business function. If I use Exact Match for Regina Kim, the result set is returned in seconds. However, that eliminates the blurry functionality.

The maintenance plan rebuilds the indices nightly. Is there anyway to improve performance on this?

kriki

First and for all: do you have an index on that field? If not, create one that matches the select that is send to the SQL server. The index must be a covering index containing all fields in the where and the order by.

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

pdj

kriki wrote:

...The index must be a covering index containing all fields in the where and the order by.

A bit off topic (sorry), but are you sure it makes any difference that the OrderBy fields are in the Index or not? I think SQL always does the sorting as the very last thing so it has already done the KeyLookup to get all field values. It would be nice for big datasets that SQL would do the sorting before the KeyLookup - I have just never seen it in an execution plan. I have even tried to re-produce it, but without luck. Can you re-produce it?

kriki

I've read on a lot of blogs that a covering index should always contain the fields of the where and the order by clause and the fields of the select (this last is quite a problem in NAV). I've always done it and it seemed to help.

stryk

Well, as I'm working let's say more "behind the scenes" I have to admit that I don't know this Contact Search feature. But this "Exact Match" features rings a bell:
I guess if "Exact Match" is used the field is compared by EQUAL operation; e.g.
WHERE "Name" = 'Kim'
In such a case the SQL Server has a chance to pick a right index to seek for the result.

I suppose, if "Exact Match" is NOT used, then a LIKE operation is executed, searching for a regular expression using wildcards; e.g.
WHERE "Name" LIKE '%[Kk][Ii][Mm]%'
If wildcards (%) are used in the beginning of an expression, SQL Server could hardly find an entry point into an index - even if the index is potentially OK - so it will start to scan that index from the beginning.
Wildcards at the end of an expression usually mean, that SQL Server "does not know" where to stop scanning, thus it will scan until the end of an index.

Hence, wildcards in the beginning and at the end of an expression will cause the worst case: a full Index Scan, mostly a Clustered Index Scan. The larger the table, the more severe the problem ... (many Reads, high CPU load, long Duration, Blocks, etc.).

To limit the damage it is necessary to have an index that includes all fields of the WHERE caluse, at least (completely covering is hardly possible with "Contact" in NAV).
To solve this issue it is necessary to NOT use wildcards (at least not those in the beginning) ...

Regards,
Jörg