During database design, sometimes attribute record values belonging to the same person are stored in multiple records for the purpose of data standardization, you want to merge multiple attribute data into one row for display. This is a row-to-column conversion.
For example, the score table.
What is the efficiency of Row-to-column conversion? I tried the following three methods to perform a row-to-column test. The number of records in the test data table is 120Tens of thousands of fields10The test results show that the performance is good.
1. method 1(Versions later than SQL 2000)
-- Select wbook_no, max (case when [cop_g_no] = '000000' then ar end) "1.2 million ", max (case when [cop_g_no] = '000000' then ar end) "50165814", max (case when [cop_g_no] = '000000' then ar end) "10221553" from (select [cop_g_no], wbook_no, sum (g_qty * decl_price) Ar from wbk_pde_list where [cop_g_no] In ('123456', '123456', '123456 ') group by [cop_g_no], wbook_no) agroup by wbook_no
Ii. method 2 (SQL 2000 and later versions)
Select wbook_no, sum (case when [cop_g_no] = '000000' then g_qty * decl_price end) "60174257 ", sum (case when [cop_g_no] = '000000' then g_qty * decl_price end) "50165814", sum (case when [cop_g_no] = '000000' then g_qty * decl_price end) "10221553" from wbk_pde_list where 1 = 1 and [cop_g_no] In ('000000', '000000', '000000') group by wbook_no
3. Method 3: Use the explain command to implement it (SQL 2005 and later versions are provided with commands)
Select wbook_no, "60174257", "50165814", "10221553" from (select [cop_g_no], wbook_no, g_qty * decl_price AR from wbk_pde_list where 1 = 1 and [cop_g_no] In ('20140901', '20160901', '20160901') as dashes (sum (AR) for [cop_g_no] In ([60174257], [50165814], "10221553") as P
4. the query results of the above three methods are as follows:
5. Finally, let's compare their respective performance loss. From the comparison results table, the three items are not much different. In general, they are in the range of 2-3 seconds.
|
Io |
CPU |
Logical read |
Physical read |
Pre-read |
CPUTime |
Time used |
|
|
Table Scan |
Computing title |
Sort |
Table Scan |
Computing title |
Sort |
Times |
Times |
Times |
MS |
MS |
|
Method 1 |
17.652 |
0 |
0.0112513 |
1.33851 |
0.121668 |
0.0131525 |
23827 |
370 |
23827 |
635 |
2216 |
|
Method 2 |
17.652 |
0 |
0.0112513 |
1.33851 |
0.121668 |
0.0131525 |
23827 |
374 |
23827 |
618 |
2171 |
|
Method 3 |
17.652 |
0 |
0.0112513 |
1.33851 |
0.121668 |
0.0131525 |
23827 |
370 |
23827 |
563 |
1960 |
|