GET vs FINDFIRST

ajayjain

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

bbrown

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...

kriki

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?

ajayjain

Thanks kriki & bbrown
I will do my test again.

macuxpc

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.

bbrown

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

DenSter

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?

bbrown

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?

DenSter

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.

bbrown

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.

Kishorm

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...

kriki

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.