efficiently paging very large record set (~500000) records

uglyeyes

Hi, I am using datalist in c# web application. the site was fast till about I had 2000 records now it slowed down and I need to efficiently page the data rows. I followed this article. http://aspnet.4guysfromrolla.com/articles/031506-1.aspx but, it only gives me 10 records at a time but the actual result is ~500000 in my below code

List<Advert> adverts = new List<Advert>();
PagedDataSource pds = new PagedDataSource();
pds.AllowPaging = true;
adverts = myBLL.GetAdverts(country, city, adtype,pds.CurrentPageIndex,pds.PageSize);
PropertyListCount = adverts.Count;
pds.DataSource = adverts;
pds.PageSize = 10;

I know i am only getting 10 records as its only retrieving 10 records for the firsst time for Page No 1 but how can i display other page numbers so when user clicks Page 2 the parameter becomes pds.CurrentPageIndex=10 and pds.PageSize=10? please help I hope my question is clear enough to understand. thank you

Not Active

How about this one, http://www.4guysfromrolla.com/articles/081804-1.aspx[^]

I know the language. I've read a book. - _Madmatt

uglyeyes

this is exactly what i am doing. its little show. takes about 5secs to display 35000 records. is there something funny i might be doing? or it is normal for it to take 5 secs? well its queries "select * from table" but this operation might take long?

Not Active

You should change your query to return only the number of records necessary for the page.

I know the language. I've read a book. - _Madmatt

uglyeyes

I tried that too. it returns 10 record and there is no page 2 or 3 below is the query SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY adid) as RowNum FROM advert e ) as DerivedTableName WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

Not Active

There are a lot of articles that cover datalist paging, like this one[^] or this one[^]

I know the language. I've read a book. - _Madmatt

uglyeyes

Hi again, this article http://aspalliance.com/157\_Paging\_in\_DataList was someway useful but I have already done that part and it mentions the disadvantage at the end of the article too.. so I changed my database call to add extra parameter i.e. currentindex and total rec to display per page

public void AdList(Navigation navigation)
{
List<Advert> adverts = new List<Advert>();
PagedDataSource pds = new PagedDataSource();
pds.AllowPaging = true;
//Based on navigation manage the NowViewing
switch (navigation)
{
case Navigation.Next: //Increment NowViewing by 1
NowViewing++;
break;
case Navigation.Previous: //Decrement NowViewing by 1
NowViewing--;
break;
case Navigation.Last: //Make NowViewing to last page for PagedDataSource
NowViewing = pds.PageCount - 1;
break;

            default:                    //Default NowViewing set to 0
                NowViewing = 0;
                break;
        }

adverts = myBLL.GetAdverts(country, city, adtype,NowViewing,pds.PageSize);
PropertyListCount = adverts.Count;
pds.DataSource = adverts;
pds.PageSize = 10;
//Set the current page index
pds.CurrentPageIndex = NowViewing;

        // Disable Prev, Next, First, Last buttons if necessary
        lbtnPrev.Enabled = !pds.IsFirstPage;
        lbtnNext.Enabled = !pds.IsLastPage;
        lbtnFirst.Enabled = !pds.IsFirstPage;
        lbtnLast.Enabled = !pds.IsLastPage;

        dlPropertyList.DataSource = pds;
        dlPropertyList.DataBind();

}
//DataList Pagination Scripts
protected void lbtnPrev_Click(object sender, EventArgs e)
{
PropertyList(Navigation.Previous);
}
protected void lbtnNext_Click(object sender, EventArgs e)
{
PropertyList(Navigation.Next);
}

    protected void lbtnFirst\_Click(object sender, EventArgs e)
    {   
        PropertyList(Navigation.First);
    }

    protected void lbtnLast\_Click(object sender, EventArgs e)
    {
        PropertyList(Navigation.Last);
    }

public enum Navigation
{
None,
First,
Next,
Previous,
Last,

uglyeyes

can someone please help me. I need a fastest way to display datalist with hugh record set in website.

Arindam Tewary

You might try MultiThreading here. Each thread would pull 'n' number of record set from database. Hence if you are having 2 thread for pulling data from database your performance is doubled. Currently if 35000 record is fetched in 5 seconds, then if you use 2 thread for data pulling then you could get 70000 records pulled from database in same 5 seconds. But the risk is that correct multithreading can provide you very enhanced reformance, whereas if not implemented correctly it can lead to severely affecting the exising system.

Thanks, Arindam D Tewary

uglyeyes

I am sure there must be an alternative solutions other than multi-threading. I saw this article http://www.netomatix.com/Development/DataListPaging.aspx in this article it users query string but how can i use it without having to use querystring could someone please help? I now have created two stored proc one returns the total count of the result which is about 35000 and the one to display for a page currently being views i.e. adverts = myBLL.GetAdverts(country, city, adtype,NowViewing,pds.PageSize); -SQL SELECT * FROM ( select ROW_NUMBER() OVER(ORDER BY adid) as RowNum, a.* from advert where isActive=1 and c.countryname like '%'+@CountryName+'%' and a.adcity like '%'+@AdCity+'%' and a.AdSubCatType like '%'+@AdSubCatType+'%' ) as DerivedTableName WHERE RowNum BETWEEN @CurrentPageIndex AND (@CurrentPageIndex + @PageSize) - 1 order by adDate desc