SQL statement exercise instance 10-SQL Server row-to-Column Performance Test

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