Shulou(Shulou.com)06/03 Report--

Many algorithms produce intermediate results that affect performance, especially when the data volume is large. Programmatic cursors are often used to avoid data landing in this case, as illustrated below.

first half sort

Calculation target: the order table has been sorted by time. It is necessary to remove duplicates of data by date and product, and then count the number of records.

Because the result set after de-duplication is large, the memory cannot fit, so groupx is generally used to de-duplicate. The SPL script is as follows:

A

1=file("sales.btx").cursor@b()/read order records from set file 2=A1.groupx(date(ORDERDATE),PRODUCTID)/de-duplicate by date and product group3 =A2.skip()/count cursors

However, groupx causes data to fall, so performance is not high.

To keep the intermediate result of de-duplication from landing, you can first generate a program cursor. The distinctProduct.dfx script is as follows:

AB1=file("sales.btx").cursor@b()

2for A1;date(ORDERDATE)=A2.id(PRODUCTID)3

return B2

A2: Revolving Order Form. Since the data is sorted by time, take batches with the same date at a time.

B2: Weigh by product. Note that although the full amount of data is large, the number of days is relatively small, and the memory can be put down, so id is used here.

B3: Return the weight loss results for this batch. Note that the program cursor does not return all the data at once, but after each loop, waits for the caller's request, and if the caller continues to ask for data, the program cursor will go through the next loop without landing the data.

The main program can call the program cursor through the cursor function, and the usage is similar to that of ordinary cursors:

A

1=cursor("distinctProduct.dfx")/caller cursor2 =A1.skip()

As you can see, program cursors can reduce data landings, thereby improving computational performance.

set operations

Database set operations to sort first, if the amount of data is too large, sorting will take a lot of time, delayed output results. In this case, it is appropriate to use procedural cursors to generate ordered intermediate result sets that do not fall to the ground, thereby achieving fast output.

For example, two tables with the same structure: callRecordA and callRecordB. Two tables have indexes established in the time field callTime, with tens of thousands of data per second. Now, union operation is performed on the data of January 1, 2015, and the first 500 items need to be quickly output (for example, quickly presented on the report).

This time the program cursor and the calling code are written in the same script, as follows:

ABC1func=connect("orcl")

2

for 60*60*24=elapse(datetime("2015-01-01 00:00:00"),A2-1)3

=A1.query("select CALLTIME,OUTID,INID,DURATION,CHARGE from"+A1+"where callTime=? ",B2)4

=B3.sort(OUTID,INID,DURATION,CHARGE)5

return B46

=A1.close()

7=cursor@c(A1,"callRecordA")8=cursor@c(A1,"callRecordB")9=[A7,A8].mergex@u()10=A9.fetch@x(500)

A1: Use func to define program cursors, and the corresponding call syntax is cursor@c.

B2: Cycle every second of the day.

C3: Query one second of data from the database, because it is indexed, so the speed is very fast, and the impact on the database is very small. Note that A1 is a table name variable, and program cursors can fetch from either callRecordA or callRecordB.

C4: Memory sort one-second data to form an ordered result set. Since the data is in the same second, you only need to sort the other fields.

A7A8: Take table name as parameter and fetch 2 program cursors.

A9: Merge two cursors in order,@u means union. Similarly, you can use @i and @d to perform union intersection and subtraction operations, respectively.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.