VIEW speed? TPS using a Filter on Primary Key

1

Hi,

Trying to solve some speed issues, using TPS

I enabled the driver trace to debug the following VIEW code
The “next(ThisView)” generates a next in the driver trace for every record in the file.
Using upper or not makes no difference.

What is missing with the view code here as the file has a primary key and the value is loaded in the filter.

thanks for any info

Johan

! code

      open(thisview)
      ThisView{prop:filter} = 'UPPER(ITE:ITEM_ISN) = <39>' & Upper(ite:item_isn) & '<39>'
      set(ThisView)                                                 
      next(ThisView)                                                
      if error()
         ds_OutputDebugString('TVCweb testview error2=:' & error() & format(clock(),@t06))  
      end


! view decleration 

ThisView            &View
View:item       View(item)
                      Project(ite:item_isn)
                      Project(ite:item_desc)
                      Project(ite:item_amt)
                      Project(ite:item_qty)
                      Project(ite:item_gr_total)
                    END ! of ThisView

! file layout 
item            FILE,DRIVER('TOPSPEED'),NAME(glo:item_f),PRE(ite),CREATE
item_isn_key      KEY(ite:item_isn),PRIMARY
account_isn_key   KEY(ite:account_isn,ite:invoice_isn,ite:item_date,ite:item_time),DUP,NOCASE
item_type_key     KEY(ite:item_type,ite:item_date),DUP,NOCASE
order_isn_key     KEY(ite:order_isn,ite:item_isn),NOCASE,OPT
cons_isn_key      KEY(ite:cons_isn),DUP,NOCASE
item_desc_key     KEY(ite:item_desc),DUP,NOCASE
invoice_isn_key   KEY(ite:invoice_isn,ite:item_isn),DUP,NOCASE
piece_isn_key     KEY(ite:piece_isn),DUP,NOCASE
acc_nr_key        KEY(ite:acc_nr,ite:invoice_isn),DUP,NOCASE
print_key         KEY(ite:invoice_isn,-ite:item_seq_nr,ite:item_desc),DUP,NOCASE
ordergrp_isn_key  KEY(ite:ordergrp_isn),DUP,NOCASE
invoicing_key     KEY(ite:invoice_isn,ite:account_isn,ite:item_grp_isn,-ite:item_DC,ite:item_date,ite:item_time),DUP,NOCASE !'D' comes before 'C' ----- see notes
integ_isn_key     KEY(ite:integ_isn),DUP,NOCASE
stockper_isn_key  KEY(ite:stockper_isn,ite:x_isn),DUP,NOCASE
gp_completed_key  KEY(ite:gp_completed,ite:item_date),DUP,NOCASE
wlable_isn_key    KEY(ite:item_type,ite:wlable_isn,ite:wtype_isn),DUP,NOCASE
stock_eff_key     KEY(ite:item_type,ite:wlable_isn,ite:wtype_isn,ite:bottle_size,ite:item_date),DUP,NOCASE
record            RECORD
item_isn            STRING(20)
item_date           LONG
item_time           LONG
and lots more fields............

…

2

Are you fetching this data across a network or from the workstation hard drive?

The workstation hard drive (Slow spin disk or fast NVMe disk) and motherboard (bus design and plugin boards) and CPU (cache and available instruction sets) and RAM (DDR4 or DDR5) will all affect the speed the TPS view is built, as will the CPU Quantum setting in Windows (24H2 Energy Saver mode which requires no reboot unlike the registry setting which alters the Quantum seen in PowerCfg Powermode) and the workload (other running apps) and Antivirus (is it scanning the TPS file and sending to the Cloud).

Oh, and the number of records in the TPS file can be a factor. Lots of records or large TPS file sizes can slow things down a bit.

3

You could try code like below that is what you find the Legacy templates generate:

  ThisView{prop:filter} = 'UPPER(ITE:ITEM_ISN) = <39>' & Upper(ite:item_isn) & '<39>'
  Set(Ite:item_isn_key)    ! Tell view the Order by Key
  open(thisview) 
      ! set(ThisView)      moved up and use file                                           
  next(ThisView)

Read the Help on View Set Filter etc

4

Johan, try sending the prop:filter to debugview immediately after setting it, just so upu can see if it looks ok.
I could see an issue if the right-side contained a quote.

Also, make sure the record is BINDed. If it isnt, then the gilter would fail.

BIND(ite:record)

5

if you add NOCASE to your primary key does it make a difference?

I am not sure how smart the query processor in the tps driver is but at the moment you have no key that can be used by the filter so it needs to read the whole file.

2 Likes
6

Good catch!

I would guess the ISN is all Numbers and special characters (no letters), if so first try removing the UPPER(). Change it to CLIP() so no explicit trailing spaces.

  ThisView{prop:filter} = 'ITE:ITEM_ISN = <39>' & clip(ite:item_isn) & '<39>'
  Set(Ite:item_isn_key)

If you don’t need features of the VIEW this can be done with Set(key,key):

CLEAR(ite:record,-1)
ITE:ITEM_ISN = desired_value
Set(Ite:item_isn_key,Ite:item_isn_key)                                           
next(item)

But that’s a primary key so GET(File,Key) makes the most sense. But I’d guess you were testing the View.

7

Add ThisView{Prop:Buffer} = x (50?)