Index question to avoid corruption (periodic maintenance)

[EricN]

I know there is an index script example in the wonderful up-to-date on-line help files but I�m concerned about corruption and I hope to get a definitive answer regarding index maintenance on the web.

I have several tables and tried but could not get away with creating at least one or two indexes for each.

I also use the LOOKUPx() extensively and I see (too late) that this may not be such a good idea by this post http://msgboard.alphasoftware.com/al...ighlight=index

When I put my project on-line, I would like to know how necessary it is to create an a5w page that will conduct index maintenance automatically (update indexes).

Do you do this?
How often do you run the script?
Script ideas?
Do you conduct any periodic maintenance on your LOOKUPx() functions?

Thanks for your input!

[jkletrovets]

Re: Index question to avoid corruption (periodic maintenance)

Hi Eric,

I'll chime in here and see if I can help any....but most of my experience is on the desktop side. That really should not matter. That being said, if your app is designed properly, you should almost NEVER have any index corruption (like years with no problems). This is something I never worry about on the desktop side and I can't see why the web side would be any different. If anything, I would expect the web side to be even more stable because the likelihood of more than one user doing something that might cause a problem at EXACTLY the same time is greatly diminished on the web.

I use the lookupx() functions a BUNCH on the desktop side. I think what Steve was saying is that had he just used the lookup() function it would have sequentially just read the records in the table and since he did happen to have a corrupt index his processing would still have worked. The lookupx() functions are a great idea to my way of thinking since they are really fast.

Also, while this may not work straight away on the web side(you would have to play with this some) - you could make a desktop hybrid app to index your files should you have a problem with Cal Locklin's utility.

Regards,

Jeff

[Steve Workings]

Re: Index question to avoid corruption (periodic maintenance)

I use the lookup functions, but when I need to be really sure, I build a query to find what I need.

As long as queries qualify for Lightning Query Optimization (see help docs for LQO rules) there's no performance problem here, and no worry about index problems.

[EricN]

Re: Index question to avoid corruption (periodic maintenance)

Thank you both for responding.

I neglected to mention that my system will conduct anywhere from 10-100 automatic daily record deletions (early morning hours) and periodic user deletions. There will also be 10-100 daily additions.

Would this change your opinions?

[Steve Workings]

Re: Index question to avoid corruption (periodic maintenance)

Nope. The surest way to insure accurate record location is to run a query. Because you can predict what queries are to be run, you can make 'em LQO queries. The basic model is simple:

query.order = ""
query.filter = "x = y"
t = table.open("[pathalias.adb_path]\mytable.dbf")
qdx = t.query_create()
if qdx.records_get() = 1
  ' do one thing
else
  ' do the other
end if
t.close()

[EricN]

Re: Index question to avoid corruption (periodic maintenance)

Thanks Steve.
I know how to make queries.

I just have so emails and code only pages where I have already implemented the LOOKUPx() function. I'm wondering about "over-indexing" (too many temp indexes) and corruption. I'm really not excited to go back and rewrite all of them into queries. Or am I still save and just being an alarmist?

[jkletrovets]

Re: Index question to avoid corruption (periodic maintenance)

Eric

Steve is MUCH more experienced than me on the web side but....

I neglected to mention that my system will conduct anywhere from 10-100 automatic daily record deletions (early morning hours) and periodic user deletions. There will also be 10-100 daily additions

That really is not that much and deleting records should not corrupt your indices at all. Perhaps apply a tbl.pack() every once in awhile to just get rid of the unused space in the table. If I was you I would use the lookupx() functions. If you find for some bizarre reason you are having problems then you could look at changing things around. That being said, in my applications it is EXTREMELY rare to ever have a corrupt index. I have one networked desktop app that has been running on dbf's for 13 years and maybe I have had one corruption (if that). In my opinion, it should not enter into your mindset that you have to design for what happens WHEN I get an index corruption. The indices should not corrupt if the design of the app is correct.

Just my 2 cents... :)

[Steve Workings]

Re: Index question to avoid corruption (periodic maintenance)

Don't get me wrong. I still use the lookups and other non-query methods for getting pieces of data. I have run into trouble on one table in the past where a re-index was necessary once in a while, but otherwise I still judge them as a sound technique. Some of this depends on how critical the procedure is. With a query, for instance, you have more opportunities to test for the correct record(s) and do something in case there's a problem.

[Steve Wood]

Re: Index question to avoid corruption (periodic maintenance)

Querys will use an index if one is available. So if the index is corrupt, and the query uses that index, the query response will be exactly the same as if you used a lookupc() function using the index directly.

I just went through a senerio like this, so thought I'd mention, if have to be really sure, add query.options="n" or query.options="m" to ensure it rebuilds the query from scratch, or absolutely does not use any available index.