• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

How to reduce processing time of updating in Microsoft Access using Delphi

I am trying to reduce the time taken to update 10000 records in Microsoft Access. The datatype of the field that is being updated is number.

>>>>>>>>>>>>>>>>>>

with GenericQry do
begin
   sStrSQL := 'Select field1 from TableName '+
              'Order by field2';
   Close;SQL.Clear;
   SQL.Add(sStrSQL);
   Open;
end;

iNumber := 1;
while not GenericQry.Eof do
begin
   GenericQry.Edit;
   GenericQry.FieldByName('field1 ').Value := iNumber;
   GenericQry.Post;
   iNumber := iNumber + 1;
   GenericQry.Next;
end;

<<<<<<<<<<<<<<<<<<<<

Any help is appreciated. Thanks.

0
cwtang
Asked:
cwtang
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
sun4sundayCommented:
'Order by field2' . is a requirment ??? If not take out order by try out the code.

Ordering will make time to the above code.

sun4sunday
0
 
cwtangAuthor Commented:
Yes, ordering is a requirement. Am out of ideas on how to shorten the time. Any suggestions?
0
 
Stuart_JohnsonCommented:
Are you opening that query and then writing back to it?

You should be using one query as your select query, and another query that's you're update query.  However, I'm not sure how that would work because I dont know the table structure you have and what is defined as a unique field.

Using MS Access for anything is going to be really slow.  It's far from an efficent database.  You'd be better off using Interbase/Firebird.  It's a many times faster and doesn't need an expensive licence to use the engine.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JDSkinnerCommented:
Hi
Try this for the select statement

    with GenericQry do
    begin
        Close;
        SQL.Clear;
        { Load SQL Statements with parameters}
        ParamCheck:= false; // stops check for non existant params
        SQL.Add('Select <Field1> from <TableName>');
        SQL.Add('Order by field2');
        Prepared:= true;
        Open;
        Active := true;
    end;

For editing records the fastest way is by invoking a database table procedure, I,ve not done
that with Access as I nearly always use Interbase.
0
 
Stuart_JohnsonCommented:
You only need to Open the table or make it Active.  You don't need to do both as they both result in the same thing.

>For editing records the fastest way is by invoking a database table procedure
This isn't true.  Table based editing is very slow and inefficent.  The quickest method is by using the Update command in a query.  Although to do this you need a unique field so you know what you're updating.

If the OP posts their schema for this table, we can probably optimise it a bit and get it working more efficently.  Even putting an ID field in that's an autoincremented field may solve the issue they're having having.

Cheers.


Stuart.
0
 
JDSkinnerCommented:
Stuart,
Ref your last comment:
"You only need to Open the table or make it Active. You don't need to do both as they both result in the same thing."

Sorry, I had incorrectly edited down the full construct in my Code Templates as it would possibly confuse the issue at hand.

As I understand it, if the query is executed by using the Open method instead of using Active := true,
then this can cause an exception which is not caused by just using Active:= true

If the Open method is run within a try .. except construct and an exception occurs, this can be acted upon from within the except section.

(For example, "TQuery indicates no records found by an ENoResultSet exception". ex D7 Dev Guide)
0
 
Stuart_JohnsonCommented:
I'm not sure why you'd be getting an exception when using Open over Active.  I thought they both ran the same pieces of code.  I never use Active as "set", but I use it as a get property (if Active = then then)

0
 
JDSkinnerCommented:
Stuart
Im not sure either, it was just something that I added to my base template after I saw it mentioned, I have not actually tested it, but it is in the D7 Dev Guide. If you search on ENoResultSet you will get just one result in a tip on the subject.
0
 
cwtangAuthor Commented:
Hi,
   I have set the Active = true but the time request for the processing is still almost the same.
   
   I agree that the quickest way is use the update command. Anyone can provide me the code for the udpate sql statement as same as the function code above. The datatype for field1 is integer and datatype for field2 is text. I have about 50 field(field2,field3.....field51). Everytime i order by the field, i need to update the field1.

0
 
Stuart_JohnsonCommented:
I don't think you can do it any other way than the way you are doing it now.  If you were doing this in Oracle or SQL Server, you could do it as a stored procedure.  But Access is just too limited.

I'd suggest making your FIELD1 an autonumber (duplicates NOT OK), and that way you're guarenteed of being given a unique sequential number each time you add a new record.  If you delete a record, you'll have a hole in your FIELD1 data (1, 2, 4, 5, 7, 8 etc), but that shouldn't be an issue really.

Another thing you can do with Oracle is to use UPDATE TABLE SET FIELD1=rownum - but again, you can't do that with Access because it's so primative.

If you don't want to use the Auto Number, can you use a different database?

Good luck with it though.  I'm fresh out of suggestions!

Stuart.
0
 
JDSkinnerCommented:
Hi
Your speed problem may be concerned with indexes being updated during the process.
If this is the case then to speed this up, make sure that the field(s) that you are updating are not part of any live index.

A good way is to iterate through table using an autoincrement field as the index, which will not be updated during this operation.
      
However I believe that you are using a text field as your index field. Does this field already have an index set up on it, or is it being created just for this operation by the SQL procedure?  Its better if it is a pre existing index.

If it is being created just for this procedure then:
Is the Text field very large? If it is large you may find it possible to create a smaller text field 10 - 12 characters, whatever it takes to make it fairly unique and populate it with the first 10 - 12 characters from field2, which would cut down on index creation time, then use this smaller text field as the index.

If any field being updated is used as an element of an index, turn that index off before starting the procedure and rebuild them after having run the update. Otherwise these indexes will be updated during the processing.

Rebuilding the indexes afterwards is probably quicker than running the process with indexing on.


// DropIndex;
with IBQuery1 do
begin
    Close;
    SQL.Clear;
    // Load SQL
    SQL.Add('ALTER TABLE <TableName>  DROP CONSTRAINT <IndexName>'); // this may work with Access
    ExecSQL;
end;

// Load youd dataset:
with GenericQry do // load dataset
begin
    Close;
    CachedUpdates:= true;
    ParamCheck:= false;
    SQL.Clear;
    // Load SQL Statements}
    SQL.Add('Select Field1 from <Table>');
    SQL.Add('Order by <field2>');
    Prepared:= true;
    Open;

    // run update
    iNumber:= 0;
    try
    first;
    while not eof do
    begin
          Edit;
          FieldByName('field1 ').Value := iNumber;
          Post;
          iNumber := iNumber + 1;
          Next;
    end;
    finally
        ApplyUpdates;
        Commit;
    end;
end;
   
// recreate any required indexes that have been dropped
Close;
SQL.Clear;
// Load SQL Statements
// a guess at Access SQL syntax
SQL.Add('ALTER TABLE <TableName> CREATE INDEX <IndexName> ON <TableName> (Field#, Field#)');
ExecSQL;    
0
 
cwtangAuthor Commented:
Thanks
0
 
JDSkinnerCommented:
Slight error, Commit should read
Transaction1.Commit; // a hazard of writing code late at night

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now