Add execute native syntax to create or replace table, insert and bulk insert syntax

Ideas (en)
,
1

To increase performance, these options would increase high volume processing performance by replacing a loop with execute native by a real bulk insert:

[bulk] insert TABLENAME()
execute native 'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name
from account_move_line ML
LEFT JOIN account_move M ON ML.Move_id = M.Id
where M.state LIKE ''posted''
LIMIT 10000' DATACONTAINER 'Vega'

and

[bulk] insert TABLENAME()
execute native 'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name
from account_move_line ML
LEFT JOIN account_move M ON ML.Move_id = M.Id
where M.state LIKE ''posted''
LIMIT 10000' DATACONTAINER 'Vega'

See also Faster loading from execute native.

2

Would like to add the possibility to create table from an execute native

create or replace table mytable
as
execute native 
  'select ML.Id'
  || ', ML.Debit'
  || ', ML.credit'
  || ', ML.Balance'
  || ', ML.Account_id'
  || ', ML.Payment_id'
  || ', ML.Journal_id'
  || ', ML.Partner_id'
  || ', ML.Move_id'
  || ', ML.Date'
  || ', ML.Invoice_id'
  || ', ML.Quantity'
  || ', ML.Name'
  || ' from   account_move_line ML'
  || ' left join account_move M'
  || ' on ML.Move_id = M.Id'
  || ' where M.state LIKE ''posted'''
  || ' LIMIT 10000' 
datacontainer 'sql'
3

What would be the benefit of a create table with execute native? Isn’t it just possible to put the create inside the execute native?

4

When :

  • syncing data from two different containers that requires some filtering (for example Postgres to SQL Server)
  • the filtering is made client (Invantive) side (left join , max(), Sum() etc…)
  • the tables have millions of record

Then executing without execute native will download all records into Invantive client, make the join, and then the create or replace will create the table in the destination container.

On the other end , execute native will run faster by being executed on server side but cannot be used to create or replace a table.

5

It is possible to use execute native to create tables, for example on PostgreSQL:

begin
  execute native 'create table if not exists y1234( col varchar(60) )' 
  datacontainer 'pg'
  ;
end;
6

The goal is not to create a table inside an execute native

But rather create a table in container1 after execute native a statement in data container2.

Sorry if this wasn’t clear.

Add possibility to create or replace a table from a execute native statement that would allow server side filtering to circumvent client (Invantive) side filtering, generating unmatched performance when for exemple a join is made between million record table tables.

create or replace table mytable@container1 
as
execute native 'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name
from account_move_line ML
left join account_move M ON ML.Move_id = M.Id
where M.state LIKE ''posted''
LIMIT 10000' DATACONTAINER 'container2'

Add possibility to Bulk insert from a execute native statement. That would allow to insert in bulk values and leveraging server-side filtering to circumvent client (Invantive) side filtering when for exemple a join is made between million record table tables, instead of using a loop stmt as described Faster loading from execute native 

Bulk Insert mytable@container1
execute native 'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name
from account_move_line ML
LEFT JOIN account_move M ON ML.Move_id = M.Id
where M.state LIKE ''posted''
LIMIT 10000' DATACONTAINER 'container2'
7
  • Add possibility to create or replace a table from a execute native stmt
    that would allow server side filtering to circumvent client (Invantive) side filtering, generating unmatched performance when for exemple a join is made between million record table tables
create or replace table mytable@container1
as
execute native 
'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name'
|| ' from account_move_line ML'
|| ' LEFT JOIN account_move M'
|| ' ON ML.Move_id = M.Id'
|| ' where M.state LIKE ''posted'''
|| 'LIMIT 10000' 
DATACONTAINER 'container2'
  • Add possibility to Bulk insert from a execute native stmt
    that would allow to insert in bulk values and leveraging server side filtering to circumvent client (Invantive) side filtering when for exemple a join is made between million record table tables, instead of using a loop stmt as described Faster loading from execute native 
bulk insert mytable@container1
execute native
'select ML.Id, ML.Debit, ML.credit, ML.Balance, ML.Account_id, ML.Payment_id, ML.Journal_id, ML.Partner_id, ML.Move_id, ML.Date, ML.Invoice_id, ML.Quantity, ML.Name'
|| ' from account_move_line ML'
|| ' LEFT JOIN account_move M'
|| ' ON ML.Move_id = M.Id'
|| ' where M.state LIKE ''posted'''
|| 'LIMIT 10000' 
DATACONTAINER 'container2'
8

Thank you; it is a cross-datacontainer copy using as much as possible Invantive UniversalSQL-standard features while working around limitations on client-side processing when we understand correctly.

We will include this idea as a candidate for future development.