Select converts WHERE '3687712' to LIKE '[3³]6877[1¹][2²]%'

rthsw

Hi, i'Ve got a strange behavior of my SQL2000 Server with german Navision 4 SP3 (logical 3.70).

on 2 "big" tables (687.208 / 727.736records) (110 Sales Shipment Header & 112 Sales Invoice Header) with code(20) as primary key, i've got since some weeks a strange rise of answertime. The Steps:

open the posted Sale shipment header card
press CTRL + END (for the last record).
Note the Document No. (3687712 in my example)
Press Pict. up once
Press CTRL+F for Find on the DocNo. Field
insert the DocNo noted before
Navision/SQL needs some time (up to 2 Minutes) to display the correct record.

With the Profiler/Clientmonitor i could get the code, Navision send's to SQL.

Navi sends
SELECT * FROM "Landefeld Druckluft$Sales Shipment Header" WHERE (("No_" LIKE '[3³]6877[1¹][2²]%')) ORDER BY "No_" OPTION (FAST 29)

In the query analyzer, i can reproduce:
a) the SQL reacts, and returnsa the correct record, but needs a lot of time
b) changing then bold string to 3687712 results the same record, but needs only a tick to answer.

The Selectstring can be seen in the Profiler also in the Clientmonitor, so it seems to be a miss-behavement of Navision. But why, and how to correct? Some Ideas?

kine

Disable the "Match Case" option on the Find dialog. It seems that from some reason, the 3, 1 and 2 are taken as Uppercase characters with lowercase variant. Check the SQL Collation you are using.

rthsw

The Field is a code-field. Navision disables the Option Case-sensitive in the Search-Dialog on Codefields. so it isn't checked and it is not changeable.

kine

Ok, than, which language you are using in the client? Which Collation you are using on the SQL DB?

kriki

Why not using a filter instead of using the find-function. In general filtering is better for performance than using the find-function.


[Topic moved from Navision forum to SQL Performance forum]

rthsw

instead of filtering, and it is more natural. It seems as the feature "Accent-sensitive" is the problem. I'm just converting a Testdatabase to "not Accent-sensitive" to test the speed.

it ssems as this feature just add's to every letter which as alternative writings all of these (hard coded).

an E is selected as EÉÈ and so on.

kine

This is driven by the fin.stx file. And this depends on the language you are using in the client. It is why I asked about the language...

stryk

Usually this is fired when searching (Ctrl+F) using the "Any Part Of Field" feature (DEU: "Teil des Feldinhaltes") - could this be the case?

kriki

stryk wrote:

Usually this is fired when searching (Ctrl+F) using the "Any Part Of Field" feature (DEU: "Teil des Feldinhaltes") - could this be the case?

I tested that because I thought the same thing. I never use part of field but beginning of field and get the same LIKE-command.
I think he should search on "Whole field" to avoid the problem.

rthsw

Hi, thanks for your Replies. It is on all 3 Types (whole,Start,part). These Options just switch the % (replacement of *) at the beginning or the end of the like-string.

Disabeling the Accent will rewrite most of the database, so i couldn't complete the task till now, but will continue testing.

rthsw

AS i declared, i've tried to dissable the "use accent" feature on Alter Database/Options.

After some Hours of working, navision tells me: "the sorting ? will not be supported". After confirming, the sql recorvers the transaction over a lot of hours, and the option is still active.

We removed allready all german signs (ÄÖÜ) out of the items, and use only Numbers (or US-Chars) for the Reference-code-fields. Is there a way throgh the back, to disable this option?

Can i dissable this bevaement (select * ... where '2E' instead of select * ... like '[2²][EÉÈ]') for special fields? I need only 3 Tables w/o this "feature".

rthsw

If you generate a new Database with Standard Options, restoring the FInbackup, starting the clientmonitor (all Options active except "use placeholders"), and searching the customer 01121212, you find under Parameter 30 the statement

SELECT *,DATALENGTH("Picture") FROM "CRONUS AG$Customer" WHERE (("No_" LIKE '%0[1¹][1¹][2²][1¹][2²][1¹][2²]%')) ORDER BY "No_" OPTION (FAST 10).

If disabeling the "accent sensitive", you get the SAME Statement. So Access-sensitive isn't the solution.

by the way: i am using "windows collation, Sorting Afrikaans[...]German[...], no Binary, No Case-sensitive, Validating Code-page YES.