At 03:48 PM 3/2/2004 -0800, Victor Timmons wrote:
I would like some tips on how to speedup rbase when it running on a network.
Victor,
Here are a few more tips in addition to the suggestions posted earlier by fellow R:BASE users/developers:
-- 01. Use the most current version of R:BASE -- (I know you are using the latest & greatest version!)
R:BASE Technologies is continually making speed improvements, and each new release is faster than the previous one. For example, building indexes in R:BASE 6.5/7.x is significantly faster than in previous versions.
You can even PACK the INDEXes faster in 6.5++, even in a Multi-User environment.
PACK INDEX
or
PACK INDEX FOR TableName-- 02. Think globally instead of row by row -- This is one of the best ways to achieve some significant speed improvements. Instead of doing row-by-row processing using a DECLARE CURSOR, try using a single UPDATE or INSERT command, which will take advantage of the SQL select capabilities of R:BASE.
For example, suppose you want to create a table to hold information about how many of each product a customer has purchased. The most logical approach is to set up a cursor to go through the detail table and count the rows for each customer/product combination and then insert that data into the new table. On a table with 3500 rows, processing all the rows may take few minutes. By replacing the DECLARE CURSOR loop with a single insert command, the time required to process all the rows may take 75% less.
Compare the two pieces of code:
DECLARE c1 CURSOR FOR SELECT CustID,PartNo FROM CPProdDet +
GROUP BY CustID,PartNo
OPEN c1
FETCH c1 INTO +
vCustID INDIC ivCustID, +
vPartNo INDIC ivPartNo
WHILE SQLCODE < > 100 THEN
SELECT COUNT(*) INTO vCount FROM CPProdDet WHERE +
CustID = .vCustID AND PartNo =.vPartNo
INSERT INTO CustProd (CustID,PartNo,Copies) VALUES +
(.vCustID,.vPartNo,.vCount)
FETCH c1 INTO +
vCustID INDIC ivCustID, +
vPartNo INDIC ivPartNo
ENDWHILE
DROP CURSOR c1vs.
INSERT INTO CustProd (CustID,PartNo,Copies) +
SELECT CustID,PartNo,Count(*) +
FROM CPProdDet GROUP BY CustID,PartNo-- 03. Be Creative -- R:BASE almost always offers two or more ways of doing something. Look for an alternative method for accomplishing a task - it might just be considerably faster.
-- 04. Change the environment -- The R:BASE environment settings that have the most effect on performance are SET MESSAGES.
SET MESSAGES OFF eliminates the time required for screen display of messages. This is particularly noticeable if you are doing repetitive UPDATE or INSERT commands.
-- 05. Reduce Number of Expressions in Reports -- Because all report expressions are processed for each row of data read from the reports driving table or view, reducing the number of expressions in a report increases the printing speed of a report.
-- 06. Abbreviate commands to four characters -- R:BASE parses command lines into 4-byte tokens - the fewer tokens to read, the faster the command executes.
-- 07. Combine commands whenever possible -- Many R:BASE commands allow access to more than one column or variable and don't require using separate commands for each. This reduces the number of times R:BASE must parse and read a command. The fewer commands to read, the faster R:BASE executes.
-- 08. Use correlated sub-selects or multi-table selects instead -- of sub-selects
A multi-table select uses indexes to join the tables. A correlated sub-select uses indexes to restrict the list of values to be compared within the sub-select. A plain sub-select will not use indexes. Each row of data from the main query must be compared to every row selected by the sub-select.
-- 09. Try using manual optimization (MANOPT) -- With MANOPT set ON, R:BASE joins tables in the order they are listed in the FROM clause of the SELECT command. The R:BASE optimizer generally picks the most efficient way to join the tables. You have the option in R:BASE of manually specifying the order by using the MANOPT setting. Doing so might improve the performance of your multi-table selects and views.
-- 10. Practice smart indexing -- In a WHERE clause, R:BASE uses only one index (except when joining tables). If more than one indexed column is referenced in a WHERE clause, R:BASE looks for the one that places the greatest restriction on the number of rows returned. It first looks for an indexed column using =, then checks for IS NULL, and finally looks at BETWEEN. It uses the first index it finds with the most restrictive operator.
-- 11. Review your code - there is ALWAYS room for improvement! -- Did you ever write a piece of code three months ago, six months ago, or perhaps longer ago still and then realize that you could have done a better job? As you learn more about R:BASE, your data, and how people are using the database and your application when you go live, you'll easily recognize areas in your code you can modify for performance improvements. You'll want to incorporate new techniques and new features from the current version of R:BASE.
Hope that helps!
Very Best R:egards,
Razzak.
