Controlling locking in complex procedures

ODBC Connectivity, ELFs , Windows API etc.

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Controlling locking in complex procedures

Postby Graham Smith » Sun Nov 27, 2011 1:31 pm

A number of years ago, I rewrote a DFD app in DFW for a client. There are a number of procedures in the app that follow a similar pattern wherein a group of records is selected and exported and then marked as having been completed. Unfortunately, the file being exported from is about 250,000 records and is central to the app so that everyone has it open all most all of the time. Needless to say, the export can be infuriatingly slow.

One of the first things I did to speed this up was to write the records to a holder file and then export them from there. That helped for a while but not enough. I finally realized that the real slow down was coming from the fact that I needed to mark the records as having been exported and that the modify records might be the slow point. As an experiment, I had them run a version that did not modify records, it only wrote to the holder file - it ran much faster.

So, I appended on a separate step. After the records were written to the holder file, I made a pass through the holder file and modified the records in the master file - bada bing, it ran quite fast.

All in all, from the original version, I have decreased the run time by at least a factor of 10 - that is, it runs about 10 times faster by breaking the procedure down into a series of steps than by trying to do everything all in one procedure.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 

Re: Controlling locking in complex procedures

Postby Pete Tabord » Sun Nov 27, 2011 5:36 pm

Graham, in DFW exports (both types) still set shared locks even though there was no modify.

I chnaged this in Ffenics but I'm not sure when as (unusually for me) I've failed to date the comments. But it would have been about the time we introduced the 'use shared lock in list records' option 'cos I think that's when I discovered it was still setting them on exports.
Peter J. Tabord
Head of Development
Database Software Ltd.
ptabord@ffenics.com
Pete Tabord
 
Posts: 1881
Joined: Fri Sep 07, 2007 12:48 pm
Location: Caernarfon, Gwynedd, UK
Has thanked: 0 time
Been thanked: 3 times
 
 

Return to Advanced

Who is online

Users browsing this forum: No registered users and 1 guest

cron