Raul


Wow, and there was me thinking we should use SQL because its faster

and before you post - yes it is, generally, for set based operation rather than navigational.

Your No 2 result is roughly comparable with what Evgeny showed he got with DBSys on a single record.

Actually the more I read and think about this thread I wonder what Evgeny is trying to achieve. Unless he's laying the foundation for a more complex system then from my understanding of the posts a couple of extra columns in the base tables would work well.

Roy Lambert

On 4/12/2019 2:21 AM, Roy Lambert wrote:
> Actually the more I read and think about this thread I wonder what Evgeny is trying to achieve. Unless he's laying the foundation for a more complex system then from my understanding of the posts a couple of extra columns in the base tables would work well.

Yep - sample Evgeny showed and what my test rig did could be done with
single a SQL execute statement which would be comparable to navigation
one in speed.



Raul

Mans, thanks a lot for your attention!
Yes, purpose of my code is more complex subsystem - data synchronization (including another db provider as mssql), its a reason why tried to use pure sql calls and thin additional table.
So there is no way to have fast short update calls in dbisam? I plan to add needed data to host table (for now see no alternative), but it will cost when i'll select this data (host data tables is relatively big by both fields set and records number).

Again, thank you all!

regards, Evgeny

Evgeny,

I'm coming in a little late here, sorry.

Can you please post a small project that reproduces the slowdown that you're seeing ?  Just extract out the relevant ExecSQL call that is taking so long, along with the relevant tables, and I'll take a look.

Also, have you looked at the DBISAM mirroring server included with DBISAM (in the \servers\dbsrvr\mirror subdirectory under the main installation directory).  It may save you some time with writing code for replicating DBISAM records to other locations/servers, especially because it works with transactions, etc.  The only thing you'll need to change is the part that applies the replicated inserts, updates, and deletes to the external, non-DBISAM server.

We use the mirroring server for replicating data here between Elevate Software (Buffalo) and our web server in Chicago.

Tim Young
Elevate Software
www.elevatesoft.com

On 4/12/2019 11:42 AM, ds wrote:
> So there is no way to have fast short update calls in dbisam? I plan to add needed data to host table (for now see no alternative), but it will cost when i'll select this data (host data tables is relatively big by both fields set and records number).

They are plenty fast - in my test rig 1000 SQL updates took less than 1
second and in general i've found dbisam to be very fast.

Sample you showed can be replaced by a single SQL statement which would
be extremely fast compared to your design of "one sql call per row".

Raul

Tim Young [Elevate Software] wrote:

> Can you please post a small project that reproduces the slowdown that you're seeing ?  Just extract out the relevant ExecSQL call that is taking so long, along with the relevant tables, and I'll take a look.

I put this code in my initial post; tables can can have any structure (main goal is to have stuff working on any given table)

> Also, have you looked at the DBISAM mirroring server included with DBISAM (in the \servers\dbsrvr\mirror subdirectory under the main installation directory).  It may save you some time with writing code for replicating DBISAM records to other locations/servers, especially because it works with transactions, etc.  The only thing you'll need to change is the part that applies the replicated inserts, updates, and deletes to the external, non-DBISAM server.

No, because we are not using c/s ue to architecture (databases move between workplaces)

regards, Evgeny

Raul wrote:

> They are plenty fast - in my test rig 1000 SQL updates took less than 1
second and in general i've found dbisam to be very fast.

Sample you showed can be replaced by a single SQL statement which would
be extremely fast compared to your design of "one sql call per row".

As i've explained, these sql should be called from triggers, which worked exactly per row...

regards, Evgeny

Evgeny


Have you tried tracing in to the code yet? The only way I can see it taking as long as you are experiencing for a single row is if there's some form of re-entrancy, or for the update to be failing multiple times and circling round the repeat..until

You also need to add code into the except clause rather than just ignoring the error, at least while debugging.

Roy Lambert

Roy Lambert wrote:

> The only way I can see it taking as long as you are experiencing for a single row is if there's some form of re-entrancy, or for the update to be failing multiple times and circling round the repeat..until
No, there is no any re-entrancy. For one loop each syncid processed only once.

regards, Evgeny

Evgeny



Silly question - are you looping through the entire table each time there's an update?

Roy Lambert