GET vs FINDFIRST

ajayjainajayjain Member Posts: 119
edited 2013-07-30 in SQL Performance
Hi,
It looks like there is big difference when you use FindFirst as compare to GET
FINDFIRST is much better than GET
I have tried on Setup tables like General Ledger Setup
SQL statements are generated like this.

FINDFIRST - Select TOP 1 ...... ORDER BY Primary_Key
GET - Select * ..... WHERE Primary_Key=@P1

Also please advise when you have blob fields, does it cause issue as well
even picture fields are not in use except reports. SQL always excecute like this
SELECT *,DATALENGTH("Picture 1"),DATALENGTH("Picture 2"),DATALENGTH("Picture 3")

and I run my expensive sql query and there is a big difference in total_elapsed_time.
Any view on this??

Thanks
Ajay
Ajay Jain
UK

Comments

  • bbrownbbrown Member Posts: 3,268
    ajayjain wrote:
    Hi,
    It looks like there is big difference when you use FindFirst as compare to GET
    FINDFIRST is much better than GET
    I have tried on Setup tables like General Ledger Setup
    SQL statements are generated like this.

    FINDFIRST - Select TOP 1 ...... ORDER BY Primary_Key
    GET - Select * ..... WHERE Primary_Key=@P1

    ...

    What drives you to this conclusion? Not sure I would agree. Unless you have a case where the NAV primary key is not the SQL clustered index. Then maybe? But with setup tables, as you example, since they only have 1 record...
    There are no bugs - only undocumented features.
  • krikikriki Member, Moderator Posts: 9,096
    I also did some testing (I admit : little testing) and I found that there was no difference.

    Are you sure that your table has good statistics?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • ajayjainajayjain Member Posts: 119
    Thanks kriki & bbrown
    I will do my test again.
    Ajay Jain
    UK
  • macuxpcmacuxpc Member Posts: 16
    ajayjain wrote:
    Hi,
    It looks like there is big difference when you use FindFirst as compare to GET
    FINDFIRST is much better than GET
    I have tried on Setup tables like General Ledger Setup
    SQL statements are generated like this.

    FINDFIRST - Select TOP 1 ...... ORDER BY Primary_Key
    GET - Select * ..... WHERE Primary_Key=@P1

    I have pointed this out to Microsoft and they pretty much shrugged it off. IMHO, by design, a GET returns ONE record, so it should be a SELECT TOP 1.

    It won't make a difference in your occasional InventorySetup.GET. But if you have a GET inside a loop that executed thousands or millions of times, the FINDFIRST will be better for overall SQL performance.
    MacUxPC
    Dynamics-NAV.org
  • bbrownbbrown Member Posts: 3,268
    A FINDFIRST would still need the WHERE clause to filter, so is it really any different than the "Select...where"?
    Select TOP 1 ...... WHERE Primary_Key=@P1 ORDER BY Primary_Key
    
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,304
    I dunno, it actually makes sense because the FINDFIRST makes the expected result explicit. Are you saying that the TOP 1 and the explicit ORDER BY Primary_Key does not make the query more efficient?
  • bbrownbbrown Member Posts: 3,268
    My point is that the statement "Select TOP 1 ...... ORDER BY Primary_Key" is only the same as a GET if all you ever want it the first record in the table. But if you want to return a different record, than you need the filter. So at that point is the statement any different?
    There are no bugs - only undocumented features.
  • DenSterDenSter Member Posts: 8,304
    Of course the WHERE clause would always be the same primary key filter on both ends, but that was not the point. I the whole point of the OP was that it might be faster to use FINDFIRST instead of GET, not because of a difference in filter but because of the added TOP 1 and the explicit ORDER BY clause.

    It really doesn't sound illogical to me at all, although I'd have to test it on a larger scale. I've definitely had weird performance issues with GET queries on single record tables.
  • bbrownbbrown Member Posts: 3,268
    I see you point. But it now makes me wonder how GET behaves when you have a multi-field primary key and don't provide all the values. In this situation, NAV returns the first record that satisfies the values you do provide. Is a "TOP 1" being included then?

    Another of those "to be tested when I find some time" things.
    There are no bugs - only undocumented features.
  • KishormKishorm Member Posts: 921
    Where you do a GET but do not supply all fields of the primary key, NAV will include all primary key fields in the WHERE clause filtering on default values, e.g. 0 for an Integer field, '' for a Code field, FALSE for a Boolean field etc...
  • krikikriki Member, Moderator Posts: 9,096
    macuxpc wrote:
    It won't make a difference in your occasional InventorySetup.GET. But if you have a GET inside a loop that executed thousands or millions of times, the FINDFIRST will be better for overall SQL performance.
    1) Probably it won't even get to SQL Server, but the client (or service tier) would take if from its buffer.
    2) Doing a InventorySetup.GET in a loop should be avoided by the C/AL programmer in the first place! Even it the data comes from the buffer, it takes unnecessary CPU-cycles to run the code.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Sign In or Register to comment.