Walter Meester
HoogkarspelNetherlands
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Tip of the day - speeding up RI and Stored Procs
Hi all,
After a long night of drinking beer, it came up in mind that RI procs might speed up when you have allready openened the tables involved in relations with a given table. I did testing with code like this:
clear all
close data all
use parent table && Open the related table to speed up RI procs
use Childtable
nsec=Seconds()
replace all secondarykeyfield with somevalue
wait window STR(second()-nsec,7,2)
clear all
close data all
*use parent table && Prevent the parent table from loading
use Childtable
nsec=Seconds()
replace all secondarykeyfield with someothervalue
wait window STR(second()-nsec,7,2)
NOTE: You have to change "somevalue" with "someothervalue" because RI will not fire entirely when the value stays the same.
I did discover performance gains of more than 100% if you have the related tabled already open. This gain is due to the fact that if the table is not open already it has to open the table and load the buffers. By closing the table it releases the buffers again. If a the table is already open in another workarea it doesn't load and release the buffers each time the RI is called.
Of course the same applies to stored procs where tables are opened and closed. This method is especially usefull when inserting, deleting, and replacing large amounts of data.
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only