isempty vs. GET

IBS

hello

Is ISEMPTY faster than GET on sql?

DenSter

They have different purposes, what is it that you want?

IBS

if the record exists or not.

Karenh

I think GET would be faster but you can only use it if you are searching on the values in the primary key.

ISEMPTY is for when the filter values are not all in the primary key.

garak

also if you filter on a PK (like a get) isempty could be faster. But normaly if no Data inBLOB the READS and the Memory Usage should be the same

isempty fired up an sql command like

SELECT TOP 1 NULL from

so it only shows if there is some Rec.

the get fired up

SELECT  * from

Now if u have also a BLOB in your table, the get also calculate the BLOB field

DATALENGTH(BLOBFIELD)

this cost also perfomance, if there are datas into the BLOB.

So an Isempty is better then a get if you only need to know if a rec exist or not. If you need the rec to work with him, use get or a find statement.

Waldo

I remember someone writing something about this here as topic "What impact does my C/AL have on SQL?".

Enjoy!

DenSter

Karenh wrote:

ISEMPTY is for when the filter values are not all in the primary key.

No that is not correct. ISEMPTY checks whether records exist within the current filters and returns TRUE or FALSE. It has nothing to do with the primary key.

IBS

thanks. That's what I thought. Since get can only works with PK, I was comparing isempty if filter were set just on PK fields.

There is a lot of places in Nav where they error out if a record exist and most of the code GET is used.

DenSter

IBS wrote:

There is a lot of places in Nav where they error out if a record exist and most of the code GET is used.

I have not idea what you mean here.

ISEMPTY as opposed to GET is more programming, because you have to set filters on all fields. From a programming standpoint it is quicker to just type a GET statement. Much of the NAV code could theoretically be replaced with ISEMPTY statements, but since most of the code was already there, and it works perfectly fine, they decided not to go and replace all of that.

The actual SQL Query that is generated is almost identical (read Waldo's blog that he links to). The time that it takes to run the actual SQL statement is probably also very close, but since you have to transfer a whole record instead of a single boolean, you can make a case that ISEMPTY is quicker than GET. You'd have to actually measure it though, I would not be surprised that it would depend on the situation too, what type of fields are in the table, how big the record is, if there are BLOB fields, things like that.

Marije_Brummel

It all depends on I/O on your SQL box and network trafic.

I/O

The SQL query optimiser will decide what to do based on I/O costs. if you read values filtered in the primairy key, the data is most likely to come directly from the clustered index when you do a get. Since NAV requests all fields (*), a clustered index read is mandatory.

An ISEMPTY does not requre a clustered index read since it does not need Clustered index data. It only needs some table where all filtered fields are present.

Since SQL (and NAV) add's al PK fields to indexes it should be able to read from any index to aquire the data. This should be the smalles index since the query optimiser calculates the I/O to disk.

It can also read from an index with only the PK fields like the new indexes on table 36, and 37 in 5.0.

GET statements on setuptables do not need to be replaced with ISEMPTY since thy do not have other indexes than the PK.

Network

An ISEMPTY does not require the data (record) to be sent to the client, thus there is less network. With SQL becomming more powerfull (x64) and hardware becoming faster, we (SQL Perform) see networks becoming more and more a bottleneck.

Cache

SQL Servers have more and more cache. If your data is in cache, you will not notice a performance gain at all. The difference might be 1nanosecond compared to 2 nanoseconds or less



Conclusion

Our experience is that an ISEMPTY statemens has the most effect on queries between 2 and 6 fields not filtered on the PK but on more or less selective fields

Good luck.