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

rthsw
rthsw Member Posts: 73
edited 2008-06-26 in SQL Performance
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?

Comments

  • kine
    kine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • rthsw
    rthsw Member Posts: 73
    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
    kine Member Posts: 12,562
    Ok, than, which language you are using in the client? Which Collation you are using on the SQL DB?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • kriki
    kriki Member, Moderator Posts: 9,121
    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]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • rthsw
    rthsw Member Posts: 73
    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
    kine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • stryk
    stryk Member Posts: 645
    Usually this is fired when searching (Ctrl+F) using the "Any Part Of Field" feature (DEU: "Teil des Feldinhaltes") - could this be the case?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • rthsw
    rthsw Member Posts: 73
    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
    rthsw Member Posts: 73
    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
    rthsw Member Posts: 73
    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.

Categories