Dan, If you need to occasionally print a report on 14 years of data it would be simple to project a permanent history table for say all but the last five years delete those records from the ARDETAIL table for the report create a view: CREATE VIEW ARALLDETAIL (columnlist) AS + select (columnlist) from ARDETAIL + UNION ALL + select (columnlist) from ARHISTORY
This way you would only have to substitute the view name for the table name in the appropriate report. This would reduce the size of the ARDETAIL file. As an accountant I am a little hard pressed to come up with a reason for the need to query 14 years of data Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ----- Original Message ---- From: Dan <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, February 11, 2010 10:46:47 AM Subject: [RBASE-L] - Re: Speed issues Jim, This table spans the last 14 years of invoices. We do use the data, though with a lot of re-writing, we could use less. But till lately it was never an issue. Just started when we switched to Turbo. I Agree with the Not Null constraint, and will implement that. And yes, I will be setting up both Invno, as Primary key, and custno as a Foreign key referencing the ARMAIN table (customer table) where Custno originates and is Primary. I really wish I had more than 2-4 hours a week to work on programmin and Maint of database. Basically if it aint broke don't fix it.. That is why we are always afraid of each new iteration or R:Base. We seem to always get caught with our pants down.... At 11:12 AM 2/11/2010, you wrote: >Dan, > >How many years does this table span? >Do you need to keep all these records in an active table versus >moving some records to a history table? >it seems that CUSTNO, INVNO, AND IDTRANS should have a NOT NULL >constraint applied. > > CUSTNO should have a FOREIGN KEY reference to CUSTNO in the > "Customer table" where CUSTNO shold be a primary key. >Jim Bentley >American Celiac Society >[email protected] >tel: 1-504-737-3293 > > > >----- Original Message ---- >From: Dan <[email protected]> >To: RBASE-L Mailing List <[email protected]> >Sent: Thu, February 11, 2010 8:18:38 AM >Subject: [RBASE-L] - Re: Speed issues > >sure, >No Triggers, >No functions, >3 indexes Custno, Invno, IDTrans (all unique numbers) > 1 rule: Ardetail.INVNO IS NOT NULL AND NOT Ardetail.INVNO IN ( > SELECT INVNO FROM Ardetail #T1 WHERE #T1.INVNO = Ardetail.INVNO ) > > Table: Ardetail No Lock(s) > >No. Column Name Attributes >--- ------------------ ------------------------------------------------------ > 1 CUSTNO Type : TEXT 6 > Index : SINGLE-COLUMN > 2 INVNO Type : TEXT 12 > Index : SINGLE-COLUMN > 3 SDATE Type : DATE > 4 IDTRANS Type : TEXT 20 > Index : SINGLE-COLUMN > 5 INVAMT Type : CURRENCY > Default: 0 > 6 INVCUR Type : CURRENCY > Default: 0 > 7 PAYCUR Type : CURRENCY > Default: 0 > 8 PAYAMT Type : CURRENCY > Default: 0 > 9 CHKNO Type : TEXT 20 > 10 CHKDATE Type : DATE > 11 CREDIT Type : CURRENCY > Default: 0 > 12 SVCHARGE Type : CURRENCY > Default: 0 > 13 CURBAL Type : CURRENCY > Default: 0 > 14 AFTER30 Type : CURRENCY > Default: 0 > 15 AFTER60 Type : CURRENCY > Default: 0 > 16 AFTER90 Type : CURRENCY > Default: 0 > 17 OVER120 Type : CURRENCY > Default: 0 > 18 SCDROPPED Type : CURRENCY > Default: 0 > 19 TOTSVCH Type : CURRENCY > Default: 0 > 20 INVBAL Type : CURRENCY > Default: 0 > 21 MONTHNO Type : INTEGER > 22 SC_OK Type : TEXT 1 > Default: Y > 23 Posted Type : TEXT 1 > 24 SYEAR Type : INTEGER > 25 SPERIOD Type : INTEGER > 26 batchno Type : INTEGER > Current number of rows: 100476 > > > > >At 09:07 AM 2/11/2010, you wrote: > > Please post the structure of your table, including a list of > indexes, rules, triggers, and constraints that apply. > > -- > > Larry > > >
