Better database engine

[clip4ever]

We have a Clipper 5.2e DOS multi user LAN application running on Win 95 (XP soon)workstation connected to database on Win 2003 server. Paripherals are Scanner, Lazer Printer and label printer on COM port. Dealing with COM port is via a C program. most used function is: Scan a barcode, validate, save and print a label.

We are experiencing delays when the user scans to process an item barcode. Means it works fine for a while (1-2 seconds per item) then it takes 10, 20, 30 seconds.

Speed goes back to normal 2 seconds after purge and re-index of the database. DB is 8 MgBytes and 50000 records.

We started to wonder if the Clipper database engine is too small for such a task.

Is this true? Do we need to re-develop the application with a new technology? or there is a solution or a better database engine ?

Please help

clip4ever

 

[JCreamerII]

Clip4ever,

Also if you are using the SET RELATION TO with or without the SET FILTER TO commands that can really slow down a process. If the process continues to slow down incrementally as you continue to scan additional items then it sounds to me like the "CODE". It it just gets slow then it may be the index issue involved as well. But just an index issue generally won't make the process slower & slower everytime you use it. So I would concur with Jim that it sounds like more than just a index issue.

Jim C

 

[clip4ever]

Gents,
There is no SET FILTER, nor SET RELATION.
By scanning a barcode, the code writes the previously scanned pending barcode + info to one table, then reads the next barcode, performs validation (reads) on 2 or 3 tables, prints a label, the waits for the next scan. Table is normally 70000-100000 records, 7-12 MGbytes. And, there is no locking of any tables or records, other than the one being created.
Speed is re-gained after a Re-index, to deteriorate in the days after.

Appreciate your attention and effort.

clip4ever

 

[opticalman]

clip4ever

what is the index code ( what values is it indexed on ) ?

Where is this "previously scanned" info held while waitng to be written?

I am still puzzled by the increase speed after PURGE.

Here is a possible suspect. You may have a LOCATE command insted of a SEEK. As the old records build up, the locate has to scroll through the old records before finding the new. After a purge of old records, the LOCATE has a fewer number of records before finding the new.

Jim Rumbaugh

 

[clip4ever]

Here's the Index expressions/code:
----------------------------------------------------
DO OPEN_FILE WITH "ITMSCAN","ITEM SCAN"
DO INDEX_FILE WITH "rcp_id + STR(seq_no,5,0)", "ITMSCAN"
DO INDEX_FILE WITH "pin + status", "ITMPIN"
DO INDEX_FILE WITH "if(preadvised,replicate(chr(255),40),rcp_id+STR(seq_no,5,0))", "INEXCSS"

PROCEDURE OPEN_FILE
PARAMETERS DBfile_nme, file_desc

PRIVATE row, file_nme, old_reccnt, new_reccnt
*-- Close the previous DBF --*
SELECT 1
USE
row = ROW()
SET COLOR TO W+/B
@ row,0 SAY M->file_desc
SET COLOR TO W/B
@ row,24 SAY M->DBfile_nme
file_nme = pth_nme + DBfile_nme
*-- Open the DBF and pack it --*
SELECT 1
USE (file_nme) EXCLUSIVE NEW
old_reccnt = RECCOUNT()
SET COLOR TO W+/B
@ row,33 SAY STR(old_reccnt,7,0)
SET COLOR TO W/B
@ row,41 SAY "rec" + IIF(old_reccnt=1,"","s")
PACK
USE
*-- Close and re-open DBF file --*
SELECT 1
USE (file_nme) NEW
new_reccnt = RECCOUNT()
IF new_reccnt <> old_reccnt
SET COLOR TO W/B
@ row,46 SAY "-->"
SET COLOR TO W+/B
@ row,50 SAY STR(new_reccnt,7,0)
SET COLOUR TO G/B
prcnt = 100 - INT(((new_reccnt*100)/old_reccnt)+0.5)
DO CASE
CASE prcnt <= 1
prcnt = 1
CASE prcnt >= 99
prcnt = 99
ENDCASE
@ row,58 SAY "("+STR(prcnt,2,0)+"%)"
ENDIF
RETURN

PROCEDURE INDEX_FILE
PARAMETERS key, ntx_nme
PRIVATE row, file_nme
row = ROW()
SET COLOR TO W/B
@ row,64 SAY "- "+ntx_nme
file_nme = pth_nme + ntx_nme
INDEX ON &key TO (file_nme)
CLOSE INDEXES
RETURN

-------------------------------------------

File DESC. is:

RCP_ID Character 35
Seq_No Numeric 5
PIN Character 35
Orgn_PIN Character 35
Status Character 1
PreAdvised Logical 1
Arrvl_Wstn Character 8
Arrvl_Usr Character 8
Arrvl_Dt Date 8
Arrvl_Tm Character 4
Observ_Cd Character 12
RtrnRsn_Cd Character 2
Dest_Cntry Character 2
Write_Ok Logical 1
Extract_Ok Logical 1
--------------------------------------------------

Scan info are in memory variables until save is requested.

I do not use LOCATE in online systems.

AND... I'm puzzled toooooo

clip4ever

 

[opticalman]

clip4ever

The code and indexes look OK to me.

I have hacked out time problems by placing "time flags" in the code and then checking how much time it takes to get from one part of the code to another. You may want to do this to find out what part is taking so long. Then attack the slow part.

Jim Rumbaugh

 

[clip4ever]

Thanks again. I'll find out what are my chances in doing this in Production.

clip4ever

 

[GriffMG]

Clip,

I have one further suggestion, the index key on inexcss might be 'unstable'

DO INDEX_FILE WITH "if(preadvised,replicate(chr(255),40),rcp_id+STR(seq_no,5,0))", "INEXCSS"

I would try replacing the key with a UDF, such as this:

DO INDEX_FILE WITH "PreAdvisedKey()", "INEXCSS"

Function PreAdvisedKey
   Private mstring
   mstring = replicate(chr(255),40)
   if !preadvised
      mstring = rcp_id+STR(seq_no,5,0)
   endif
return(mstring)

I might also change the chr(255) to some other 'unlikely' key, 255 might not be helping.

One other thing, sometimes Clipper needs a little help with the indexes, try appending a couple of spaces to the end of the key (no good reason, just a trick I have used in the past).

Good luck

Regards

Griff
Keep [Smile]ing

 

[clip4ever]

Interresting...

I'll try to get approval...

Thanks

 

[clip4ever]

Griff,

This code does not work because Clipper INDEX ON needs and expression to evaluate not an index value.

Regards

clip4ever

 

[GriffMG]

You can certainly index on a UDF... do it all the time.

You need to ensure the UDF is visible to both the 'indexer' and the module using it though!

The code works fine.

Regards

Griff
Keep [Smile]ing

 

[clip4ever]

Griff,

How do I do that. I'm including the function in the same re-index program. Do I need to include it somewhere else ? or define it ?

Clipper does not recognize other than clipper commands and database fields in an index expression.

Clip4ever

 

[GriffMG]

Hi,

Clipper is a wonderfull tool and does allow you to index on a UDF, as well as the built in things and what not as you say.

To do this, the UDF must be visible to Clipper at the time the index is created (in your re-index program) and also at run-time (in your application). If it isn't in both Clipper will not be able to create or update it properly - although seeks would still work, as the resultant string is used, not the expression that created it.

I created this code a few moments ago and tested it:

use myFile
index on Test() to myFile

function Test
  mstring = Left(myField,1)+Right(myField,1)
return(mstring)

Now so long as I have the same Test() function defined in any app that wanted to update myFile, I'd be laughing!

Obviously, DBU.EXE will not be able to update the table though... so watch out





Regards

Griff
Keep [Smile]ing

 

[clip4ever]

Griff,

Ok, I know my mistake now. I had to include this UDF in the index program and in a library used by many other online and batch programs that are accessing the same db but compiled in different EXEs. I replace the CHR(255) with "z" and added 2 spaces at the end of this particular index only. It worked.

Do you really think we need 2 add 2 spaces to all indexes ?

clip4ever

 

[GriffMG]

The two spaces trick is 'magic fairy dust'. It has to do with 'boundary checking' which is poor in some versions of Clipper - sometimes an index key which is likely to be duplicated a large number of times (although that's not always necessary) causes a 'glitch' to appear in the binary tree system that the index files use - and it can cause odd results - each branch is probably expected to contain a maximum number of elements and when the key is duplicated it maybe contains an extra one... I am not sure.

If your indexes are working, don't add the two spaces. Sometimes one space does it, sometimes three - it seems to be a bit random.

One thing though, once I've applied this oddball technique to an index I have never had to change the size again - not in the last twenty years, not even once.

Good luck.

Regards

Griff
Keep [Smile]ing

 

[hag99]

Try issueing a Pack command after deleting records and not using the reindex command. See what that does.

 

[clip4ever]

Thanks.
But pack cannot be used in on-line applications because it requires exclusive access.

clip4ever

 

[clip4ever]

All,
As a feedback on my experience with this problem, we implemented CDX index and the response Time improved dramatically in on-line and batch programs. However, we had some side effects that we are still working on, like a highly used batch server program crashes often. By putting some delays we reduced the crashing but it is still a problem.

Thanks all for your help. This CDX upgrade renewed a diminishing Client confidence in this Clipper application.

clip4ever