ASP.NET

Re: efficiently paging very large record set (~500000) records

22-Mar-10 15:13

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
<pre>
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;

</pre>

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 Active22-Mar-10 15:32

Re: efficiently paging very large record set (~500000) records

22-Mar-10 15:32

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

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

uglyeyes22-Mar-10 15:43

Re: efficiently paging very large record set (~500000) records

22-Mar-10 15:43

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 Active22-Mar-10 16:09

Re: efficiently paging very large record set (~500000) records

22-Mar-10 16:09

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

uglyeyes22-Mar-10 16:44

Re: efficiently paging very large record set (~500000) records

22-Mar-10 16:44

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 Active22-Mar-10 16:51

Re: efficiently paging very large record set (~500000) records

22-Mar-10 16:51

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

uglyeyes22-Mar-10 17:06

Re: efficiently paging very large record set (~500000) records

22-Mar-10 17:06

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

<pre>
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,
Pager
}

public int NowViewing
{
get
{
object obj = ViewState["_NowViewing"];
if (obj == null)
return 0;
else
return (int)obj;
}
set
{
this.ViewState["_NowViewing"] = value;
}
}
---STORED proc script
select ROW_NUMBER() OVER(ORDER BY adid) as RowNum,
* 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

</pre>

but this gives only 10 records in one call so how can i display total records, next and previous etc?

uglyeyes22-Mar-10 19:43

Re: efficiently paging very large record set (~500000) records

22-Mar-10 19:43

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

Arindam Tewary22-Mar-10 21:58

Arindam Tewary

22-Mar-10 21:58

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

Re: efficiently paging very large record set (~500000) records

uglyeyes23-Mar-10 2:04

23-Mar-10 2:04

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

load page

future383922-Mar-10 14:32

future3839

22-Mar-10 14:32

hey. am new in asp.net. Actually, I have a series of component and would like these component come invisible before.although I invisible the properties, I need to make them invisible before loading page. I am lloking for sth like page load but don't know when and where to write it.
thx for ur help in advance

Re: load page

Not Active22-Mar-10 15:14

Mouse Over ASP [modified]

22-Mar-10 15:14

Your post isn't exactly clear. If you set the visibility property of the control it will be not be visible when the page loads.

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

Dalek Dave22-Mar-10 13:17

22-Mar-10 13:17

I have an ASP page, and want to have a hover changing style event.

Here is the code for the hyperlink.

<asp:HyperLink ID="HyperLink1" runat="server" BorderStyle="None"
                   Font-Underline="False" NavigateUrl="~/Default.aspx"
                   ToolTip="See Overview of Cardington Site">

                   Cardington</asp:HyperLink>

I was thinking there is a way of doing a StaticHoverStyle, but I think I can only do that in a MENU item.

Is there a way of doing a mouse over change easily?

PS Using VB as code behind.

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

modified on Monday, March 22, 2010 7:28 PM

Not Active22-Mar-10 13:26

22-Mar-10 13:26

Just add the events to the link

<asp:HyperLink ID="HyperLink1" runat="server" BorderStyle="None" 
                    Font-Underline="False" NavigateUrl="~/Default.aspx" 
                    ToolTip="See Overview of Cardington Site"
                    onmouseover=MouseOverFunction onmouseout=MouseOutFunction>
                    Cardington</asp:HyperLink

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

Dalek Dave22-Mar-10 13:44

22-Mar-10 13:44

<asp:HyperLink ID="HyperLink1" runat="server" BorderStyle="None" 
                    Font-Underline="False" NavigateUrl="~/Default.aspx" 
                    ToolTip="See Overview of Cardington Site"
                    onmouseover = bgcolor="Red" onmouseout = bgColor="Aqua">
                    Cardington</asp:HyperLink>

I put this line in but it is not a happy bunny.

It is suggesting that if this statement contains quotes, then they must match, but they do.

When I build it fails

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: The tag contains duplicate 'bgColor' attributes.

Source Error:

Line 95:             <td bgcolor="Aqua" class="style15" style="border-color: #00FFFF" width="88">
Line 96:             
Line 97:                 <asp:HyperLink ID="HyperLink1" runat="server" BorderStyle="None" 
Line 98:                     Font-Underline="False" NavigateUrl="~/Default.aspx" 
Line 99:                     ToolTip="See Overview of Cardington Site"

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

Not Active22-Mar-10 14:06

22-Mar-10 14:06

Of course its not happy, that is not the correct syntax. Roll eyes | :rolleyes:

onmouseover="this.style.bgcolor='Red'" onmouseout="this.style.bgcolor='Aqua'"

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

Dalek Dave22-Mar-10 14:18

22-Mar-10 14:18

Yeah, see below!
I beat you to it Smile | :)

I forgot I was in ASP.

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

Dalek Dave22-Mar-10 14:11

22-Mar-10 14:11

Ah, got it!

onmouseover="this.style.color='red'" onmouseout="this.style.color='black'"

Your answer was superb, it got me thinking, got me searching and the final solution was down to me working on your response!

I feel all clever with myself, as I am not an ASP expert!
(obviously).

Thanks, have a vote-up.

------------------------------------

I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

Not Active22-Mar-10 14:21

22-Mar-10 14:21

Good although if all you are doing is setting styles a better way to handle this would be to use CSS

<style>
.MouseOver:hover
{
    color:Red;
}
.MouseOver
{
    color: Black;
}
</style>

    <asp:HyperLink ID="HyperLink1" runat="server" BorderStyle="None" 
                    Font-Underline="False" NavigateUrl="~/Default.aspx" 
                    ToolTip="See Overview of Cardington Site" CssClass="MouseOver">
                    Cardington</asp:HyperLink>

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

Dalek Dave22-Mar-10 14:29