Progress /Record Count during index

[andy0923]

I know this may be a tired subject but thought something new might have developed recently.

Does anyone know of a way of returning the current record number during a long indexing process?

My purpose is to use a custom progress bar.

Thank you

 

[Dan Freeman]

There's nothing built into the product, and since VFP may make multiple passes through the table to do the inndex it wouldn't be much help to have the recno().

I usually SET TALK ON and SET TALK WINDOW.

 

[andy0923]

Thanks guys. Mike, thats a fascinating idea. I must try it as an academic challenge.

As always, much appreciation to this site and you guys.

 

[Scott24x7]

I have used a different approach, since machines can varry... The first time it is run I capture the start and finish time. The next time it is run, I create a "MS-Like" "About xseconds or xminutes remaining" and update this at 10/Total time increments. I also save this start and finish time, and so next time it is based on the average of the total start/finish times for the run. This is all kept in a database. In reality, I have found this method to be MUCH better as a predictive "Time remaining", then tie it to a progress meter. I can set what functions I want to monitor in this way. This also allows for your database to continue to grow, and retains the "About this long" time. It's nice to know if a process is going to take a few seconds, a few minutes, or hours... The users like it too, and it's independent of anything. If you run on a slow machine, it is reflected in the time. You can "localize" this table so that it is machine specific for exactly that reason, so you don't get weird predictions for other machines.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Scott,

I was very interested to read your idea for displaying an "about n seconds remaining" message. It sounds like a good approach, and I can see how the users would like it.

However, to go back to Andy's question: how would you tie that into the creation of an index? The root of the problem is that there is no event or hook that you can trap to trigger the code that updates the message. Once you've started an indexing process, your program has no opportunity to step in to update the progress message.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[andy0923]

Just to let you folks know that Mike Yearwood's UDF worked like a charm. However, on a table of 50000+ records the time it took was quite a bit longer:

start 10:39:35 end 10:40:03

as opposed to without the UDF

start 10:40:15 end 10:40:17

So I may just tinker with Dan's suggestion of using set talk window

 

[Scott24x7]

Mike,
Launch a seperate application with a timer on it. Make it idependent, and modal... stays on top that way. It simply updates throughout time, since the database captures the "Start and Stop" times, don't DESTROY() the seperate application until there is a value in finish for the last record...


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

hmm, you could make the udf dependant on _vfp.startmode or even a self defined variable. That way no cdx hack is needed, eg

field+iif(vartype(gProgress)#"U",showprogress(),"")

To make the index creation faster, you may only invoke showprogress, if recno()%set("odometer")=0., eg

field+iif(vartype(gProgress)#"U" and recno()%set("odometer")=0,showprogress(),"")

Then release gProgress after index creation.

Still it's an ugly way to hook into the progress of the index (re)creation. Think about rushmore optimizations, that would work for the field, but only if you use that key expression with the additional iif-expression within the sql.

You could also use the for clause of index calling a showprogress() function, that always returns .T.:

index on field for showprogress()

or

index on field for recno()%set("odometer")=0 and showprogress()

But that again won't be used by rushmore. It might be easier to hack that. I see the bit 3 (value 8) of byte 14 o the cdx file is set 1 if the index has a for clause, I wonder if it would be sufficient to set that off.

Bye, Olaf.

 

[Olaf Doschke]

no forget about the last suggestion, that would index only few records.

Bye, Olaf.

 

[GriffMG]

It's not that hard, you just need two UDFs one 'inside' the app and one 'outside' it.

They both return an empty string but the one doing the 'work' updates the screen.

Regards

Griff
Keep [Smile]ing

 

[jbailey268]

with regard to
start 10:39:35 end 10:40:03

as opposed to without the UDF

start 10:40:15 end 10:40:17

The only way to truly benchmark would be to reboot between each try.. A second index of exactly the same thing (or near exactly) will, of course, be lightning fast by comparison just like an SQL query because of the cashe memory. Namely - it's already seen this and knows what to do.
Try rebooting between each test - I know it's a pain but it's the only fair way to guage.

 

[GriffMG]

Hi Mike

You need to use two executables, one for creating the index files and the other being the 'normal' application.

Your index expression is the same for each case:

index on myfield1+myfield2+MyFunction() tag MyIndex

But there are two versions of MyFunction, they both return an empty string (or just one space if you prefer), but the one in the 'indexer' also does some progress reporting - you choose exactly what.

So, in your main app you would have MyFunction defined like this:

Function MyFunction
Return(" ")

and in the 'indexer' you could have code that looks like this:

Public MyCounter
MyCounter = 0

index MyTable on MyField1+MyField2+MyFunction()

Function MyFunction
  MyCounter = MyCounter +1
  if int(MyCounter/100) = MyCounter/100
     if reccount() > 0
        Wait "Indexing "+AllTrim(str((MyCounter/Reccount())*100,6,2))+"% Complete" window nowait timeout 1
     endif
  endif
Return(" ")

Regards

Griff
Keep [Smile]ing

 

[Olaf Doschke]

I'd not return " " (space(1)), even for a character type expression, space(0) would be better. In fact the needed return value depends much on the index expression (type). To make it more general, why not make it an echo function?

function Myfunc()
  lpara tuAnything
  ...
  return tuAnything
endfunc

The filtered index idea would be nicer, if you can manipulate the cdx later to be used as a non filtered index, as that does not interfere with the index expression at all.

Still, the whole idea is ugly. Even SDT does not show the progress of a single index creation.

Bye, Olaf.

 

[GriffMG]

What is SDT Olaf?



Regards

Griff
Keep [Smile]ing

 

[GriffMG]

I never think of Rushmore, so that hadn't occurred to me, come to that the use of SQL commands in this insance hadn't occurred to me either - but I guess they would be relevant if you were working via OLEDB or ODBC!

As for the function names being the same - you're quite right, you can't do that within the same .exe/app but the idea was to get the main app to run an 'indexer' which could have a deliberately 'similar' function in it - which is probably excusable and *slightly* better than hacking the resultant .CDX file (IMHO).

It was just an idea to 'hook' into the index creation - it's clear that VFP can do it for itself, so it SHOULD be possible to programmers - even if it is not at the moment.



Regards

Griff
Keep [Smile]ing

 

[Olaf Doschke]

Hi Griff,

SDT is the very popular stonefield database toolkit, which contains about anything you might want to do as a database admin: fixing tables, updating a database/views, reindexing, storing all kind of metadata about a database, database maintainance.

Bye, Olaf.

 

[GriffMG]

Thank you Olaf

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Nah Mike, I never, ever, think of it.

I optimise my own searches by design, and just ignore Rushmore! Obviously I have select statements for some things - combo boxes for example (which would be very simple 'select code,decrip from...') - but unless I'm using using OLEDB I don't use them at all!

I come from Clipper and am used to coding the extraction of records for subsets etc - I don't think I'm any slower/faster than Rushmore...

B-)

Regards

Griff
Keep [Smile]ing

 

[TamarGranor]

Griff-You may be using Rushmore without knowing it. Any Xbase command that includes the FOR clause is a candidate for Rushmore.

As Mike says, don't assume you can make it faster. Remember that the VFP engine is written in C++, so it's a layer closer to the machine. That's why it's nice when we get built-in functions that replace home-grown ones.

Tamar

 

[GriffMG]

Hi Tamar, Mike

I'm sure you're right if I used FOR or WHILE I might find that Rushmore had taken advantage of the indexes in place...

But I don't actually use the FOR or WHILE statements (except for the odd report I suppose - and even then not often) because I was 'brought up' in Clipper and tend to use the index files to limit my scopes to the info of choice...

I agree that functions that get 'built-in' are generally faster - but not always, I did a test with scan/endscan against a do while/enddo and found that was actually measurably faster!

But I'm happy 'GriffMore'ing my data, so I'll carry on with that - one things' for sure, if I design in indexes that help ME, then if a bit of Rushmore takes advantage of it so much the better. If I leave the indexes out Rushmore won't be able to improve on my results!


Regards

Griff
Keep [Smile]ing