PDA

View Full Version : Can we control loading of calc fields?


ABC123

Howard G. Cornett
08-23-2004, 12:24 PM
I have a WAS project with 3 calculated fields (there will be many more). My test database is over 100,000 records and the real thing will continue to grow larger and larger. These calculated fields seem to need to be calculated for the entire table before the page loads. This slows the inital page loading to an unacceptable crawl. Since I am only displaying the first 10 records of the table in the beginning, is there any way to limit the calculations for the calc fields to only these records that the WAS is actually going to display? And subsequently, to load them only for the records that will show on the page? Otherwise, how will we handle lots of calc fields in large databases? I am open to any and all ideas.

Thanks,
Howard

CALocklin
08-23-2004, 01:11 PM
Howard,

Where are the calc fields? On your form or in the table? (Maybe that should be obvious since it's the WAS but I haven't actively used the WAS in the last couple months or so.)

Howard G. Cornett
08-23-2004, 02:56 PM
Neither, they are on a grid component.

Howard

Selwyn Rabins
08-25-2004, 02:23 PM
New Page 1






HowardThe number of records in the table is not really that
important in determining how long it will take for a page to load. Nor
are the number of calc fields that important.
Basically, if you have the proper indexes in place so that whatever
query is active when a page loads executes quickly, the table size
should be largely irrelevant. For example, say that you have a table of
100,000 customer names, and your grid is defined with a query of:
state="MA". as long as you have an index on the state field, then query
should be fine.
you can test this yourself in the interactive window. for example:
t =
table.open("customers")
query.filter = "state = " + quote("MA")
i = t.query_create()
t.close()

If that takes place quickly in the Interactive window, then the grid
should also load quickly. If this does not execute quickly, then the
Grid will also be slow. The solution, of course, is to add an index on
the 'state' field.
Another example: Say that you define your grid to sort on the
'company' field, but you don't have an index on the 'company' field.
Then this code in the Interactive window will be slow, as will the Grid
component:
t =
table.open("customers")
t.order("company")
t.close()
Again, the solution is simple. Just add an index on the Company
field.
One mistake that I found another making was this: He had designed a
.a5w page that contained a component. He had then deleted all of the
place holders for grid output on the page (thinking that he was deleting
the component from the page), and had then added another component to
the page. He then deleted the placeholders for this second component
(thinking again that he was actually deleting the component), and added
another component to the page. However, if he had switched to the Source
view, he would have seen that the xbasic to load and run each component
was still in the page. So the page was actually running 3 components
(although only html from the last component was displayed on the page).
The sample code below shows how you can test the speed of the query
that your Grid is doing. Copy the code below (shown in blue and red).
Edit your component, and switch to the Xbasic view. Copy the code that
looks like the code shown in Red below and replace the code shown in red
with your own code. Then run this script. how long does it take for the
query to run?




dim t1 as t
t1 = now()

delete tmpl

tmpl.DBF.type =
"Table"
tmpl.DBF.table_name = "c:\program files\a5v5\samples\alphasports\Customer.Dbf"
tmpl.DBF.filter = ""
tmpl.DBF.order = ""
tmpl.DBF.flags = ""

tmpl.DBF.fieldmap = ""%str%
CUSTOMER_ID,C,8,0=Customer_id
FIRSTNAME,C,20,0=Firstname
LASTNAME,C,20,0=Lastname
COMPANY,C,32,0=Company
PHONE,C,20,0=Phone
FAX,C,20,0=Fax
BILL_ADDRESS_1,C,40,0=Bill_address_1
BILL_ADDRESS_2,C,40,0=Bill_address_2
BILL_CITY,C,20,0=Bill_city
BILL_STATE_REGION,C,20,0=Bill_state_region
BILL_POSTAL_CODE,C,10,0=Bill_postal_code
BILL_COUNTRY,C,20,0=Bill_country
SHIP_ADDRESS_1,C,40,0=Ship_address_1
SHIP_ADDRESS_2,C,40,0=Ship_address_2
SHIP_CITY,C,20,0=Ship_city
SHIP_STATE_REGION,C,20,0=Ship_state_region
SHIP_POSTAL_CODE,C,10,0=Ship_postal_code
SHIP_COUNTRY,C,20,0=Ship_country
SHIP_SAME,L,1,0=Ship_same
EMAIL,C,60,0=Email
NOTES,M,10,0=Notes
TIME,T,17,0=Time
fn,c,255,0=alltrim(firstname) + " " + lastname
%str%



map_string = tmpl.DBF.fieldmap
dim map_unbound as c
map_unbound = *for_each(x,word(x,1,"=") + "=null_value()",map_string)

dim alias as c = "Table"
dim tm as p
tm = table.mapper_create(alias,map_unbound)
tbl = tm.local_open(tmpl.DBF.table_name)
tm.mapper_change(map_string,tbl)

query.filter = "firstname = " + quote("")
query.order = "lastname"
tm.query_create()

t2 = now()
ui_msg_box("","Time taken: " + (t2-t1))

tm.close()