sql optimization

NavStudent
NavStudent Member Posts: 399
edited 2007-05-14 in SQL Performance
Hello I've been looking at how navision does findasyoutype on sql. And this is very costly. I was simply searching on name
On SQL Navision runs the following statement.
SELECT  * ,DATALENGTH("Picture") FROM "Test"."dbo"."Pharm$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY 
"No_" OPTION (FAST 10)

This takes 1700 ms and the number does go down as you keep searching for the field but.

Most of the time whe you are searching (Ctrl +F), you are looking for one record. So why isn't Navision client sending 
SELECT  top 100 * ,DATALENGTH("Picture") FROM "Test"."dbo"."Jason$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY 
"No_" OPTION (FAST 10)

The empasis is the Top 100, or even better top 10
This improves performance a lot.
while I was running the test, I was running

DBcC dropcleanbuffers
dbcc freeproccache

To clear the cache so that I would get same results.
Thank you.
my 2 cents

Answers

  • kine
    kine Member Posts: 12,562
    There are more issues with this search. If you are using some charset with accents, the search select can look like: 
      SELECT .... where Name = '%[Mm][AaÄäÁáâĂ㥹][CcĆćÇç][Hh][OoÖöÓóÔôŐő][Vv][AaÄäÁáâĂ㥹]%

    If you enable the Match case option, the select will look like: 
      SELECT ... where Name = '%Machová%'

    And if you are using case-insensitive collation, it doesn't matter, if you select this, because it will find any variant of the string. It means you can search faster if you select the "Match case" when using CI collation and there is one additional gain: in some languages (like Czech) where are special "double character" (like 'ch' in Czech - it is one character in our alphabet) will this option grant you correct searching when using the local collation for sorting...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • NavStudent
    NavStudent Member Posts: 399
    I see.
    It still doesn't anser, why they wouldn't add TOP 100 or TOP 10 in the sql statement.
    my 2 cents
  • Saint-Sage
    Saint-Sage Member Posts: 92
    Because of support for the native database?
    No one loves you like the one who created you...
  • NavStudent
    NavStudent Member Posts: 399
    They are two different clients. fin.exe and finsql.exe.
    Navision has also added finset,findfirst, etc as well.

    What I'm asking when it translates the navisision find dialog into sql statement.
    my 2 cents
  • DenSter
    DenSter Member Posts: 8,307
    Those SQL statements are generated by the executables, you can't change those. Every time MS comes out with new exe's, they make small improvements to the SQL statements.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • NavStudent
    NavStudent Member Posts: 399
    DenSter wrote:
    Those SQL statements are generated by the executables, you can't change those. Every time MS comes out with new exe's, they make small improvements to the SQL statements.

    I know that. I'm asking why Navision finsql.exe calling the top Number, it will save a lot of processing power.
    my 2 cents
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    And you really expect these forum members to answer that?

    Please do a product suggestion via the MS site.
    Marije Brummel | Freelance Business Central Specialist
  • NavStudent
    NavStudent Member Posts: 399
    Mark Brummel wrote:
    And you really expect these forum members to answer that?

    Please do a product suggestion via the MS site.

    Fair enough. A link would where this is would be helpful.

    The reason I was asking it here, to see if I was missing something and the suggestion I had wouldn't be right in certain scenarios.

    Thank you.
    my 2 cents
  • kriki
    kriki Member, Moderator Posts: 9,121
    [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!

  • Nobody
    Nobody Member Posts: 93
    The reason for not "TOP 10" is because the type of ODBC cursor being used.

    When "OPTION (FAST 10)" is used at the end of the statment (within a cursor) SQL creates a FFO or Fast Foward only cursor, it returns the first 10 rows but leaves the cursor open and then essentially fetches the rest. It is not as nice as TOP 10, but NAV would need to change the logic to the whole NDBCS.DLL to change this.

    The reason the statement is slow is not becuase of the "TOP" or cursor type it is because of the "LIKE" in the where clause. LIKE is very expensive. Hence the reason "Find as you Type" is so costly. Not sure how you would redesign that?
  • kriki
    kriki Member, Moderator Posts: 9,121
    Nobody wrote:
    The reason the statement is slow is not becuase of the "TOP" or cursor type it is because of the "LIKE" in the where clause. LIKE is very expensive. Hence the reason "Find as you Type" is so costly. Not sure how you would redesign that?
    Very easy!
    Remove the "Find as you type"-toggle from finsql.exe. :mrgreen:
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Nobody
    Nobody Member Posts: 93
    If only they would do that.. :-)

    There are many customers who flat out refuse to turn it off.

    One way I found to "somewhat" mitigate the effect is compile a list of all the fields the users will use with "find as you type" and create single column non-clustered indexes on them. This is really more to get field statistics than anything else but it does help somewhat.

Categories