Performance of $insert on rows defined on tables

[Bastiaan Olij]

Hey Andrea,


Keep in mind that if you're calling $insert for each row that means a new SQL statement is created that then needs to be compiled. 


What I would try is to create a method in your base class that is similar to the following (please forgive typos as I'm doing this from memory):


tBase.$insert_all
--
calculate lvSQL as con('insert into ',$cinst.$servertablename,' ',$cinst.$insertnames('$cinst'))
if $cinst.$statementobject.$prepare(lvSQL)
  for $cinst.$line from 1 to $cinst.$linecount
    Do $cinst.$statementobject.$execute()
  end for
else
  do $cinst.$sqlerror(kTableInsertError, $cinst.$statementobject.$errorcode, $cinst.$statementobject.$errortext)
end if
--


This will compile the insert query once using bind variables, and then execute the SQL query for each line in the list. As you're looping through the list the bind variables ensure you're sending the actual row data on the execute. 


In the end though the thing to realise with processes like these is that you're still sending each row one at a time so if you're server is distant you're still dealing with a lot of lag time. This approach really only is fast on a LAN.
What we've started doing with Postgres (I don't know if this is possible with SQL Server) is converting our list to a JSON and calling a stored function with that JSON data (to be more exact, we pack the entire invoice into a single JSON). 
That packs all data into a single data set send over the wire as a unit, and then we handle the process of creating the invoice fully on the server. The performance difference here is staggering especially over a slow internet connection to a hosted server. 


Our end goal here is to not do this through SQL at all but send the JSON to a middle tier and have it do all the work on the database, solves a lot of security issues and the code becomes a lot cleaner, but as a stepping stone its serving us well.

Kindest Regards,


Bastiaan Olij
Head of development - Instinct Systems: The JobBag People
Ground Floor, 48 Chandos Street
St Leonards NSW 2065
Australia


Phone: +61 2 8115 8000
Direct: +61 2 8115 8003
Mobile: +61 4 321 44833
bastiaan.olij at instinctsystems.com.au
http://www.jobbag.com



 From:   Andrea Zen <a.zen at athesiavr.it> 
 To:   OmnisDev List - English <omnisdev-en at lists.omnis-dev.com> 
 Sent:   6/28/2019 10:17 PM 
 Subject:   Performance of $insert on rows defined on tables 

Hi all, 
I have several row variables, each one defined on a table through "$definefromsqlclass"; each table class relates to a schema class that maps a physical table on database (SQL Server). 
All these tables have a generic table as superclass; they all inherit "$insert" from this superclass, in which we do some stuff, than "Do $cinst.$default.$insert", then some other stuff. 
I've temporarily commented all pre and post stuff, so the only instruction remaining is "Do $cinst.$default.$insert". 
I've also temporarily removed all indexes from physical tables; by the way, all of them have the same total number of records. 
These rows are involved in an invoice insert routine and I'm monitoring its performances: the routine does 1 insert for each table. 
Well, using Omnis performance collector, I see all the calls to $insert: worst one takes 100 ms, best one 5, average 22 ms. 
>From SQL Server Profiler, I see that all inserts performed by SQL Server are almost momentary; it seems it takes many ms, in worst cases, from the moment Omnis does the call "Do $cinst.$default.$insert", to the moment the query gets to SQL Server. 
Any idea why? It seems something that depends on the table being inserted, since code and class hierarchy is the same for all of them...but I can't get what. 
I've already tried to compose the SQL insert instruction manually and prepare/execute it directly, instead of using "Do $cinst.$default.$insert": no improvement... 
 
Andrea Zen 
_____________________________________________________________ 
Manage your list subscriptions at http://lists.omnis-dev.com 
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com