What do you do about large index on commands Not Responding?

[metron9]

I was testing a large database and doing an INDEX on FIELD to FILE.NTX
And my window gets a not responding and hangs.

I remember working in C you had to give windows time during a long loop
but i don't know what the commend is here to do that in a normal loop or
when issuing a command like INDEX ON.

Oh i just found Do Events for the loops, but still dont know about index on command.

[esgici]

I was testing a large database and doing an INDEX on FIELD to FILE.NTX
And my window gets a not responding and hangs.

I remember working in C you had to give windows time during a long loop
but i don't know what the commend is here to do that in a normal loop or
when issuing a command like INDEX ON.

Oh i just found Do Events for the loops, but still dont know about index on command.

Could you denote your INDEX command here ?

Regards

[danielmaximiliano]

Hi :
I have 210 Mb tables were created in 1997, the much larger now since I have not for months an updated copy on my machine.
has 75 fields and more than 200,000 records, currently use CDX indices with an increase of 20% over the previous indices.

Bases_2012-09-20_08-50-48.jpg (82.83 KiB) Viewed 3345 times

Some indices are quite complex and take a long time to create them and not hang up or do not presentra bloqueda the pc for other processes.

[metron9]

I will study how to change from ntx to cdx index files daniel.

The code is

index on jobtick to jobtickx

That's it just the index command of clipper.

[esgici]

I will study how to change from ntx to cdx index files daniel.

The code is

index on jobtick to jobtickx

That's it just the index command of clipper.

INDEX command syntax : (simplified)

Code: Select all

   INDEX ON <indexExpr> ;
         TO <cIndexFile> ;
         ... 
         EVAL <bBlock> ;
         EVERY <nInterval> 
         ...

EVAL <bBlock> : This parameter is a code block that must return a logical value. The indexing operation continues as long as EVAL(<bBlock>) returns .T. (true). The operation is stopped when EVAL(<bBlock>) returns .F. (false). The code block is evaluated for each record unless the EVERY option is specified. It is recommened to use EVERY in conjunction with EVAL.

EVERY <nInterval> : This option is a numeric value specifying the number of records after which the EVAL block is evaluated. This can improve the indexing operation especially for large databases. EVERY is ignored when no EVAL block is supplied.

So it's possible to do something like this :

Code: Select all

index on jobtick to jobtickx EVAL { || DoEvents() } EVERY 1000

...


FUNCTION DoEvents()

  DO EVENTS 

RETURN .T.

<HMGRoot>\SAMPLES\EDIT.EXTENDED\DEMO.PRG\OpenTable() procedure may be useful for learning .cdx indexing.

[metron9]

Very very cool. thanks for such an in depth answer.
I remember reading about code blocks and ignoring them because i thought
they were too complicated. Like calculus.

But you make it easy to understand.

I just finished testing and it works very well.
I tried it without using the doevent and it works well as well now.
I am glad it did not work well at first as I have learned very much tonight.
I created a feedback message using a public variable, I index the variable by 1000
each time the doevent function is called and display the progress of the indexing.

I was at first pulling my hair out because i looked at the size of the ntx file in the windows
explorer, that only read 1K and sometimes 3k so I finally went to dos window
and compiled a test program to just index the file using hbmk2
I see the new file and the file made in HMG was the same size in dos window
but different in explorer window so error was display in explorer for some reason.
I attached a screen pic of the difference.

That is working good now.

I will post my next question that came up working on this in a new thread.

[esgici]

I'm glad that your problem solved

It isn't time to add "progress bar" to your indexing process ?

Don't worry about differences between file sizes, please look at this old discussion.

Regards