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

Questions
, , ,
1

Good morning
I have a question, something is unclear how query SQL behaves:

select max(id) 
from t1@sql

with 588K records in the table, Query Tool takes 22 seconds to print the result:

Execute query with max

The query:

select id 
from   t1@sql 
order 
by     id desc 
limit  1

with 588K records in the table, Query Tool takes 23 seconds to print the result.

Ordered retrieval
Ordered retrieval1048Ă—480 62.8 KB

It seems that both statement are not passing the MAX nor the ORDER BY to the SQL Server data container on @sql, instead loading all the records and doing the operation Query Tool side, not SQL server side.

Am I wrong?

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