如何避免查询中的行号（） [英] How to avoid row number() on query

问题描述

  SELECT  
 ROW_NUMBER（） OVER （< span class =code-keyword> ORDER   BY  Item_ID） AS  ROW_NUM 
，我。[ITEM_ID] 
，我。[ITEM_NBR] 
，我。[ITEM_CATEGORY_ID] 
，我。[ITEM_CATEGORY_NAME] 
  FROM  INV_ITEM_MASTER  as 我

我有查看查询如上所述

然而我发现性能问题与使用该查询导致的大数据有关OW_NUMBER（）OVER（ORDER BY Item_ID）AS ROW_NUM





有什么建议可以避免使用上面的行号吗？

因为我仍然需要用它来分页

解决方案

< blockquote>有很多技术如何实现分页。例如，看一下在ASP.NET中对大型结果集进行分页 [ ^ ]



但是，如果这是您使用的整个查询，您似乎缺少了获取的边界，因此您从数据库中选择所有内容并执行客户端的分页。我认为查询应该类似于

  SELECT   TOP   10  a。* 
  FROM （ SELECT  ROW_NUMBER（） OVER （ ORDER   BY  Item_ID） AS  ROW_NUM 
，I。[ITEM_ID] 
，I。[ITEM_NBR] 
， I. [ITEM_CATEGORY_ID] 
，I。[ITEM_CATEGORY_NAME] 
  FROM  dbo.zipcode）a 
  WHERE  ROW_NUM>  @ start_row_number  

SELECT
    ROW_NUMBER() OVER (ORDER BY Item_ID) AS ROW_NUM
    ,I.[ITEM_ID]
    ,I.[ITEM_NBR]
    ,I.[ITEM_CATEGORY_ID]
    ,I.[ITEM_CATEGORY_NAME]
FROM INV_ITEM_MASTER as I

I have view query as above
however i found performance issue work with big data using that query cause by OW_NUMBER() OVER (ORDER BY Item_ID) AS ROW_NUM


any suggestion to avoid using row number as above?
cause i still need use it for paging

解决方案

There are a lot of techniques how to implement paging. For example have a look at Paging of Large Resultsets in ASP.NET[^]

However, if that is the whole query you use, you seem to be missing boundaries for the fetch, so you select everything from the database and do the paging on client side. I believe that the query should be something like

SELECT TOP 10 a.* 
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Item_ID) AS ROW_NUM
              ,I.[ITEM_ID]
              ,I.[ITEM_NBR]
              ,I.[ITEM_CATEGORY_ID]
              ,I.[ITEM_CATEGORY_NAME]
       FROM dbo.zipcode ) a
WHERE ROW_NUM > @start_row_number

这篇关于如何避免查询中的行号（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！