0

I have a .dbf file from an old clipper program here in the company an I added it to a data dictionary in ADS 12 Evaluation, with the associated index files of the dbf (.ntx)

The fields are :

account,type,billmo,bill,kwhr,amount,pay,present,previous,forex,fpca,pfcharge,demand,demandchg,rebate,paydate,dummy,raffle,enerchg,others,pf,adj,datebill,billstat,insert,tendered,meter,adj2,lifeline,e_vat,w_tax,add_chg,restruc,mtrr,senior

and containing 300k+ rows.

The .dbf contains 4 .ntx (index) files, and one of them has the expression account+billmo.

When i execute the a simple query:

SELECT * FROM TABLE.DBF WHERE account='000000...' AND billmo = '2016...'

It takes like 16 seconds or more. Also on deleting:

DELETE FROM TABLE.DBF WHERE account='...' AND billmo = '...'

It is still the same.

I don't know how to use index (.ntx) on the query, i have search the google and tried something like

SELECT * FROM TABLE.DBF use index TABLE1 WHERE account = '...' AND billmo = '...'

But it doesnt work.

John Pangilinan
  • 953
  • 1
  • 8
  • 25

1 Answers1

2

Some hints:

  1. NTX (and IDX) files have to be manually added to the table (as opposed to CDX and ADI index files which are automatically detected as they have the same basename as the table). In ARC32 when you right click the table there is an "Add Existing Index Files..." option.

  2. You can check whether ARC32 actually uses any indexes by showing the execution plan (The button with one big and two small tables in the toolbar of the SQL Utility window). If there is a red circle and it says "TABLE SCAN" then it will not use an index.

enter image description here

  1. If the index is on FIELD1+FIELD2 you have to actually query it like that:

    SELECT * FROM TABLE.DBF WHERE account + billmo = '000000...' + '2016...'

Here there is no red circle and it says "AOF SCAN" instead of "TABLE SCAN" which means that ADS is using the index in an "Advantage Optimized Filter" scan instead of reading through the whole table.

  1. As long as you only use the data to do some reporting and don't want to write it back to your legacy application it might make sense to migrate it (maybe with a periodic job) to a better database format (ADT + ADI with correct indexes like 'account;billmo' [note the semicolon] would already be quite a step up).

enter image description here

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
  • Hi, I already have add the associated index file to the table and also i have tried the query you suggested with the `ACCOUNT+BILLMO` still it shows TABLE SCAN with the warning `Restriction is not optimized`. I have tried to reindex with the ARC32 but still the same warning. I can't migrate the table because it is currently in use by other systems here. – John Pangilinan Oct 19 '16 at 01:02
  • Have you tried individual indexes on each column? Also as I already wrote: When the index is not loaded the SQL engine can't take advantage (no pun intended) of it. – Jens Mühlenhoff Oct 19 '16 at 07:51
  • Yes, i only tried the `account` column. Still the same. How can i know if the index was loaded or not? I already added it to the table. – John Pangilinan Oct 19 '16 at 08:09
  • I'm afraid that's as far as I can help you. You might want to post in the SAP forum: https://go.sap.com/community/tag.html?id=67838200100800005437 – Jens Mühlenhoff Oct 19 '16 at 15:07
  • Hi, i know what's the problem now. It is fast when no one is using the table. The Old system was using the table, and when many use it, it starts to get slow. I can get data fast if no one is using but still the sql utility says it doesn't use any indexes. Can i ask what TableType and DBFTableType did you use? Because i can't actually use the SQL utility when table type is NTX, only in ADT. – John Pangilinan Oct 20 '16 at 01:08
  • It seems I accidently created a CDX index. I have redone my test setup and I now get the error message "poQuery: The TAdsQuery component does not support the NTX table". So it seems you can't use SQL on NTX tables with NTX indexes. You can query the table in CDX mode, but then no index is used. – Jens Mühlenhoff Oct 20 '16 at 11:18
  • The other method is to use classic table operations. You'd have to write your own application then. – Jens Mühlenhoff Oct 20 '16 at 11:37