Performance of $insert on rows defined on tables

Mayada Al-Kishtini malkishtini at gmail.com
Mon Jul 1 03:13:10 EDT 2019


Hi Bastiaan,

I agree with you, one would think that there is a difference in the way the
sql is executed in $doWork, but I just confirmed that both approaches are
using the same method for the table class..$insertrow, $updaterow()

--the code in table class method .$doMethod is:

Do $cinst.$history.$makelist($ref.$rownumber,$ref.$status) Returns
vHistoryList
Do $cinst.$includelines(kRowAll)
Do $cinst.$first(kFalse)
If $cinst.$linecount
Switch $cinst.$history.[$cinst.$line].$status
Case kRowUpdated
   Calculate vRow as $cinst     ;; for some reason, we can't directly
update the row. Copying to local variable works.
   Do vRow.$updateRow() Returns #F
   Do $cinst.$assignrow(vRow)
Case kRowInserted
   Calculate vRow as $cinst
   Do vRow.$insertRow() Returns #F
   Do $cinst.$assignrow(vRow)
Case kRowDeleted
   Calculate vRow as $cinst
    Do vRow.$deleteRow() Returns #F
End Switch
 Do $cinst.$next(0,kFalse)
End While
End If
Do $cinst.$savelistwork()
--------

And below is the slow code:
;  @@Do iSlroJobsRow.$updaterow() Returns pass
;  @@Do iSlroJobsRow.$insertRow() Returns pass


Best Regards,
Mayada
On Mon, Jul 1, 2019 at 2:45 AM Bastiaan Olij <
Bastiaan.Olij at instinctsystems.com.au> wrote:

> Hi Mayada,
>
>
> $dowork will do inserts, updates and delete in order. It calls $dodeletes,
> $doupdates and finally $doinserts.
> I wouldn't be surprised if internally these functions rely on $prepare +
> $execute to speed things up.
>
> 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:   Mayada Al-Kishtini <malkishtini at gmail.com>
>  To:   OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>  Sent:   7/1/2019 4:17 PM
>  Subject:   Re: Performance of $insert on rows defined on tables
>
> Hi Andrea,
>
> I have also experienced the slow execution of a rowVar.$insert() and
> rowVar.$update() when calling the commands from a js client. (rowVar is
> define using definefromsqlclass )
> As a workaround, which improves the speed a bit for me, I used a smart
> list
> for doing the insert and the update:
>
> >> iSlroJobsRow is the Row Var and slroList is the List
>
> Do slroList.$copydefinition(iSlroJobsRow)
> Do slroList.$smartlist.$assign(kTrue)
> Do slroList.$merge(iSlroJobsRow)
> Do slroList.$dowork() Returns pass
> Do slroList.$smartlist.$assign(kFalse)
> Do iSlroJobsRow.$assign(slrowList.1)
>
> But I couldn't figure out yet what is causing the preference difference
> between the 2 approaches and I have not tried the approach of using direct
> sql execute commands to perform the insert and update to measure the
> performance.
>
> HTH,
> Mayada
>
> On Fri, Jun 28, 2019 at 8:17 AM Andrea Zen <a.zen at athesiavr.it> wrote:
>
> > 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
> >
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>



More information about the omnisdev-en mailing list