INDEX versus TAG 5

[SitesMasstec]

Hello colleagues!

I would like to have your opinion about INDEX clause when opening a file (free table only).

I have the following in a PRG file to add a new record, update or delete records:

SELECT 1
USE CLIENTES INDEX INDCCODI,INDCSTAT,INDCNPOP,INDCNOME,INDCCCGC,INDCCCPF,INDCCIDA,INDCMAIL,INDCDPRO,IND00001,IND00002,IND00003,IND00004,IND00005,IND00006,IND00007,IND00008,IND00009

This PRG file has evolved since FoxPro DOS, and now, after someone in this forum told me to use TAGs in the file so I do not need to use INDEX.

I have a lot of indexes for this file because in a report I need to order records by INDCCIDA+IND0003.

For another report: USE CLIENTES INDEX INDCNOME, or USE CLIENTES INDEX INDCCODI

Othe report uses USE CLIENTES INDEX IND00005, and so on.

I think it is an absurdity to have all this indexes opened when changing the file (add, exclude or delete records).

Can I get rid of all this indexes, and use TAG instead?

Thank you,
SitesMasstec

 

[bborissov]

Sure,
But I doubt you need all this indexes all the time.
If you need an index JUST for report use SQL Select into cursor and order the information there.
And the base the report of the result cursor.

Borislav Borissov
VFP9 SP2, SQL Server

 

[TamarGranor]

As I told a client last week, I haven't used an IDX file since we got CDXs in FoxPro 2.0. If you're in a position to replace all those IDX files with a single structural CDX ("structural" means it has the same name as the table, and opens automatically when the table does), then absolutely, ditch the IDX files and make your life much simpler.

Borislav is also right; you don't need to maintain indexes that are used only for reporting. Run a query before you run a report to collect all the data and put it in the order you want. If you're not familiar with SQL, you might start with this paper I wrote:

http://tomorrowssolutionsllc.com/ConferenceSessions/Learn to use SQL.PDF

Tamar

 

[SitesMasstec]

Ok, Tamar, I want get rid of all IDX files in my applications.

But can I use TAG in free tables? (In your paper about SQL it seems I have to use the tables in a database, not in free tables)

I'm trying to get more basic information from the Hacker's Guide to VFP 6.0 (oh, heavy book).

Thank you,
SitesMasstec

 

[Mike Lewis]

You've been given good advice by Borislav and Tamar. I can only add that it is very rare to see so many indexes on a single table (and even rare nowadays to see each index in a separate file, which is what you have).

As a rule of thumb, indexes can speed up the retrieval of data. But they can slow down the updating of data. For that reason, you should favour indexes only on the fields or expressions where retrieval performance is important.

Your best bet - as Tamar has said - is to replace all the single index files (IDXs) with a CDX. That way, you won't have to worry about explicitly opening the indexes.

Also, you asked if you can use TAG with free tables. The answer is yes. It makes no difference whether the table is free or is part of a database. But TAG isn't an alternative to INDEX. A tag is simply a name by which you can refer to the index. You specify the tag when you create the index; thereafter you can refer to the tag, for example in SET ORDER.

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

You also need to take account of the size of the table. If the table is small - say, only a few hundred records - you can probably get away without any indexes at all. For reports, do as Borislav suggests: use SELECT ... ORDER BY ... INTO CURSOR, and use the resulting cursor as the data source for the report.

But if the table runs to hundreds of thousands of records, then the choice of indexes is critical. That might also be true if you have a moderately-sized table but you are running many reports many times.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[tomk3]

Hello,

just to clarify with an example

use adr
index on shortname tag short
index on bdate tag bdate
index on grpname + shortname tag grpname
(all this goes in one .cdx)

Use examples
set order to tag bdate or set order to 2 or set order to bdate
set order to grpname DESCENDING

Pls. also look in help for more options like for clause and other commands like indexseek and maybe you google on "binary index" if you for example have an index on deleted()

Regards
tom

 

[Chris Miller]

Free tables can have a cdx, that's not the problem.

The most important limitation on indexes of (legacy or not) free tables is they can only have a candidate index type, not a primary index. But using idxes you only use the normal index anyway. And candidate is as good in its properties of not allowig duplicate keys and null as primary keys are, they will just not be automatically used for pk/fk relationships. You should really dig a bit more into your options.

Instead of trusting your "belief" you can't do something, you can simply try an INDEX ON field TAG tagname and see if it works and as easy as that you get out a wrong idea you have about VFP. If you're concerned with data integrity in case something errors, do such an experiment on a copy of your DBF and you're safe?

You're wrong ides of VFP are limiting you and you have VFP itself available to answer such uncertainties yourself. You need even less code to check it out for yourself than wrinting a question here. What is your bloackade, really?

Chriss

 

[Chris Miller]

In your paper about SQL it seems I have to use the tables in a database, not in free tables

In such situations, where you tell from where you got an idea or knowledge, it helps if you refer to it by a more specifci name of an article, (white)paper or with a link. What is it? Or don't you remember anymore?

I can't imagine Tamar ever wrote something like indexed optimized SQL only works on database tables, what spawned your wrong idea about how things work must have been a misunderstanding or generalization of a very specific case.

Chriss

 

[Mike Lewis]

A little further clarification ...

In his post (above), Tom suggested:

use adr
index on shortname tag short
index on bdate tag bdate
index on grpname + shortname tag grpname

All good stuff. But be clear that this would be a one-off operation. The INDEX command is what you use to create the indexes. You only need to do that once, when you first create the table - not every time you use the table.

It is the SET ORDER TO or the USE ... ORDER that actually opens the indexes that were created previously.

This is probably all obvious, but I thought it worth mentioning.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

This is probably all obvious, but I thought it worth mentioning.

Indeed I think the usage of [tt]USE CLIENTES INDEX list of idx filenames[/tt] indicates SitesMasstec is aware that usage of IDX files has the need to open them together with a table to update them when changes are made to the DBF. Tamar also pointed out that a CDX is automatically taken into account, SET ORDER TO a tagname is then setting an order, if you explicitly want to and not just rely on automatic usage of indexes by SQL or LOCATE by Rushmore optimization, indexes always have that double use case of being used for optimization and sorting. You don't need to fear this gives you less space for your indexes, usually the CDX grws less than the fpt file for memos, for example, I've never had the case the CDX file size was the limiting factor of the overall table file space limitations. Plus, you can define further CDX files and have more indexes than the 2GB of the main CDX file. So there's no need to worry about that. CDXes in themselves are making better usage of disk space than IDX, which you can also verify by createingh two IDX and a CDX with the same indexes and comparing the toal size of both IDX vs the size of the CDX.

So indeed up until now you waste disk space and made optimizations harder than necessary, even if you always were opening IDXes together with a DBF. In some aspects free DBFs are still faster than DBC DBFs, but IDX files are not better than CDX files at all.

Chriss

 

[SitesMasstec]

Yes, Mike. Tom's example is good and your note is important for clarifying it:

You only need to do that once, when you first create the table - not every time you use the table.

So, after I create a table, I shall index all the fields I will use (just one time).

USE CLIENTES EXCLUSIVE
INDEX ON CCODI TAG CCODI
INDEX ON CNOME TAG CNOME

Then, I can use in a report

USE CLIENTES ORDER CNOME

If after some weeks I need the table to be ordered in a new way for a new report, I can type the command:

USE CLIENTES EXCLUSIVE
INDEX ON CCIDA+CCCEP TAG ENDER

In the event the CDX file becomes corrupted (blackout during saving a record, for example), I can have a PRG file to recreate it. As:

USE CLIENTES EXCLUSIVE
INDEX ON CCODI TAG CCODI
INDEX ON CNOME TAG CNOME
INDEX ON CCIDA+CCCEP TAG ENDER

Thank you,
SitesMasstec

 

[SitesMasstec]

I've just finished reading what Chris posted above, before my last post.

So, I will stick to CDX, better than having lots of IDX files in a data folder. When, and if, necessary I can create one IDX for a particular listing.

Now, thank you, I have a clear direction to take.

As an additional learning I will read (or reread) "Indexes in Visual FoxPro", chapter "Working with Data" in Kilofox.


Thank you,
SitesMasstec

 

[Mike Lewis]

SitesMasstec, looks like you've got it spot on. Your latest two posts are completely correct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Ah, I've just thought of something you might need to keep in mind.

It's quite common (and perfectly acceptable) to give the tag the same name as the field. And if you do that in the table designer, it will happen automatically by default. In other words, if you create a field in the table designer, then use the Index combo (to the right of the field name) to create a tag for it (and specify it as either ascending or descending), then the tag will have the same name as the field.

No problem ... except that tag names are limited to ten characters. So if the relevant field name is longer than ten characters, the tag name will be automatically truncated. And if you don't realise that that has happened, you will get an error in your program when you try to SET ORDER TO what you think is the correct tag name.

Obviously this is not a big problem provided you understand what is happening. But it is surprising how many people get caught out by it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I agree with Mike, what you posted as code examples is a good way of using the index tags, including a way to recreate them. Just add that you would DELETE TAG ALL before recreating the indexes.

Also, you don't need to use the ORDER clause of USE, and you can switch sort order at any time by using SET ORDER TO TAG tagname, there does not need to be an initial order, i.e. openina table now can concentrate on just that, opening the table. The TAG is automatically found in the main CDX, if you have secondary IDX or CDX you need to set order as before or in case of a secondary CDX, like tablenam2.cdx you'd use SET ORDER TO TAG tagname OF tablename2.cdx, for example, and the OF clause is unnecessary, if the tag is part of the main CDX. which will be the the most likely case. You rarely need a second CDX. There is still a reson to have the ORDER clause of the USE command, you often will use/list/search a table in a spcific sort order and therefore a tag used right away is often helpful, but it won't be necessary to optimize a search by SQL or a LOCATE, that will not only use open CDX tags, it will use the full CDX, no matter if you open a tag or not.

There is a use case where an IDX is very appropriate: If you only need a temporary sorting of data, which you later can dispose by deleting the IDX file. It's possible to use in a DBF that's no open exclusive, I'm quite sure you also don't need exclusive access to create a CDX tag, too. The major point here is that an IDX could be created as a local file that only applies to one client and not all, so you could use an IDX to create a complex sort order with primary, secondary sort order by an expression like creating an IDX on LASTNAME+FIRSTNAME limited to a current filter with a FOR clause, to give an example, where an IDX still is quite fine. Anyway, when indexing query data I usually have that in a cursor that's temporary anyway and use a cursors CDX, cursors, like DBFs, can also store their indexes in a major CDX and that's also automatically disposed when a cursor is closed, so even that use case for an IDX is something you can also solve with a CDX.

Chriss

 

[SitesMasstec]

Yes, Mike, I realized that there is an Index combo in the Table Designer to activate the correspondent TAG for the field.

But the table field name doesn't accept more than 10 characters. But I understand now, as you notify me that if I use programatically this, the Tag name will be truncated to ENDERECOFI:

INDEX ON CCIDA+CCCEP TAG ENDERECOFIRMA

Thank you,
SitesMasstec

 

[TamarGranor]

Looks like the others have all sorted out your questions. I am curious what I wrote that misled you to think that CDXs were only for tables in databases (so I can say it more clearly the next time, or even fix the paper, if that's appropriate).

Tamar

 

[SitesMasstec]

Hello Tamar!
I was not absolutelly mislead by your excellent paper "Learn to use SQL": as one example I saw in the article was about a table connected to a Database, I was in doubt if the use of Tag/Set Order could be used in a free table, too. That was the only reason I ask you dear colleagues the question in my post.

Thank you,
SitesMasstec

 

[Chris Miller]

SitesMasstec,

Thanks for pointing out the title of the article you read, now Tamar could verify what she wrote and whether that was misleading in some way.

SQL can also be used on free tables, and also on 2.x version legacy/free tables, the SQL engine does not have any requirement about DBF file versions. I guess it's not the article that talked about the limit you have remembered, it's your interpretation of the article. Nothing seriously alarming, it's hard to get something completely right when you learn it for the first time, but learning wrong conclusions from what you read obviously is even worse than the other usually feared danger of half knowledge that even exists if you have everything right but still only know part of what should be known to use a technology. If you don't learn from a teacher in person, in University or school and are not tested and have to pass exams, in short learning autodidactically, you should always take your time to test what you read in techincal articles with hands on VFP and do the things that are explained to see whether your understanding is right or wrong, an article, book or any such one way learning material will never be able to test your understanding, but VFP itself will always react to something that doesn't work, it's always even better than any expert in telling you what works and what doesn't. An expert then is good for telling you what you have to change to make your failed try work.

Chriss