Index on (variable)? 1

[JasonZ10]

Hi,

I'm making a quick function to find and replace occurrences of data within tables (procedure findreplace(table, field, find, replace)) to simplify some scripts I'm working on, but I can't figure out a way to index on the passed in field. I figured "index on (variable) to index" would work as "use (table)" works, but it just tries to index on "(variable)" instead of the contents of the variable. Does anyone know a way to get this working or have an alternate solution?

[tt]USE (tbl) IN 0
INDEX ON fld TO tempidx
GOTO top
DO WHILE NOT EOF()
IF SEEK(find)
replace (fld) with repvar
ENDIF
SKIP
ENDDO[/tt]

Thanks!

 

[Chris Miller]

In case of the REPLACE this works.

For the INDEX macro substitution is easier:

INDEX ON &fld TO tempidx

The reason is an index cannot only be on a field (name), but any expression. In that case the index would point to any record with the field name and SEEK "fieldname" would position on record 1.

Chriss

 

[JasonZ10]

Great, thank you! I appreciate the explanation

 

[Mike Lewis]

Jason,

You've had some useful answers. But I would ask: Are you sure you actually need to create the index here?

For any given table, you normally only need to create an index once - when you first create the table. You would not normally create the index every time you use the table. Doing so does no harm. But indexing is a slow operation, and doing it when you don't need to can slow down the whole application, especially if your tables are large.

I appreciate that your case is slightly different from usual, in that you don't know until run-time what index you want to use. But the table has a finite number of fields, and it does no harm to create an index on each of them (that is, each of those that you are likely to be using in your searches). You create all the indexes when you first create the table. Then use ORDER BY to select a specific index for a given search.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

In the example from Mike Y above you need to be sure that repvar is not equal to find

Otherwise, nothing would change and the scan might loop indefinitely.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

I don't like to index every field of any table just to be able to sort by any field or seek in it. It noticeably increases the insert time.

I think Jason thought it through enough, as he's not creating index tags, just IDX files. He names it tempidx, so it'll be removed later, I guess, or it'll be overwritten by the next call. If tables are small it doesn't cost much. Well, you could also LOCATE. If tables are large, it'll have a break even depending on how many times the "find" parameter is within the data.

There is another solution, that's just overly complicated, in the ffc framework you have a grid header sorting that looks for a usable existing index tag and creates a new one if necessary. It's based on ATAGINFO, so it doesn't use an IDX, if one matches.

Overall I agree definitely indexing no matter what indexes already exist will likely not boost this at all, so you could use LOCATE and rely on it to find a usable index. It'll just be slow on large tables, but at least you limit the table scan to a single pass when making use of CONTINUE, so actually it pays in comparison with indexing, as that's also a single pass through all data anyway. You really only profit from already existing indexes.

Chriss

 

[Chris Miller]

Griff, good point.

Also, when you replace something with something plus a suffix, SEEK will still find that something (eg find ABC replace ABCD, SEEK ABC still finds ABCD).

All in all I think it's still just a very academical corner case.

Chriss

 

[GriffMG]

Sorry Mike, I wasn't really referring your example, just that if you change a field while processing a list indexed on that
field - in general you get unpredictable results - and I think scan is only immune from that if it can use rushmore to create
the bitmap, which it most likely would in this instance, bit not if the field were part of something more complex.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​