Hello everyone,
I have a question about indexing. I have just a little of experience,
so be patient with me.:)
First of all I'm using Universe 10.0.4.0 and SB 5.2 on a Windows 2003
Server on a Dual-Core Opteron Server and 4Gb of RAM & SCSI drives
I wanted to simplify (just a little) my database, to improve the
performance. with the changes I made, i got to the point that I have
to compare 2 tables:
TABLE1
DetID - KeyField
InvID -
InvDt -
LinIt -
ItmType
ItmID -
QtyReq -
TABLE2
DetID - KeyField
PurID -
PurDt -
LinIt -
ItmID -
ItmType -
QtyPur -
QtyAva -
What I want to do is to scan the first table/file (TABLE1), substract
inventory ammounts and create a third file (LOG)with the following:
TABLE3
DetID
InvID
InvLI
PurID
PurLI
QtyDis
On TABLE1 I have around 4,000,000 records; and on TABLE2 i have around 60,000.
I have been testing on how to create index. CREATE.INDEX /
UPDATE.INDEX. But after looking on the manuals and online (everything
points into here :)) i don't know how that works....
How I can have access to the index?
Is the index applied when i use a SELECT command?
I created the index like this:
CREATE.INDEX TABLE1 InvID InvDt
CREATE.INDEX TABLE2 PurID ItmType PurDt QtyAva
and I updated the indexes like this:
UPDATE.INDEX TABLE1 ALL
UPDATE.INDEX TABLE2 ALL
mmm ,mmm now that I'm writing this...i'm thinking... do I need to
create an index for the third file? (probably an obvious question).
By the way I'm running this from a basic program, something like this:
(I didn't copy/paste the code, but it's something similar....)
.........
MyListFromTable1 = {I get this variable from after reading from my
&SAVEDLISTS& file}
QtyOfItemsOnList1 = dcount(MyListFromTable1,CHAR(254))
for i=1 to QtyOfItemsOnList1
c_ItemID = MyListFromTable1<i>
read RegItmTable1 from TABLE1_file,c_ItemID else
ErrMsg = "Unable to read [":c_ItemID:"]"
goto ExtitSub
end
InvID = RegItmTable1<1>
InvDt = RegItmTable1<2>
InvLI = RegItmTable1<3>
InvIT = RegItmTable1<4>
InvQT = RegItmTable1<6>
CmdToFindAvail = \SELECT TABLE2 WITH ItmID = "\:InvIT:\" AND WITH
QtyAva > 0 and PurDt <= "\:oconv(InvDt,'D4\'):\" BY PurDt BY PurID BY
LinIt\
EXECUTE CmdToFindAvail
....
....
......
next i
Thanks!
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/