Remember the paging optimization of SQL Server and talk about the problems of using row_number () page

The original: Remember the paging optimization of SQL Server and talk about the problems of using row_number () page

Recently there is a project response, in the server CPU usage is high, our Event query page is very slow, query a few records unexpectedly 4 minutes or more, and in the second page to take so much time, this is certainly unacceptable, but also let the scene with SQLServerProfiler the statement grabbed up.

Paging with Row_number ()

Let's take a look at the page statements caught at the scene:

  Select Top A.*,ag. Name as agentservername,,d.name as mgrobjtypename,l.username as UserName from EventLog as a left join Mgrobj as B on a.mgr Objid=b.id and A.AGENTBM=B.AGENTBM left joins Addrnode as C on B.addrid=c.id left joins Mgrobjtype as D on B.mgrobjtypeid=d. The Id left joins Eventdir as E on a.eventbm=e.bm left joins Agentserver as AG on A.agentbm=ag.  AGENTBM left join Loginuser as L on A.cfmoper=l.loginguid where A.orderno not in (select top 0 OrderNo from EventLog as A left joins Mgrobj as B on a.mgrobjid=b.id left joins Addrnode as C on B.addrid=c.id where 1=1 and a.alarmtime>= ' 2014- 12-01 00:00:00 ' and a.alarmtime<= ' 2014-12-26 23:59:59 ' and B.addrid in (' 02109000 ',......, ' 02109002 ') Order by AlarmTime D ESC) and 1=1 and a.alarmtime>= ' 2014-12-01 00:00:00 ' and a.alarmtime<= ' 2014-12-26 23:59:59 ' and B.addrid in (' 02109 ',......, ' 02109002 ') Order by AlarmTime DESC  

This is a typical use of two times top for paging, the principle is: to find out pageSize*(pageIndex-1) (T1) The number of records, and then Top out of PageSize the record is not in T1, is the current page record. This kind of query efficiency is not high mainly used not in . Refer to my previous article, "How the program ape solves SQL Server's cpu100%" mentioned:"The index is useless for an expression that does not use the SARG operator."

Then use ROW_NUMBER paging instead:

WITH cte AS(select a.*,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userName,b.AddrId,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNofrom eventlog as a WITH(FORCESEEK) left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id left join eventdir as e on a.EventBm=e.Bm left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid where a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId in ('02109000',……,'02109002'))SELECT * FROM cte WHERE RowNo BETWEEN 1 AND 20;

The execution time is increased from 14 seconds to 5 seconds, which indicates that row_number paging is more efficient, and that this is top top a lot more elegant than pagination.

The "cheat" query engine lets queries query as you wish

But why is it 5 seconds to query 20 records, especially if the table is a time index--refer to the index mentioned in "How the program ape solves SQL Server's cpu100%".

I tried to get rid AND b.AddrId in (‘02109000‘,……,‘02109002‘) of this sentence, the results of less than 1 seconds to the 538 records query out, and add location limit this sentence, the result is 204 lines. Why does the result set is not big, spends the time to be so much different? Look at the execution plan and find that you are walking on a different index than the time index.

Put this question on the SQL Server group, and soon, Takakuwa gave a reply: to achieve with the removal of the site limit this sentence, use AdddrId+‘‘ in .

What do you mean? For a moment not to understand, is Takakuwa not read my statement? Soon, someone added, to cheat the query engine. " cheat "? Still do not understand, but I did, the above-mentioned CTE statement copy out intact, and then change this sentence AND b.AddrId in (‘02109000‘,……,‘02109002‘) in order to AND b.AddrId+‘‘ in (‘02109000‘,……,‘02109002‘) , a little execution, God!!! It's done in less than 1 seconds. In the execution of the plan pair, sure enough to go is the time index:

Later, remember the Query engine optimization principle, if you have a condition with an operator or use a function, the query engine will abandon the optimization, and perform a table scan. The head suddenly turned around, before using the b.AddrId+‘‘ query engine to try to add the Mgrobj table to do optimization, then two tables together, will cause the estimated number of records greatly increased, and used b.AddrId+‘‘ , the query engine will first by the time index of the record brush selected, so that the effect, That is, forcing the CTE to perform the condition instead of the in in conditional brush selection in the CTE. I see! Sometimes, an overly optimized query engine can lead to the opposite effect, and if you know the principle of optimization, then you have some small tricks to make the query engine optimize as you wish .

Row_number () Paging in the case of a large number of pages

Things are here, not yet. After the colleague and I reaction, query to the back of the page, and card! What? I re-execute the above statement, the time range from 2011-12-01 to 2014-12-26, the number of records is limited to 19981 to 20000, sure enough, the query to about 30 seconds, view the execution plan, is the same, why?

Takakuwa suspect that Key lookup is too much to cause, it is recommended to first page out the RID and then do key lookup. I don't know what that means. Print out the execution plan and IO:

Looking at Io, it's clear that the more pages you get to the back, the more pages you read in several other related tables. I speculate that when row_number paging, if there is a table connection, sorted to the number of records returned, the previous record is to participate in the table connection , resulting in a later paging, the more slowly, because the more associated tables to scan.

Isn't there a way out? This time Song Sang heroic stand out: "You give the table after adding a forceseek hint can break." This is really like the sound of nature, immediately try.

Use the Forceseek hint to force the table to go index

Looked up the following information:

Hints introduced in SQL Server2008 ForceSeek that can be used to replace index scans with index lookups

So, what happens when you add this sentence to the EventLog table?

Sure enough, the query plan changed, starting with the hint, missing the include index. Quickly add, sure enough, according to this way query time changed to 18 seconds, there is progress! But looking at Io, as above, is not getting less. However, finally learned a new skill, and Song Sang is also very enthusiastic about the evening to help see.

Put other tables that are not in the where, outside the CTE.

According to the above Io, it was soon mentioned that other left join tables were placed outside the CTE. This is a way to put the addition eventlog , mgrobj and addrnode the table outside, the statement is as follows:

WITH cte AS(select a*,b.AddrId,b.Name as MgrObjName,b.MgrObjTypeId          ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNofrom eventlog as aleft join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm left join addrnode as c on b.AddrId=c.Id where a.AlarmTime>='2011-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' AND b.AddrId+'' in ('02109000',……,'02109002'))SELECT a.* ,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userNameFROM cte a left join eventdir as e on a.EventBm=e.Bm left join mgrobjtype as d on a.MgrObjTypeId=d.Id left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid WHERE RowNo BETWEEN 19980 AND 20000;

It worked, the IO was greatly reduced, and the speed was increased to 16 seconds.

表 'loginuser'。扫描计数 1，逻辑读取 63 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'agentserver'。扫描计数 1，逻辑读取 1617 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'mgrobjtype'。扫描计数 1，逻辑读取 126 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'eventdir'。扫描计数 1，逻辑读取 42 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'addrnode'。扫描计数 1，逻辑读取 119997 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'eventlog'。扫描计数 1，逻辑读取 5027 次，物理读取 3 次，预读 5024 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'mgrobj'。扫描计数 1，逻辑读取 24 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

We see that the Addrnode table or scan count is very large. That can still improve, this time, I think of, first put addrNode , mgrobj mgrobjtype Three tables union query, put into a temporary table, and then eventlog do inner join , and then query the results and other tables do left join , this also can reduce IO.

Use temporary tables to store paging records in making table connections to reduce IO

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObjSELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName INTO tmpMgrObj  FROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdWHERE AddrId IN('02109000',……,'02109002');WITH cte AS(select a.*,b.AddrId,b.MgrObjTypeId          ,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userNamefrom eventlog as aINNER join tmpMgrObj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBmleft join mgrobjtype as d on b.MgrObjTypeId=d.Id left join agentserver As ag on a.AgentBm=ag.AgentBm left join loginUser as l on a.cfmoper=l.loginGuid WHERE AlarmTime>'2011-12-01 00:00:00' AND AlarmTime<='2014-12-26 23:59:59') SELECT * FROM cte WHERE RowNo BETWEEN 19980 AND 20000IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

This query took only 10 seconds. Let's take a look at IO:

表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'mgrobj'。扫描计数 1，逻辑读取 24 次，物理读取 2 次，预读 23 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'addrnode'。扫描计数 1，逻辑读取 6 次，物理读取 3 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。----------表 'loginuser'。扫描计数 0，逻辑读取 24 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'eventlog'。扫描计数 93，逻辑读取 32773 次，物理读取 515 次，预读 1536 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'tmpMgrObj'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'mgrobjtype'。扫描计数 1，逻辑读取 6 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 'agentserver'。扫描计数 1，逻辑读取 77 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

Besides EventLog, the IO of other tables is greatly reduced, and there is wood there?

Inner the difference between join and left join

However, a few more tests, found that the above statement is still a bit of a problem: the first page of the query, but also to use 5 seconds, and query time in the current month, also close to 5 seconds. What is this for? This time, Song Sang to help, provide another SQL statement, in the first few pages of the query 1 seconds out, and the number of pages later, the change is not big. I carefully compared the two statements, I originally used inner join , and Song Sang gave left join . What's the difference between the two? After careful comparison of the query plan, it was found that when use inner join , the query engine executes inner join rather than subqueries, using LEFT join The query engine executes the subquery first. Therefore, if inner join is used, the time index is not effectively used when querying data for 1 months. Finally, I came up with the statement below, in the query the latest data or the previous pages of data, can come out in about 1 seconds, and query the number of pages, in about 10 seconds, basically solve the problem.

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObjSELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeNameINTO tmpMgrObj  FROM dbo.mgrobj mINNER JOIN dbo.addrnode a ON a.Id=m.AddrIdINNER JOIN dbo.mgrobjtype t ON m.MgrObjTypeId=t.IdWHERE AddrId+'' IN('02109000',……,'02109002');SELECT tmp.*,ag.Name AS AgentServerName, l.UserName AS userNameFROM    ( SELECT    a.* ,b.MgrObjTypeName  , b.AddrId,ROW_NUMBER() OVER ( ORDER BY AlarmTime DESC ) AS RowNoFROM(SELECT    * FROM      eventlogWHERE     AlarmTime >= '2011-12-01 00:00:00' AND AlarmTime <= '2014-12-26 23:59:59') AS aLEFT JOIN tmpMgrObj AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm) tmp LEFT JOIN eventdir AS e ON tmp.EventBm = e.BmLEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBmLEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuidWHERE tmp.RowNo BETWEEN 1 AND 20;IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

Other optimization references

In another group discussion, it is found that the use of ROW_NUMBER paged query to the next page will be more and more slowly this problem really bothers a lot of people.

Some people suggest, who would be so bored, to turn pages to thousands of pages later? I thought so at first, but after interacting with other people, I found that there really was a scenario where our software provided the last page of this function, the result ... Of course, one way to do this is to remove the last page when you design the software, and another idea is to query the page after more than half the number of pages, then the query is actually the first page of the query.

Others have suggested that the contents of the query be put into a temporary table, the temporary table is added to the index of the self-increment ID, so that the identification ID can be used to quickly brush the record. This is also a method that I intend to try later. However, this method is also problematic, is not able to do generic, must be based on each table to build a temporary table, in addition, in the large data query, the insertion of too many records, because the existence of the index is also slow, and each time, it is estimated that the CPU is also very tight. But anyway, this is a way of thinking.

Do you have any good advice? You might want to put your ideas in the comments and discuss them together.

Summarize

Now, let's summarize what we learned in this optimization process:

• In SQL Server, ROW_NUMBER paging should be the most efficient and compatible with SQLServer2005 future databases
  
• You can control the optimization of the query engine section by using the "cheat" query engine tips
  

• ROW_NUMBERPaging has a performance problem with large pages, and can be circumvented with a few tricks

  • Using the index as far as possible by CTE
    
  • Place non-participating where tables outside the CTE of the pagination
    
  • If you have where too many tables to participate in, consider making a temporary table for tables that do not participate in paging, reducing IO
    
• inner joinwill take precedence over subqueries, without left join
  

• with(forceseek)indexed query with the ability to force queries

Reference articles

• Acrobatics field--row_number function of egg-ache
  
• Why is the page-turning technology of super-long list data complex

Finally, thanks to the SQL Server group of Takakuwa, Song Sang, Chaussain and other friends of the great help, the elimination of water blowing group is very good, let me this program ape learned a lot of knowledge of the database!

If you feel that reading this article is helpful to you, please click " recommend " button, your "recommendation" will be my biggest writing motivation! If you want to continue to follow my article, please scan the QR code, follow the horse non-code of the public number, I will send my article to you and share with you my daily reading of the high-quality articles.
   
The copyright of this article is owned by the author and the blog Park, the source website: http://www.cnblogs.com/marvin/Welcome to reprint, but without the author's consent, reprint article must be in the article page obvious location to give the author and the original link , Otherwise, the right to pursue legal liability is retained.

One-time paging optimization for SQL Server and talk about problems using row_number () paging