Speed up SELECT using inedx?

[pxw]

Hi,
I want to speed up the select process by using an index. I have tried the following two programs. It seems that there is no improvement in program2. Actually, Program2 is slower than Program1 as it needs to generate the index on line. Could someone please tell me how to use the index with SELECT properly? Note that Servhist.dbf is about 100Mb.

Program 1
Use Servhist excl
select * from Stock where InvoiceNo=="E68712" into cursor Ctemp
browse


Program 2
Use Servhist excl
index on InvoiceNo to InvNo
set index to InvNo
select * from Stock where InvoiceNo=="E68712" into cursor Ctemp
browse

Thanks in advance.



Peter

 

[Mike Gagnon]

Couple of question.
1. Is there a reason you use the "=="?

2. Do you need to index Servhist every time?

3. If your Select is getting it's fields from Stock, how does that relate to Servhist?

 

[EdLeafe]

First, stop using .IDX indexes. Create a .CDX index instead by using the TAG format of INDEX. In your example, the code would be:

INDEX ON InvoiceNo TAG InvNo

This creates a permanent index that is automatically open and available every time you USE the table. No need to constantly reindex each time.

Second, while "==" is faster than "=" when doing comparisons, it is not compatible with Rushmore optimization in queries. Use a single "=" in your SQL statements. If you are concerned about exact matches, make sure to SET ANSI ON.

Third, why open the table exclusively? There is no need to do that unless you are modifying the structure.

 

[pxw]

Thanks for all the replys.

mgagnon, you are right. It is my mistake.

The table should be Servhist.dbf, instead of Stock.dbf. I don't need to index Servhist every time. The reason I use "==" is to exclude the exact matches. I am going to use "SET ANSI ON", based on EdLeafe's suggestions. I don't need to open the table exclusively.

The reason I use .IDX indexes is that it is an existing systsm. The index file Servhist.idx has been used everywhere in the system. It will be a big job for me to change them to Servhist.CDX index. The user doesn't want to do so as the system works well currently.

Peter

 

[Mike Gagnon]

One suggestion:
You could create a .cdx index without disturbing the .idx file and just open the table (the cdx file will open automatically) and use:

Use Servhist shared again in 0
SET ORDER TO tag Newindex && whatever you called it
select * from Servhist where InvoiceNo="E68712" into cursor Ctemp
browse

See if you get the right answer and better speed.

 

[pxw]

hi mgagnon,
Thanks for you suggestion.

I have tried your code. It is great! It only took 0.01 second to select 1 record from 77000 recodes, instead of 0.19 second without index previously. That is what I want.

The problem I have now is that the .CDX file, named ServhistCDX.cdx, can not be open automatically. For example the existing .cdx file can't be updated automatically as follows,
*/------test 1
use Servhist
append blank,
repalce InvoiceNo with "E68712"
use in Servhist
*Servhistcdx.cdx need to be reindexed.
*\------


It works okay in the codes as follows,
*/-----test 2
use Servhist index ServhistCDX
append blank,
repalce InvoiceNo with "E68712"
use in Servhist
*\-----

My understanding is that VFP can open/update the .cdx file automatically only if the name of the .CDX file is the same as the table. In my case, I can't do so as Servhist has been used as the name of the .idx file everywhere in the app.

I appreciate the if you can explain further what you mentioned above, "(the cdx file will open automatically)".

regards,



Peter

 

[Mike Gagnon]

I think your problem is related to the fact that the index name SHOULD be the same as the table in order to be opened automatically with the table otherwise as you discovered you would have to specify the index being used. Just try renaming your index Servhist.cdx and see if that will solve your problem. (Or is there a problem renaming it with the actual name of the table)

This part if the INDEX command text in the help file might explain what is happening: (the section in bold)


Two types of compound index files can be created: structural and non-structural.

If you exclude the optional OF CDXFileName clause from TAG TagName, you create a structural compound index file. A structural compound index file always has the same base name as the table and is automatically opened when the table is opened.

If a table's structural compound index file cannot be located or is deleted or renamed, a dialog box appears when you try to open the table. If you choose the default Cancel push button, the table isn't opened. Choosing Ignore opens the table and removes the flag in the table's header that indicates an associated structural compound index file is present.

Tip To reassociate a structural compound index that has become dissociated from its table, issue the following command:
USE TableName INDEX CDXFileName
If you include the optional OF CDXFileName clause after TAG TagName, you create a non-structural compound index file. Unlike a structural compound index file, a non-structural compound index file must be explicitly opened with SET INDEX or the INDEX clause in USE.

 

[wgcs]

How about this: Convince your customer that the proper way to obtain the speed advantage of Rushmore IS to use CDX files instead of IDX files? It should be easy enough to change SET INDEX TO to SET ORDER TO everywhere for whichever DBF's need the Rushmore (and therefore would benefit greatly from Structural Index files named the same as the IDX's)

 

[EdLeafe]

Also remember the syntax for creating a compound index tag:
INDEX ON [expr] TAG [TagName]

Sounds like you're using the standalone syntax:
INDEX ON [expr] TO [FileName]

-- Ed