advantage..index versus tag? 2

[gentforreal]

I tried to find the answer in here... but... what is the difference... or better yet the advantage to using tags for indexes as opposed to just using "index on cfield to whatever" ? Do straight indexes require reindexing and Tags do not?

Thanks

 

[jimoo]

Both index types require maintenance (reindexing) from time to time. A structural index allows you to have mulitple indices while only opening one index.

Using the IDX to create indexes is nice for temp tables, where you plan on deleting it, but I would not use them on production data tables. The biggest problem with using them is that you must manually open then each time in your USE statement or risk on of them being corrupt.

My thoughts.
Use CDX for production tables.
USE IDX for temp tables you plan on creating a single index for and deleting later.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[gentforreal]

Good food for thought.. thanks.

 

[jimoo]

Thanks for the *

I should note that FoxPro, which came after Dbase, took on much of the dbase characteristics. These languages became known as the Xbase languages.

In the early years of Xbase programming, we were limited to 5 indexes per table; each index was a separate file. Consequently, opening 1 table with 5 indexes took up 6 file handles. For those of us who have been around a long time we remember messing with the FILES=NN command. We tried to keep it low, but we also need enough to run our apps.

The 5 indexes were tough to manage. You had to know all of the index files for each table and be sure to open them each time.

USE mytable INDEX a, b, c, d, e

was not uncommon. If you forgot to include one of them, and updated data, you risk corrupt data because that particular index would not be updated....

To make a long story short, CDX files have been wonderful. IDX are still used from time to time, but mainly for temp tables, etc.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[andreateh]

Any different if i use CDX with the temp table instead of using IDX?Is it will cause perfomance slow down ?

 

[rgbean]

Nope, in fact if you need more than a single index, a .CDX can be more efficient!

Rick

 

[mm0000]

One situation where you could think of using an idx file instead of a cdx index is if you want to create an index and the table is being shared. Creating an idx index does not require the table to be used exclusively.

 

[andreateh]

Creating an idx index does not require the table to be used exclusively.


Can i assume that IDX as a temporary INDEX ? Which mean when i try to do a "Sorting the grid on multiple columns" i can simply create the indexes for all the column and then just delete the .idx files from computer(without effect the original .cdx) ?

 

[craigsboyd]

andreateh,

Yes. That is exactly the kind of thing it is good for (one of the things anyways). I have a grid class that creates an IDX on the fly as the user clicks the columns to sort the contents...I first check to see if there is something in the CDX (if there is one) that I can use, and if not then I create an IDX on the fly and delete it when I am done. I do them one at a time rather than doing all of them cause there is really no telling if the user is even going to sort one of the columns and why expend all that energy for nothing.

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[mm0000]

An idx file by itself is not a temporary index - however you can use it as a temporary index if you need to and you can do so without affecting the cdx files. Deleting the idx files will not cause any problems to the cdx files. Only remember to use the 'set order' command to set the master index.

 

[andreateh]

Craigsboyd and mm0000,thanks for yours the explaination.

 

[DSummZZZ]

mm0000,

I had never thought of using an IDX for a temp index, never tried it. I had always kept in my head I needed to have exclusive use of tables when creating any index.
It's a little tidbit I'll keep in the back of my mind.
A star from me for the enlightenment!

old dog == new trick.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[jimoo]

Another interesting tidbit is that you can use it to index a view. It's not often you will need to do that, but if you have a view that contains a lot of data and it doesn't change frequently, you can add an idx and that will increase access speed a ton.

Ironically, IDX files have been kept around mainly for backward compatibility issues, but there still some neat uses for them.






Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Stella740pl]

Deleting or just dropping .IDX files won't cause any problems to .DBF itself - the table will still open and not require the index files for that. Because of that and the fact that indexing to .IDX doesn't require file to be open exclusively I often use .IDX as temporary index - usually while debugging or looking for data problems. I might never need this particular order again, so why cram .CDX? I just create them on the fly in my Temp folder, and often just leave them there till next Temp folder maintenance. Then I will delete them along with other stuff.

 

[andreateh]

The problem when using idx as temporary is when another user open the same table at the same time and delete a record. The deleted record will not be update in the table with the temporary idx. So if the user (with the table with idx open) choose the record which is deleted by another user it will be trouble. Can any one give some idea how to solve it ?

 

[mm0000]

If it is not too much of a problem (too many records or any other specific situation) you could think of recreating the index again everytime you worked on the data.

 

[gentforreal]

Another related question to using indexes, with SQL select statments and very large databases, will indexes speed up the results by a substantional margin? If its re-indexed to a temp.idx each time, it seem that you trade off.

 

[Stella740pl]

gentforreal,

As for me, if you need the same index expression each time, then .IDX is not right for the job, and you need a tag in .CDX instead. I haven't used .IDX in an application since long ago, and use it only interactively, if I need it once to research a problem in a table, while debugging a suspended application, to do a one-time data request and such.

 

[craigsboyd]

gentforreal,

If what you are asking is would it be faster to Index the table temporarily to run a query on it, rather than just running the query the answer would be NO. However, having reuseable/available indexes who's key expressions match the expressions used in the query will speed up the retrieval of the data substantially YES. Knowing where to draw the line between having too many indexes and having too few is a fine art and aided substantially by the SYS(3054) function and the Coverage Profiler. You can use the SYS(3054) function to see how optimized the SQL statments are that you are running and you can use the coverage profiler to effectively guage any improvement/degradation in speed (time elapsed).

_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​