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

Source: Internet
Author: User

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

 
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.