Total Record Count in paging

Zafar Ullah posted on Thursday, July 03, 2008

Hello,

I am using CSLA version 3.0.4.0 and my application is web based(ASP.NET with C#)

I have a huge number of records to display in our database e.g over 20 million and above in some tables rest of them are under 1 lac. I hav implemented paging and a max of 100 records can be shown displayed in a grid at 1 time.

Actually what we do is we send call to Collection class to load all records which meet under certain search criteria and take its count. like this

AMCollection.GetAMCollection(crit).Count

In background Collection holds objects so it make objects of all records e.g 1million and then we have a count. which make web page response very slow over 30 sec on first load and 20 to 25 sec on each postback.

Is there any way to take the total count with out making objects because that make it slow.

(we are not using Stored Procedures)

Thanks in advance

Zafar Ullah

sergeyb replied on Thursday, July 03, 2008

You can probably just use command object that just gets total count of records by calling “Select Count(1) From ….” and just returning single number.

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

From: Zafar Ullah [mailto:cslanet@lhotka.net]
Sent: Thursday, July 03, 2008 3:08 PM
To: Sergey Barskiy
Subject: [CSLA .NET] Total Record Count in paging

Hello,

I am using CSLA version 3.0.4.0 and my application is web based(ASP.NET with C#)

Actually what we do is we send call to Collection class to load all records which meet under certain search criteria and take its count. like this

AMCollection.GetAMCollection(crit).Count

Is there any way to take the total count with out making objects because that make it slow.

(we are not using Stored Procedures)

Thanks in advance

Zafar Ullah

RockfordLhotka replied on Thursday, July 03, 2008

You shouldn't even need a command object.

You are loading the page of rows in a DataPortal_Fetch() method. That method should use one of the many techniques to only load the required rows from the database.

That method also must get the total rows available. That's just another SQL statement - typically with a COUNT or something.

Zafar Ullah replied on Friday, July 04, 2008

Thanks Rocky,

but what if i am using NHibernate.Actually i put my question in CSLAContrib but no body reply me there so i put it here with default CSLA behavior.

This is the actual post:

I am using CSLA.NHibernate from CSLA Contrib.

I am using CSLA version 3.0.4.0 and CSLA.NHibernate version is compiled using the same version of CSLA and my application is web based(ASP.NET with C#)

Actually what we do is we send call to Collection class to load all records which meet under certain search criteria and take its count. like this

AMCollection.GetAMCollection(crit).Count

In background NHibernate make objects of all records e.g 1million and then we have a count. which make web page response very slow over 30 sec.

Is there any way to take the total count with out making objects of each record that NHibernate pulls.

Thanks in advance

Zafar Ullah

RockfordLhotka replied on Saturday, July 05, 2008

It doesn’t matter what data access technology you are using. Your DataPortal_Fetch needs to do two things:

1. Get the page worth of data

2. Get the total number of rows available

I don’t know how you do that in NHibernate, but there must be a way to do it?

Rocky

rsbaker0 replied on Sunday, July 06, 2008

We looked at NHibernate before settling on the Wilson ORMapper. (In fact I transated. CSLA.NHibernate to CLSA.WORMapper... :)

I recall that NHibernate fully supports paged collections. You tell it the starting page you want and number of records, and it should do the rest for you. (I don't remember the exact parameter interface, but this was a requirement for us an NHibernate met it easily)

Zafar Ullah replied on Monday, July 07, 2008

Thanks

here is the code your are talking about

for example you can pick top 20 records based on the given criteria like this

_iCriteria is an object of ICrietria Class

_iCriteria.SetFirstResult(0);

_iCriteria.SetMaxResults(20);

But the problem is that if we have to show total records which meets the given criteria where as the above 2 statements returs only top 20 records. How could i get Total.

e.g this will be my output if i have a page size of 20 and total records found are 100.

Total Records: 100 Page 1 of 5 >>

Is there any way that existing crietria give me 100 as total count along with page size records.(20)

Hope its clear now.