Optimize Your pb program

I. Process SQL statements
1. Buffer SQL statements
InApplicationProgramThe same group of SQL statements must be called repeatedly. In this case, you can set up a buffer for these SQL statements in the application.

To improve the execution performance.
By default, the buffer zone of the SQL statement is disabled and can be opened through the following statement:
Sqlcache = n indicates the number of SQL statements attached to the buffer (0 by default)

For example:
Dw_1.settransobject (sqlca)
Sqlca. dbparm = "sqlcache = 0"
Dw_1.retrieve ()

If you change "sqlcache = 0" in the above example to "sqlcache = 25", the execution efficiency of this sentence will be improved by about 1/5.

But pay attention to the buffer size, otherwise it will also affect the performance of program execution. Note: This method is applicable to data connected with ODBC and Oracle.

Library is very effective.

2. bind variables
See the following example:
Sqlca. dbparm = "disablebind = 1"
Insert into da_dh values ("1", "Henan 0 ")
Insert into da_dh values ("2", "Henan 1 ")
Insert into da_dh values ("3", "Henan 2 ")
Insert into da_dh values ("4", "Henan 3 ")
Insert into da_dh values ("5", "Henan 4 ")
Insert into da_dh values ("6", "Henan 5 ")

Bundle variables are not used here. If Pb is inserted, each SQL statement with a new value will be reprocessed. If you change the preceding example:

Sqlca. dbparm = "disablebind = 0"

Insert into da_dh values ("1", "Henan 0 ")
Insert into da_dh values ("2", "Henan 1 ")
Insert into da_dh values ("3", "Henan 2 ")
Insert into da_dh values ("4", "Henan 3 ")
Insert into da_dh values ("5", "Henan 4 ")
Insert into da_dh values ("6", "Henan 5 ")

ThenSystemThe insert statement is processed in the following format:
Insert into da_dh values (?,?)
Where "? "Is called a placeholder. The system performance will be enhanced.

3. Use a data window instead of an SQL statement

Generally, it is feasible to use data windows and SQL statements to obtain some data.

Sentences use different processing mechanisms, so they have different efficiency. For example, you can use SQL

Statement to retrieve all the data to one multi-row edit, or to a data window. If you use the first method:

First, define a cursor:

Declare cur cursor
Select "da_dh". "Hm"
From "da_dh ";

Then you can:

String stxt [], St
Int Li
Open cur
Do li = Li + 1
Fetch cur
Into: stxt [Li];
St = ST + stxt [Li] + "~ R ~ N"
Loop while stxt [Li] <> ""
Close cur;
Mle_1.txt = ST

You can also use the second method:

Dw_1.settransobject (sqlca)
Dw_1.retrieve ()

Use the PowerBuilder profiler tool to check. The time required for comparing the two methods is as follows:
Time required for the method (1% seconds)
SQL 100.9857
Data window 49.0133

Because the data window or datastore uses the standard embeddedCodeInstead of coding all at the same time

The translation execution speed is much faster than the interpretation execution speed. Therefore, you should try to use the data window and datastore during the development process...

When SQL statements must be used, you should also define them as stored procedures (especially in multi-user environments ).

Improve application performance.

 

2. Data Window Programming and execution
PB is the most commendable data window, which has the following features:
1. Multiple display modes.
2. Multiple Editing Methods.
3. Easy to use.
4. There are multiple forms of reports.
5. Screen scrolling.
6. Check the data validity.
7. The execution performance is significantly improved.
8. Less programming work.
9. Data crying can be updated within the data window.

Below, I will introduce some technologies used to improve the performance of the data window.

1. Reduce the number of database connections

Database Connection operations have a significant impact on the execution speed. Therefore, once connected to the database, you should try to maintain data consistency.

Database connection to reduce the number of database connections. PowerBuilder provides two functions to establish a connection between the data window and the transaction object:
Settrans ()
Settransobject ()
Settransobject () should be used whenever possible in the program, because the settrans () function calls retrieve () every time (),

After functions such as update (), you must perform database connection and disconnection operations.

2. Connection Between the drop-down data window and the table

For database servers, table join operations are a huge overhead, while the drop-down data provided by PowerBuilder

Window can replace table join operations in some cases.

For example, to display the user's phone number and name in the data window: If the table connection method is used

The SQL statement should be as follows:
Select "da_dh". "dhhm", "da_hth". "DWM"
From "da_hth", "da_dh"
Where ("da_hth". "dhhm" = "da_dh". "dhhm ")
The same program can be completed in the drop-down data window. We will not introduce it here. However, you should note which of the above two methods are used?

A type of data is faster and has a relationship with the table structure, number of tables, and connection methods. It should be analyzed in detail.

3. Share Data

In an application, some data needs to be frequently used. If you retrieve data from the database during each use, you must

A large amount of server resources and network resources are occupied. To reduce the overhead, you can retrieve the data only once on the client, and then

Allow other tasks to share the data.

For example, if there are two data windows, you need to retrieve the user's phone number from the third-party table, and this column uses the drop-down data window

If the phone number is retrieved every time, the performance is low. Therefore, you can create a separate number of phone numbers.

Data window. Each time a window is opened, the phone number is first retrieved to this data window, and the other two data windows

The drop-down data window of the phone number can share the data in this data window.

Write the following program in the window open event:

Dw_1.settransobject (sqlca)
Dw_2.settransobject (sqlca)
Dw_3.settransobject (sqlca)
// Retrieve dw_1
Dw_1.retrieve ()

// Share dw_1 with the drop-down data window of dw_2
Datawindowchild Child1
Dw_2.getchild ('dhhm ', Child1)
Child1.settransobject (sqlca)
Dw_1.20.data (Child1)

// Share dw_1 with the drop-down data window of dw_2
Datawindowchild child2
Dw_3.getchild ('dhhm ', child2)
Child2.settransobject (sqlca)
Dw_1.20.data (Child1)
This method avoids physical data copying between data windows, thus reducing the overhead of space and improving the application process.

Overall sequential performance.

4. Data Copying between data windows

If you want to share data between data windows, use the partition data () function whenever possible. However, the partition data () function and

It is not a physical copy of data between data windows. If you want to operate the data while displaying the data, you need to perform the data

.

For example, copy the row selected in dw_1 to dw_2:

Program in window open events:
Dw_1.settransobject (sqlca)
Dw_2.settransobject (sqlca)
Dw_1.retrieve ()

Write the following program in the rowfocuschanged event of the data window dw_1:
Long lR
LR = dw_1.selectrow (0, false)
LR = dw_1.getrow ()
LR = dw_1.selectrow (LR, true)

There are two ways to copy data from dw_1 to dw_2:

First: Program in the clicked event of the "copy" button
Long lR
LR = dw_1.getselectedrow (0)
Dw_1.rowscopy (LR, LR, primary !, Dw_2, 100, primary !)
Execute the program and use PowerBuilder profiler to obtain the required time as 1.7034 (1% seconds)

Type 2: Program in the clicked event of the "copy" button
Dw_2.object.data = da_1.object.data.selected
Execute the program and use PowerBuilder profiler to obtain the required time as 0.8062 (1% seconds)

5. Access and modify data window attributes

A. Access to data window properties
You can use the following methods to access the properties of a data window in a program:
A1. access using DOT expressions
A2. Application of Multiple independent describe () function access
A3. use only one describe () function and use compound parameters to access multiple attributes.
The third method above, usually the first method is the slowest, and the third method is the fastest.

For example:
In the window open event
Dw_1.settransobject (sqlca)
Dw_1.retrieve ()

Method 1:
Programming in the clicked event of the Search button.

String dx, Dy, DH, DW
DX = dw_1.object.da_dh.x
DY = dw_1.object.da_dh.y

DX = dw_1.object.da_dh.height
DY = dw_1.object.da_dh.width

St_1.text = dx + "," + dy + "," + DH + "," + DW

Method 2:
String dx, Dy, DH, DW
DX = dw_1.describe ("da_dh.x ")
DX = dw_1.describe ("da_dh.y ")
DX = dw_1.describe ("da_dh.height ")
DX = dw_1.describe ("da_dh.width ")

St_1.text = dx + "," + dy + "," + DH + "," + DW

Method 3:
String dx, Dy, DH, DW
St_1.text = dw_1.describe ("da_dh.x" + "a_dh.y" + "da_dh.height" + "da_dh.width ")

Experiments show that the third method is the fastest, but the program has the worst readability.

B. modify data window attributes
You can modify the properties of a data window in a program by using the following methods:

A1. use the dot expression to modify
A2. use multiple independent modify () function access
A3. only one modify () function is used, and multiple attributes are accessed using compound parameters.

The third method above, usually the first method is the slowest, and the third method is the fastest.

For example:
In the window open event
Dw_1.settransobject (sqlca)
Dw_1.retrieve ()

Method 1:
Programming in the clicked event of the Search button.

Dw_1.setredraw (false)
Dw_1.object.da_dh.x = 18
Dw_1.object.da_dh.y = 16
Dw_1.object.da_dh.height = 100
Dw_1. object.da_dh.width = 200
Dw_1.setredraw (true)
St_1.text = dx + "," + dy + "," + DH + "," + DW

Method 2:
Dw_1.setredraw (false)
Dw_1.modify ("da_dh.x = 18 ")
Dw_1.modify ("da_dh.y = 16 ")
Dw_1.modify ("da_dh.height = 100 ")
Dw_1.modify ("da_dh.width = 200 ")
Dw_1.setredraw (true)

Method 3:
Dw_1.modify ("da_dh.x = 18" + "da_dh.y = 16" + "da_dh.height = 100" + "da_dh.width = 200 ")

Experiments show that the third method is the fastest, but the program has the worst readability.
Note that the setredraw () function is used in method 1 and method 2 to Reduce screen re-painting. Otherwise, the execution speed will

Serious decline.

6. Data access in the data window

In programs, data in the data window needs to be dynamically modified. For this, Pb provides a variety of methods, various methods in nature.

There may be some differences.

A. Data access in the data window

Objective: To store the phone numbers in the data window in an array.
Compare the following two methods.
Method 1:
String da_dh []
Long LL, I
LL = dw_1.rowcount ()
For I = ll to 1 Stet-1
Da_dh [I] = dw_1.getitemstring (I, "dhhm ")
Next
Method 2:
String da_dh []
Da_dh [] = dw_1.object.dhhm.current

The test shows that the second method is nearly doubled faster than the first method. The larger the data volume, the more obvious the difference.

B. Data modification in the data window

Purpose: To modify the value of the phone number column in the data window.
Compare the following two methods.
Method 1:
Dw_1.setitem (I, "dhhm", l_name)
Method 2:
Dw_1.object.name [I] = l_name
The test shows that the second method is nearly doubled faster than the first method. The larger the data volume, the more obvious the difference.

7. Impact of data window events on Performance

Program code should be minimized for events that often occur in data window control, especially the following events:
A. itemchanged
B. editchanged
C. itemfocuschanged
D. pbm_dwnkey
E. rowfocuschanged
F. retrieverow
Any processing program in these events will reduce the processing speed of the application. Therefore, we should minimize

When necessary, you can consider placing only important code in these events, and placing the remaining code in one passed event.

Medium.

For example, if you need to use the rowfocuschanged event of the data window, you can define a user event for the data window.

"Ue_re", and write the following code in the rowfocuschanged event of the data window:

Parent. postevent ("ue_re ")
Write the corresponding program code in the ue_re event, for example:
String code
Dw_1.selectrow (0, false)
Dw_1.selectrow (rownum, true)
Code = getitemstring (dw_1, rownum, "dhhm ")
Dw_2.retrieve (CODE)

In addition, to obtain the current row number, use the currentrow variable of the data window as much as possible, instead of the getrow () function.

8. column names and column numbers in the data window
Access a column in the data window. You can use the column name or the column number,
For example, use the column number:
Dw_1.object.data [ll_row, 2]
Dw_1.getitemstring (3, 2)
A column name is used to indicate a column:
Dw_1.object.article_text [ll_row]
Dw_1.getitemstring (3, "dhhm ")
Dw_1.setitem (3, "date", 2-03-31)
For the above two methods, if only one query is performed, the speed is not much different. For example, if you need to access a column in the data window cyclically, the speed difference is obvious, and the first method is faster, but the program is less readable.
However, when using functions (such as getitem () and setitem (), the execution speed is usually not very different.
9. Computing domain
The computing domain of the data window will affect the data operation performance. If the data window contains many complex computing domains, the execution speed of the data window will be seriously affected.