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

Associative actions can seriously affect performance. SPL supports memory pre-association, which can speed up associative actions and improve performance.

To understand the impact of associative actions on performance, let's design a set of Oracle associative tables and unrelated wide tables, and perform the same calculation.

The structure and relationships of the associated table are as follows:

Data quantity: call record form (million items), user form (100,000 items), account opening network (10,000 items), agent form (10,000 items).

Calculation goal: to calculate the total communication cost, that is, the sum of the shared cost of all the outlets and agents corresponding to all outgoing users and incoming users respectively.

Write the association result to another table to form an unrelated wide table:

CallRecordWideSERIALNUMBERCHARGEOUTBRANCHOUTCOSTINBRANCHINCOSTOUTAGENTOUTCOSTINAGENTINCOST

The following SPL script is used to illustrate the impact of associated actions on performance:

AB1=connect ("orcl")

2=now ()

3for 10=A1.query ("select sum (outBranch.outCost+inBranch.inCost+outAgent.outCost+inAgent.inCost) from callRecord,callUser outUser,callUser inUser,telecomBranch outBranch,telecomBranch inBranch,telecomAgent outAgent,telecomAgent inAgent where callRecord.outID=outUser.userID and callRecord.inID=inUser.userID and outUser.branchID=outBranch.branchID and outUser.agentID=outAgent.agentID and inUser.branchID=inBranch.branchID and inUser.agentID=inAgent.agentID") 4=interval@ms (A2 outBranch.outCost+inBranch.inCost+outAgent.outCost+inAgent.inCost now ()) / Oracle associative table 25802ms5

6=now ()

7for 10=A1.query ("select sum (outBranchOutCost+inBranchInCost+outAgentOutCost+inAgentInCost) from callRecordWide") 8=interval@ms (A6 Magi now ()) / oracle wide list 2055ms9=A1.close ()

As you can see, the association is 12.6 times slower than the non-association (25802), which will seriously affect the computing performance.

SPL can improve the performance of associated actions through pre-association. First, load the data into memory, as follows:

AB1=connect ("orcl")

2=A1.query ("select * from telecomAgent") .keys (AGENTID) 3=A1.query ("select * from telecomBranch") .keys (BRANCHID) 4=A1.query ("select * from callUser") .keys (USERID) 5=A1.query ("select * from callRecord"). Keys (SERIALNUMBER) 6=A1.switch (AGENTID,A2:AGENTID; BRANCHID,A3:BRANCHID) 7=A5.switch (OUTID,A14:USERID; INID,A4:USERID) 8=env (callRecord,A7) / global variable: pre-associate

The function switch replaces the field value with a record reference to achieve pre-association.

In subsequent business algorithms, you can directly reference the fields of other tables to improve the performance of associated computing, as shown below:

= callRecord.sum (OUTID.BRANCHID.OUTCOST+INID.BRANCHID.INCOST

+ OUTID.AGENTID.OUTCOST+INID.AGENTID.INCOST)

In order to intuitively understand the computational performance improvement of pre-correlation, SPL pre-association and wide table are also used below to compare.

AB11=connect ("orcl")

12=A11.query ("select * from telecomAgent") .keys (AGENTID) 13=A11.query ("select * from telecomBranch") .keys (BRANCHID) 14=A11.query ("select * from callUser") .keys (USERID) 15=A11.query ("select * from callRecord") .keys (SERIALNUMBER) 16=A14.switch (AGENTID,A12:AGENTID; BRANCHID,A13:BRANCHID) 17=A15.switch (OUTID,A14:USERID) INID,A14:USERID) 18=env (callRecord,A17) / global variable: pre-associate 19=A11.query@s ("select * from callRecordWide") .keys (SERIALNUMBER) 20=env (callRecordWide,A19) / global variable: wide table 21

22=now ()

23for 10

= callRecord.sum (OUTID.BRANCHID.OUTCOST

+ INID.BRANCHID.INCOST

+ OUTID.AGENTID.OUTCOST+INID.AGENTID.INCOST)

24=interval@ms (A22 now ()) / SPL pre-associate 13272ms25

26=now ()

27for 10

= callRecordWide.sum (OUTBRANCHOUTCOST

+ INBRANCHINCOST+OUTAGENTOUTCOST

+ INAGENTINCOST)

28=interval@ms (A26 Magi now ()) / SPL wide table 2210ms

It can be seen that the pre-correlation is 6 times slower than the wide table (13272 move 2210), and has been greatly improved compared to the 12.6 times slower than the wide table. When the table is wide, the computing performance of SPL is almost the same as that of ORACLE (2210 SPL 2055), but when associated, the pre-associated SPL is significantly faster than the temporarily associated ORACLE (13272 SPL 25802).

It should be noted that although the above algorithm uses a wide table for comparison, it does not mean that the wide table can replace the associated table. In fact, wide meters waste a lot of space and cause maintenance difficulties such as creation and synchronization, which are rarely used in actual projects. Pre-association uses references to establish associations, does not create new tables, wastes space, and does not need to synchronize data.

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.