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

rthsw · 2008-06-23

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 · 2008-06-23

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 · 2008-06-23

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 · 2008-06-23

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

kriki · 2008-06-23

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 · 2008-06-23

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 · 2008-06-23

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 · 2008-06-23

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 · 2008-06-23

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 · 2008-06-24

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 · 2008-06-26

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 · 2008-06-26

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.

Howdy, Stranger!

Quick Links

Categories

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

Comments