I have a vortex script that compares records in a temp table to records in a production table. It steps through each temp table row and searches the prod table for a matching key. If found it then compares the columns and updates the changed columns. The problem lies in that occassionally the search in the production table appears to get caught in a loop and one time I caught it searching for the same record for almost 7 hours. The table has the searched column indexed as shown below. Any ideas why it would get into this state?? When it has I have gone to the database with ltest and seen alternating I and i for the entire period until I kill the app.
Texis Version 03.01.971289085(20001011) Copyright (c) 1988-2000 Thunderstone EPI
NAME TBNAME FNAME COLLSEQ TYPE NON_UNIQUE FIELDS
------------+------------+------------+------------+------------+------------+------------+
xuseridrec books xuseridrec A B 01 USER_ID\USER_REC
What is the exact SQL you're using for the outer SELECT loop from the temp table, any inner SELECTs, and any updates? Are there any indexes on the temp table?
Are there any other processes running which might update/insert/delete from either the temp or production table during this process (regardless of whether it's the affected row(s) or not)?
Try setting bubble=0 before this script starts, eg.:
<SQL "set bubble=0"></SQL>
This pre-loads index values before returning results, and may avoid this issue.
