从大表中分块获取数据 [英] Get data from large table in chunks

问题描述

我有一个包含数百万条记录的大表.我在我的应用程序中将此表绑定到 gridview.由于数据很大，我们使用分页概念检索数据.就像我将 gridview 页面大小设置为 2000 一样，那么我只从表中获取 2000 条记录.我为此使用以下查询

I have a large table having millions of records. I am binding this table to gridview in my application. Since data is large, we are retrieving data using paging concepts. like if i set the gridview page size to 2000, then i am fetching only 2000 records from table. i am using following Query for this

Select * from (select *, Row_Number() over (order by id) as Row_Index) a 
where
Row_Index > @start_index and Row_Index < @End_Index

这个查询对前几百万条记录运行速度很快，但随着开始和结束索引的增加，性能急剧下降.我如何改进这个查询

This query run fast for first few millions of records but as the start and end index increases performace degrades drastically. How can i improve this query

推荐答案

让你的唯一列成为索引(聚集或非聚集)就像你的 ID 列在表中是一个很好的候选者，如果它没有重复......

Make your unique column be index (either clustered of non-clustered) like your ID column in table is a good candidate if it has no duplicate..

或者添加一个自动增加的列 ID.

Or add an AutoIncremented Column ID.

你也可以像这样使用查询

You may also use query like this

Select  top 2000 *
from    t
where ID >= @start_index
order by ID

这篇关于从大表中分块获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！