Query Tool MAX() , Order By ... statements speed question with SQL Server

Questions
, , ,
2

You are correct.

Invantive SQL currently does neither forward group functions like max nor order by to the data container. This also holds for the next release of the Invantive SQL engine, which focuses on optimization technologies.

In theory it is possible to forward group functions and the sorting to the data container, but given insufficient market demand we have not created it (yet).

A workaround is to use native SQL such as:

begin
  execute native '...'
end;

or with a loop like:

begin
  for r in
  ( execute native '...'
  )
  loop
  end loop;
end;

Disadvantage is that this approach requires knowledge of the syntax of the data container’s native platform. Advantage is that it allows access to almost all native optimization techniques available, including platform-specific hints.

Performance

For example, the following Invantive SQL query on a large table take 2.7 seconds:

select max(id) 
from   lic@sql

The alterantive using native SQL takes 180 ms:

declare
  l_max_id pls_integer;
begin
  execute native 'select max(id) from lic' 
  into l_max_id 
  datacontainer 'sql'
  ;
  dbms_output.put_line(l_max_id);
end;
1 like