My simple application to maintain an 8.2 million record foxpro database is taking too long to update record. Seek times are fast but updates each take at 10 secords.
After 20 record updates the time take can grow to 50 seconds.
In an update only one record is updated. The record is first check to determine if it exists in the DBF. If it does it is updated otherwise the record is inserted. When records are updated the indexes ( CDX) are also updated.
Do you expect the updated to take so long with this size of table.
|
What's your query/update sql? Whether you haven't disabled transaction? Whether you haven't a suitable index expression for your where clause? Whether you are using lockType connection property for Xbase compatible mode?
|
Our Query is as follows :
UPDATE urglocal SET free=false WHERE unique=?;
We have not disabled autoCommit (presuming autocommit is true by default),
we have the following indexes in a .cdx file :
Structural CDX file: C:\VOL6\PINNACLE\URGNET\DB\URGLOCAL.CDX
Index tag: PARTIC Collate: Machine Key: PART+IC+YARDORDER F
or: .NOT.FREE
Index tag: URGID Collate: Machine Key: URGID For: .NOT.FRE
E
Index tag: UNIQUE Collate: Machine Key: UNIQUE For: .NOT.FREE
Index tag: IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12)
For: .NOT.FREE
Index tag: YDVSTOCKNO Collate: Machine Key: URGID+VSTOCKNO For:
.NOT.FREE
Index tag: FREE Collate: Machine Key: FREE
Index tag: DELETED Collate: Machine Key: DELETED()
Index tag: URGID2 Collate: Machine Key: URGID
Index tag: MODPART Collate: Machine Key: MODEL+PART+YEAR+YARDORDER For: .NOT.FREE
we are not currently setting any specific lock type
should we disable autoCommit ?
and/or should we set any specific properties on our Connection ?
Thanks in advance.
|
>Seek times are fast but updates each take at 10 secords.
>After 20 record updates the time take can grow to 50 seconds.
>UPDATE urglocal SET free=false WHERE unique=?;
Please backup your database, then zip and email us a sample with those inex files. I guess that it should be an index update speed issue.
|
How can I take advantage of an index like
"IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12) For: .NOT.FREE " ?
What sort of SQL should I write to make use of this index?
|
>"IDUNIQUE Collate: Machine Key: URGID+STR(UNIQUE,12) For: .NOT.FREE " ?
where URGID+STR(UNIQUE,12)=? and not free
|
>UPDATE urglocal SET free=false WHERE unique=?;
You should use UPDATE urglocal SET free=false WHERE unique=? and not free;
BTW, you're using a very old package. You can find some new feature at here.
|
With the CDX driver and an 8.2 million record database
running the command
update urglocal set free=true where URGID+STR(UNIQUE,12)="az33 1234567890' and NOT FREE;
Took around 200 seconds
update urglocal set free=true where URGID = "az23" and UNIQUE= 1234567890 and NOT FREE;
Takes between 1 second and 2 seconds
Using IDX driver instead gave much better results sometimes 10 ms sometimes ~2 s
We are having trouble getting consistent results but I will update this issue further when I have more results + test code.
|