INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Better database engine

Better database engine

(OP)
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

RE: Better database engine

clip4ever

My first OPINION is that you have a small database. Clipper has historically handled much larger databases easily. I doubt if your problem is a weakness of CLipper.

Do you have the code and ability to change it? I am curious as to why a purge and re-index is needed.

Jim Rumbaugh

RE: Better database engine

(OP)
Thanks for responding, and this my openion as well. I have the code and I can modify any of it. Purge purges old records to reduce size. Re-index is a regular weekly task we perform because we suspect regular corruption.

clip4ever

RE: Better database engine

clip4ever,

I would echo Jim's comments, that the size isn't an issue for the underlying dbf, though it may be for the index.  I recreate indexes nightly for most of the dbf that are connected to my app.  Clipper DBFNTX, as an index schema, is notorious for getting corrupt.  

The delay that you mentioned is probably related to the code being executed and not the DBF itself.  I have millions of records and hundreds of meg's of data that I work with on a minute by minute basis.  What you are doing is what Clipper was designed to do, and still does as good a job as anything else.  Also making an assumption that the network isn't a culprit in this issue.  What is the "PURGE" doing?    

Are you using DBFNTX as the index schema?  8 meg's and 50,000 records isn't that big of a deal for Clipper to handle.  Has this been going on forever, or did it just start recently?  

Going to the next level with a new program & database is fine but that may not be necessary if this is just a badly written routine if that is the cause.  Of course there are better database engines but again with the size of the data you are working with it shouldn't be an issue.

Jim C.




RE: Better database engine

(OP)
Thanks for responding.
Purge deletes recorsd older than a month, and followed by a re-index, once a week. Actually the size after we added another product, is now doubled(15 mg and 100000 records).
I understand that you re-index avery night? do you recommand this?

If you think it's the code, would it help if you have a look at the main scan program ?   

Thanks

RE: Better database engine

clip4ever,

Yes, I would, and I would also reindex everytime you added a large volume of records.  Should take a minute or two, and if you still have the issue with the delay, it would point to the source code as the problem.   A seek to a record should be instantaneous, the one to two seconds mentioned seem like a correct response time.

Thanks,

Jim C.

RE: Better database engine

Hi, Clip4ever

I agree with the Jims, this does not sound like a large database at all. I have an application running database files over 500 meg in size with 2.2 million records and multiple indexes. I rebuild indexes (NOT reindex) weekly.

How many users are concurrently accessing the database?
Does the application lock the database or multiple records?
Do these slowdowns occur with just a single user active?

If you have access to the server, you can check the open files on the share and also see how many locks are outstanding. If you see the number of locks increasing as time goes on then maybe the app isn't  unlocking in a timely fashion.

You also mention index corruption. DbfNtx is really bad for that, but normally it should only happen rarely, such as due to workstation crashes or people closing active windows or network problems.

Has the server been patched to disable opportunistic locking? That also can cause index corruption, and once an ntx is compromised results can be unpredictable. Hwvr I am not positive that is an issue with Win 2003 server; it certainly is with other Win versions though not with Novell which is what I normally use.

Jock

RE: Better database engine

(OP)
Thanks again for taking the Time to help and share the knowledge.

Are you aware of any innovation or a new database engine or library (anything) to avoid re-indexing?

We are open to suggestions and willing to spend to fix this problem in order to avoid milions on other options.

Please advise

Clip4ever

RE: Better database engine

(OP)
Thanks Jock for the input. Yes we have several concurrent users and we lock 1 record by user not more, and NO it happens apparently with many users actives.

clip4ever

RE: Better database engine

(OP)
If dbfntx is bad and buggy, are you aware of another method ?

RE: Better database engine

Hi, Clip4ever

The DBFCDX driver has a much better reputation for reliability and also results in fewer files since all indexes are in one .cdx file which means lower overhead.

In terms of newer technology, the Advantage Database Engine is widely used with Clipper.

Jock

RE: Better database engine

(OP)
I came across CDX before when I upgraded to 5.2e but declined it because it required extensive changes to my app. I will have to reconsider it again since you believe it will help.

I will also inquire on the Advantage database and I welcome any tips lessons learned about it in order to try to sell it to the management.


Great response. I'm grateful.

clip4ever

RE: Better database engine

Clip4Ever-

Are you packing the db after deleting large amounts of records, deleting the existing indices and indexing from scratch, or what?

David

RE: Better database engine

(OP)
The weekly Purge and reindex activity includes:

-- Deleting records older than a specific date.
-- Packing the tables.
-- Indexing the database tables using the INDEX ON statement, whic I believe recreates the index.

If you mean phusically deleting the NTX file using the DOS delete command before re-indexing ? Then no, we re-index using the same physical ntx file. Is there a difference ? Do you suggest to better phusically delete the file.NTX ?

clip4ever

RE: Better database engine

clip4ever,

As David mentioned you should be phsyically deleting the indexes before executing the INDEX ON statement.  If there is corruption you may be propagating the same corruption if you don't delete the index prior to INDEX ON.  Use the

IF FILE("FILE.NTX")
 DELETE FILE.NTX
ENDIF

test to see if it is present before you try to erase it.

On any of the high volume transaction you may want to recreate the indexes prior to using the program.  Basically if you add a lot of records I would recreate the indexes, your doing that with the PACK & INDEX ON after you purge the old records.  The volatility on top of a corrupt index  may be causing the continuing issues you see.

Jim C.


RE: Better database engine

(OP)
Jim,

Deleting the NTX is an inerresting idea. I'll try it.
Re-creating the indexes here and there and every night is not possible because it is a 7/24 application and I need an outage in order to re-index or do anything.

A maintenance release is coming in a week or 2, and I'm convinced to try the CDX and deleting the index file(s) before re-indexing. I'll let you know the results.

Many thanks again, with some of these ideas I might be able to:

clip4ever

RE: Better database engine

Hi,

I wouldn't be quite so quick to jump on the index corruption bandwagon - sure they can be problematic, but most of their problems are infrastructure  related, poor network cabling, power instability and the like - I'm not 100% convinced they are as buggy as all that. That's not to discount them entirely though.

This speed thing is interesting though and smacks of an ownership issue in Windows.

Does your code open and close the table as and when required, or does it hold it open and just access as needed?

If it's the former, then the windows server may be using a technique called opportunistic file locking... this is where the workstation asks to open a file in shared mode and the server hands it a local copy in an exclusive mode (it makes the server look quicker).

A second workstation trying to open the table at the same time then has to wait while the server and first workstation negotiate the release and reopening of the file.

Regards

Griff
Keep Smileing

RE: Better database engine

(OP)
Inerresting analysis. However, in my application, I open all databse tables in shared mode when I enter to the main menu and they all stay open until I exit back to the signon screen where I close most of them keeping a couple of control tables necessary for login.

According to your analysis I'm fine in this side.

Thanks

clip4ever

RE: Better database engine

In that case I'd be looking at the network hardware - duff hdd/switch/router/nic or cable... Clipper can append a large multiple index to the largest file much faster than almost any other db I know.

Regards

Griff
Keep Smileing

RE: Better database engine

Clip4ever

What does the code do when you scan an item? Is it a read only proccess? or is something written.  The fact that a re-index solves the problem, but it slows down as time goes on sounds like an index description problem, or the dreaded "set filter to" command. My experience with ntx indexes, is that when they go bad, you get junk, not a slow down.

Jim Rumbaugh

RE: Better database engine

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

RE: Better database engine

(OP)
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

RE: Better database engine

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



RE: Better database engine

(OP)

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


RE: Better database engine

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

RE: Better database engine

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

clip4ever

RE: Better database engine

Clip,

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

CODE

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:

CODE

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 Smileing

RE: Better database engine

(OP)
Interresting...

I'll try to get approval...

Thanks

RE: Better database engine

(OP)
Griff,

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

Regards

clip4ever

RE: Better database engine

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 Smileing

RE: Better database engine

(OP)
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

RE: Better database engine

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:

CODE

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 Smileing

RE: Better database engine

(OP)
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

RE: Better database engine

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 Smileing

RE: Better database engine

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

RE: Better database engine

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

clip4ever

RE: Better database engine

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close