SQL Server SQL performance optimization--pivot row and column conversion reduction Scan Count Optimization query statement

　　

First look at a commonly used table structure design method:

Then you may encounter a typical query method, the Master Table Association, query the child table of some (or all) key points corresponding to the value, landscape display (that is, the line is displayed)

This is a very obvious query, but it shows multiple queries on the Word table (the index is temporarily thrown away)

Compared to this query way many people have encountered, if the child table is the configuration information such as small table, the problem is not big, if the size of the table data is large, it may have an impact.

The purpose of this query is to display the results of the "vertical table" stored in "landscape", which is equivalent to the feeling of Heng lie conversion.
The results of a child table can be converted to banner and then connected to the primary table.
And then get a final query result (format), you can reduce the number of queries to the child table
Here, the result of the child table "converts the results of a table to banner" is a typical row-and-column conversion operation

First of all, let's look at one of the steps here that translates into banner, which requires pivot, step-by-step

Then look at the overall query results for the two query methods after the main table join

So take a look at the latter query method is also through the industry after the conversion to do the join execution plan, you can see only the Word table for a search (this is the index seek, but the temporary throw-off index)

Looking at two SQL IO information, you can find that the former scan count is 5, the logical read is 65, the latter scan count is 1, logical read is 13,65=13*5. It is visible that the latter is a one-time reading of several key values in the table, while the former each key value reads the table.

Summarize:

Rewriting SQL is one of the ideas to realize optimization, of course, there are many kinds of rewriting the SQL technique, this article only provides a rewriting idea for a class of typical queries, and avoids the query which is implemented by the way of multiple reads to a table.

By rewriting a common query notation, an equivalent logic is implemented to reduce the number of reads to the base table to achieve SQL optimization.

Of course, the actual situation may be more complex, use this idea to rewrite the time to pay attention to the SQL statement test validation.

Attach the test script for this article

Create Tableheadertable (Headeridint, Othercolumnvarchar( -))Create Tabledetailtable (Headeridint, Detailidint Identity(1,1), Detailkeyvarchar( -), Detailvaluesint)Declare @i int = 0 while @i<1000000begin    Insert  intoHeadertableValues(@i,NEWID())    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0001',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0002',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0003',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0004',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0005',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0006',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0007',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0008',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0009',RAND()*10000)    Insert  intoDetailtable (headerid,detailkey,detailvalues)Values(@i,'A0010',RAND()*10000)    Set @i=@i+1EndCreate IndexIdx_headerid onheadertable (Headerid)Create IndexIdx_headerid ondetailtable (Headerid)Create IndexIdx_detailkey ondetailtable (Detailkey)Select *,(SelectDetailvalues fromDetailtable TwhereT.headerid=A.headerid andT.detailkey= 'A0001') as 'the value of the Key1',(SelectDetailvalues fromDetailtable TwhereT.headerid=A.headerid andT.detailkey= 'A0002') as 'the value of the Key2',(SelectDetailvalues fromDetailtable TwhereT.headerid=A.headerid andT.detailkey= 'A0003') as 'the value of the Key3',(SelectDetailvalues fromDetailtable TwhereT.headerid=A.headerid andT.detailkey= 'A0004') as 'the value of the Key4',(SelectDetailvalues fromDetailtable TwhereT.headerid=A.headerid andT.detailkey= 'A0005') as 'the value of the Key5' fromheadertable awhereA.headerid= 10000SELECTA.*, t.a0001 as  'the value of the Key1', t.a0002 as  'the value of the Key2', t.a0003 as  'the value of the Key3', t.a0004 as  'the value of the Key4', t.a0005 as  'the value of the Key5' fromHeadertable AInner Join(SelectHeaderid, Detailkey, detailvalues fromdetailtable) T Pivot (MAX(detailvalues) forDetailkeyinch(a0001,a0002,a0003,a0004,a0005)) T onT.headerid=A.headeridwhereA.headerid= 10000

　　

SQL Server SQL performance optimization--pivot row and column conversion reduction Scan Count Optimization query statement