Index Tip?

[doorscomputers]

Re: Index Tip?

Unless someone has already suggested, and the app is multi-user, on a LAN, I would look into "oportunistic record locking."

http://www.dataaccess.com/whitepaper...adcaching.html

Also, when you say "corruption," I presume you are saying you have gone to design mode and checked that the indexes are stlll there and are still spelled the same on the master app on the server..

Nice alternative Martin. http://www.dataaccess.com/home.asp?pageid=700

[Ted Giles]

Re: Index Tip?

Seems like a Ray in capetown style, Okey, nice original advice, go back to post number 19 and you will find 2 zip files, just in case you know, post your modification to solve index corruption issue.concentrate on the order application.

1) this is borderline rude.
2) it's your application, you do it - you have had a lot of free advice and suggestions to try.

Index employeeid name appears twice
You have a Recno named Indx

[aschone]

Re: Index Tip?

Seems like a Ray in capetown style, Okey, nice original advice, go back to post number 19 and you will find 2 zip files, just in case you know, post your modification to solve index corruption issue.concentrate on the order application.

No problem, sorry I couldn't be of any help. Good luck in your issue.

[SNusa]

Re: Index Tip?

You may get problems with similar named indeces in different tables when the first 8 chars are the same.

A while back, I posted a thread regarding index collisions (between several simultaneously open tables). ~ Relative to both tables sharing the same index name. Reason I bring this back up is I'm experiencing index corruption when looping through a table...

To process updates as efficiently as possible, I have two loops, an outer loop and an inner loop, both fetching through two different tables, adding data from a temp table to the main table. While looping through these two open tables (via nested loop)..... On each inner loop, a search for records in a third and fourth table using exist() is performed on each inner loop. (these third and fourth tables are not even open)

When the values doesn't exist in these other tables, two separate functions are invoked to add a record to each these two other (closed during the looping process) tables. Everything works fine, except once the process finishes, these two other tables seem to have an index issue.... After the script/process terminates, when I open these third and fourth tables (viewing them with the default browse): All the records are there as expected, but the proportional scroll area is not "proportionate." ~ To fix this, I'm forced to manually rebuild the index for these tables using <tbl>.update_production_index()

I think the problem must have something to do with the exist() function, and or timing????

Note: If the ".not. exist()" evaluates to true, the invoked functions each open a table, set the table to enter mode, and adds a record & then closes the tables. I've also tried opening these other two tables (at the beginning of the script instead of within the function) and keeping them open during the entire process. While this does speed things up, this doesn't fix the issue. And a side effect is that keeping these 3'rd and 4'th tables open results in the .exist() method to not return expected results. (presumably as the table it's checking is already open?)

3 out of the 4 tables can be completely empty at the beginning of the process and this still happens. After the script terminates, the one "temp table" containing 1200 records populates these other tables CORRECTLY from anywhere from 600 to 1200 records.

Ironically, the largest table (which is looping to stay synchronized with the import table process as records are being inserted) is not afflicted with this issue. (Like the table used to import all the data, this table is also opened at the beginning of the script & kept open. ~ With enter.begin and enter.end used when necessary.) On these 3'rd and 4'th tables, there is no fetching whatsoever (and I've tried some fetch_firsts and other things etc.) Finally, I'm not opening any of these tables using a query.

All the data is correctly added to the table as expected via good old Xbasic hand written code... AND.....
Interestingly enough: As the outer loops progress, subsequent .not. exist() results are effected by prior entries to this third table earlier in the looping process via these same 2 functions that add records to tables #3 & #4 . ~ So obviously the .exist() is working properly on newly added records, even though the indexes are having trouble "keeping up" with the loop.

I've literally tried about everything I can think of, including renaming indexes. (3 of the 4 tables share a similar index "Kfb__", so I had thought that possibly Ted's statement supporting my "index collision" inquiry was the culprit. Turns out it isn't. The only thing I haven't tried is alternate methods of inserting data, other than using <tbl>.enter_begin() & <tbl>.enter_end()......

Why do I need to use <tbl>.update_production_index()???
(Is it required with

[martinwcole]

Re: Index Tip?

for one thing, you could try using batch begin and batch end - as the indexes are updated at the end of the process

also, when I moved a big app from 8 to 10, I had problems with exist() and instead used a query in the script, and that fixed it

also you could try using two different index names, although I doubt that is the issue

[SNusa]

Re: Index Tip?

for one thing, you could try using batch begin and batch end - as the indexes are updated at the end of the process

also, when I moved a big app from 8 to 10, I had problems with exist() and instead used a query in the script, and that fixed it

also you could try using two different index names, although I doubt that is the issue

Thank you.... Batch begin & end are already in place for the outer loops. I hadn't tried them inside the enter begin/end functions as there are only 4 lines of code adding 4 fields enclosed by open and close. I did try renaming the indexes to no avail. I'm thinking you may be correct that the exist() method is causing timing issues..... (I will try inserting a query instead. I'll bet that will correct the problem as you suggested!) ~ Thanks Martin.

By they way: Have you ever experienced index confusion (what I referred to as "index collision" with two open tables as Ted suggested? (Both indexes with identical names like "kfb__")? ~ Just kind of curious, as my index naming is standardized across multiple tables. I keep index names @ a max of 10 as suggested. (and 8 chars is "the goal")

One related question: When is it appropriate to use/need <tbl>.update_production_index()?

Index collision thread: http://msgboard.alphasoftware.com/al...l=1#post632969

[martinwcole]

Re: Index Tip?

Personally, I have never had issues with long names, anywhere in Alpha. The only time it becomes an issue, as far as I know, is if you don't ALWAYS include all the related tables when gtransferring an update, or you open it in a folder where the supporting files don't exist.

Also, using the same index on multiple open tables has never been an issue - in a set, typically ALL related tables are based on the same key field, and thus would have/need an index on the same field.

Example, I will sometimes, in a script, open 2 co[pies of the same table, along with other tables, using the same index name on all of them. Never been an issue.

As for exist(), I'm not sure what the problem was, (timing or other,) only that when I found that that was the problem in the script, when I opened a copy of the table, and querried, and got the data in that fashion, that that fixed it. I didn't have time to tery and figure out the root cause.

[SNusa]

Re: Index Tip?

Thanks Martin; (PS: just love your signature! )

I'm wondering whether it's "dangerous" to leave the code as it is, an perform a <tbl>.update_production_index() at the end of the script (for the effected 3'rd & 4'th tables, or is this just "asking for trouble?" ~ The other reason I chose to use exist() as opposed to qndx.records_get() with a query on table 3 & 4 is speed. I had assumed that using exist() was faster. (I'm probably totally wrong here, but I was thinking: "one line of code with exist() vs. code to open table & execute a query.)

One thing I haven't tried is querying a table to find whether a record already exists, and with the table still open: Immediately adding a record that is "outside" the queried result set. I suspect this should work fine, as enter_begin() & enter_end() are working with the open table pointer, and not the queried results/pointer??? ~ Just thinking out loud here.....

[DaveM]

Re: Index Tip?

As opposed:

I have had table names and field names trunkated to the 8 format. Not indexes or anything else though. I do not want that one again. I will comply to 8.3 for tables, sets, and anything named on disk. Just not hard and much easier than fixing a possible mess.

[martinwcole]

Re: Index Tip?

Where I ran into the exist() issue was in a very long, looping script, maybe 900 lines, for converting dbf data to a text format for submitting to Medicare. Interestingly enough, there was virtually no appreciable difference in speed. I doubt you will need to rebuild the indexes if you get the script working well. PS: the indexes won't rebuild if the tables are open.

If you are working with looping scripts with records in open tables in a form with embedded browses I recommend you read about get_cursor - like t=table.current(2) or t=table.get("invoicelineitems") - and t1=t.get_cursor()

Dave, I've been using Alpha since 1998, and have never had any issue with long table names, field names, etc. It only becomes an issue if you break the rules for moving data. Although in the early days indexes got truncated frequently.

Selwyn wrote a script for reindexing tables, which replaces the existing indexes with the original names, and any missing indexes that got lost by having a query or expression for a table that Alpha could not resolve. I run it in a Windows task scheduler in a maintenance adb at appropriate times, usually at night.

[DaveM]

Re: Index Tip?

Martin,

I really wish I could remember the circumstanes that caused truncation, but it was either v5 or v7. I just can't remember. I know it caused me a lot of grief.

[martinwcole]

Re: Index Tip?

I remember a time when some weird circumstance would cause field rules to get totall messed up, scrambled, etc. But I don't think the cause was ever discovered. But I haven't seen it for several years.

The main way that table and field names can get messed up is if you transfer the dbf's without the dd's - but the layouts will also not be updated either. And if there are fpt's, and you don't also transfer them, too, the fpt's will corrupt. There may be other circumstances that could cause the long field and table names to be truncated, but that is the only reason I am aware of.

Way back in the DOS days, we were limited to 8 characters - was a real real real pain.

But there could also been a period when Alpha was doing it in some weird circumstance. Most likely in V5 and 6.

[SNusa]

Re: Index Tip?

Thanks guys;

@Martin: I am familiar with the usage of get_cursor() which deals with timing refreshes of browse data while updating underlying data of visible browses. (v9 addition?) ~ Also, is the re-indexing script you mentioned located in the code archives here?

~Robert

[martinwcole]

Re: Index Tip?

will post here tomorrow - fixin to get some shuteye right now

[martinwcole]

Re: Index Tip?

here are 2 functions - one creates a copy of the indexes, and the other recreates them

whereas Selwyn used a text file in his script, I prefer storing them in a table, as it is more flexible, and using it I can elect to filter on specific tables and only rebuild the ones I want

first - create a table named zindexes:
Tablename Character 32 0
Indexnames Memo 10 0

second - examine the indexes of ALL tables, and be sure none are missing, and none are truncated (VERY IMPORTANT)

third - run the "indexes_create" function
also, anytime you add new tables and/or new indexes, or remove some of the indexes, you will need to re-run the "indexes_create" function again.

last - run the rebuild function to rebuild all indexes


FUNCTION indexes_create AS L ( )
on error goto errors
t=table.open("zindexes")
t.zap(.t.)
t.pack()
t.close()
t=table.open("zindexes")
tables=a5.Table_Enum()
n=w_count(tables,crlf())
for i=1 to n
temp=alltrim(word(tables,i,crlf()))
gpc=get_index_definitions(temp)
if gpc > ""
if alltrim(gpc)="__A5_RECOR|RECNO()||" .or. alltrim(gpc)="__A5_RECORD__|RECNO()" .or. alltrim(gpc)="__A5_RECORD__|RECNO()||" .or. alltrim(gpc)="__A5_RECORD__|RECNO()||"
else
t.enter_begin()
t.Tablename=temp
t.indexnames=gpc
t.enter_end(.t.)
end if
end if
next
t.close()
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end
END FUNCTION


'Date Created: 30-Mar-2009 08:18:36 PM
'Last Updated: 26-Dec-2012 02:49:32 AM
'Created By : martin cole
'Updated By : MARTIN
function indexes_rebuild as L()
dim temp as c
on error goto errors
dim shared is_shadowed as l
if a5.get_master_path() > ""
is_shadowed=.t.
else
is_shadowed=.f.
end if
if is_shadowed=.t.
mpath=a5.get_master_path() + chr(92)
end if
temp=""
datapath = a5.get_path() + chr(92)
dim tp as p
t1=toseconds(time())
t=table.open("zindexes")
n=t.records_get()
DIM GLOBAL pDlg1 as waitdialog
pDlg1.Set_Title("Please Wait")
pDlg1.Create(1,"Bounce")
pDlg1.Set_Message("Processing Indexes")
pDlg1.Set_Bottom_Message("")
m=0
t.fetch_first()
while .not. t.fetch_eof()
m=m + 1
pDlg1.Set_Bottom_Message("Processing " + alltrim(str(m)) + " of " + alltrim(str(n)))
temp1=alltrim(t.Tablename)
temp2=alltrim(t.Indexnames)
if table.in_use(temp1)
if temp1="zindexes" .or. temp1="dummy"
else
temp=temp + f_upper(temp1) + crlf()
end if
goto next1
end if
tp=create_indexes(temp1,temp2)
if tp.haserror
temp=temp + f_upper(temp1) + crlf()
end if
next1:
t.fetch_next()
end while
t.close()
pdlg1.close()
t2=toseconds(time())
t3=t2-t1
if temp=""
time2=toseconds(time())
else
time2=toseconds(time())
' ui_beep(UI_CRITICAL_BEEP); ui_yield();ui_beep(UI_CRITICAL_BEEP)
txt="Some tables were in use and could not be reindexed." + " " + temp
end if
indexes_rebuild=.t.
end
errors:
indexes_rebuild=.f.
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end
isopen:
temp=temp + f_upper(temp1) + crlf()
1=1
goto next1
end
end function