Quickest way to check if a Hana table is empty

[Former Member]

I would like to know the most efficient way to determine if a Hana table or dataset is empty, within the context of an 'IF' statement.

In the example procedure below I am doing a 'count' but I don't think that can be the most efficient way.  I don't know how the SQL 'EXISTS' clause could be used efficiently in an IF clause, or maybe there is some other Hana function?

create procedure blah... AS

BEGIN

  declare numrows integer;

  mydata = select ...;

  select count(*) into numrows from :mydata;

  if numrows > 0 then

      do some stuff

  end if;

END;

[lbreddemann · Accepted solution]

Hi David,

using COUNT(*) surely is the fastest way to get the number of records that are visible to your session.

Keep in mind, that any table is a shared resource and can potentially be read/modified by other sessions, while your code is running.

General statements like "this table is empty" either ask for a more concrete definition, like "for my session the table is empty" or you need to exclusively lock the table before you count to ensure that no other session works on this table.

It's important to keep in mind that tables and code, although tightly coupled by the table structure and the data access methods, it's never possible to assume that no other code/user will ever change the table content. Thus, proper locking needs to be placed by your code where required.

- Lars

[Former Member]

Thanks Krishna,

However, I've done an experiment using

    (a) SELECT COUNT(*)..  and

    (b) .. WHERE EXISTS.. as in your example.

It looks like using COUNT(*) is actually significantly faster, with the test taking 53 milliseconds versus 1489 milliseconds,  on my 100,000 row table.

Please see the code below. Please let me know if the test is flawed.

create column table MYTABLE (

  name varchar(100),

  val integer

  );

create procedure check_empty (size int)  LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS

begin

  declare n int;

  declare total1 int := 0;

  declare total2 int := 0;

  declare time1 timestamp;

  declare time2 timestamp;

  declare time3 timestamp;

  declare not_empty int;

  declare numrows int;

  delete from mytable;

  for n in 1..:size do

    insert into mytable (name, val)

    select to_varchar(n), n from dummy;

  end for;

  select current_timestamp into time1 from dummy;

  for n in 1..100 do

    select count(*) into numrows from MYTABLE;

    if numrows > 0 then

      total1 := total1 + 1;

    end if;

  end for;

  select current_timestamp into time2 from dummy;

  for n in 1..100 do

    SELECT 1 into not_empty FROM DUMMY WHERE EXISTS (SELECT 'X' FROM MYTABLE);

    if :not_empty = 1 then

      total2 := total2 + 1;

    end if;

  end for;

  select current_timestamp into time3 from dummy;

  select nano100_between(time1, time2) / 10000 as Loop1_Milliseconds from dummy;

  select nano100_between(time2, time3) / 10000 as Loop2_Milliseconds  from dummy;

end;

call check_empty (100000);

[former_member182302]

I agree with you David.

Even i tried with one more statement like this:

select record_count into numrows from M_CS_TABLES where table_name = 'MYTABLE';

Regards,

Krishna Tangudu

[former_member182302]

Hi David,

You can use "Exists" like this:

SELECT 1 FROM DUMMY WHERE EXISTS (SELECT 'X' FROM KRISHNA.TESTEMPTY);

If the above statement returns 1 it means the table is not "Empty", else 0 means "Empty".

Regards,

Krishna Tangudu