2.6 - Self Servicing - Oracle 11g

I'm trying to find a quick way to determine if there are still more rows available after I've fetched a page of data. I'd like to be able to ask GetMulti to give me back one more row then I've really asked for. But I can't just increase my page size by 1 as this would throw off the calculated rows being put into the query.

So I tried some fancy math to determine a new pagenumber (always 2) and a new pagesize. I was then setting that limitMaxRows to my true page size+1. But it didn't seem to honor my limitMaxRowsToReturn value. It still returned the my "adjusted" page size, which as the user gets deeper and deeper into the pages, would keep growing. For example, to fetch page 30 assuming 20 row page size, I would ask for page 2, pagesize 580, limitrows 21. GetMulti still returned 580 rows.

Is there an easy way to accomplish this?

Have you used GetDBCount to determine how many rows are returned by the query that you want to page through. Once you have done this you can then calculate how many pages exist for a given page size so will always know if more is available.

Have a look at the documentation

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/SelfServicing/gencode_paging.htm#CollectionPaging

Matt

MTrinder wrote:

Have you used GetDBCount to determine how many rows are returned by the query that you want to page through.

Yes, we've used that. And for most tables it performs great. But some tables that will return lots of rows, it does hurt performance. And if all I want to do is know that there is a next page available, going one more row than I need to seems much more efficient.

If they do hit the last page button, then I do in fact do a GetDbCount to determine what that last page is.

GetDBCount() does: Select Count(*) From Table So it won't hurt performance at all.

Walaa wrote:

GetDBCount() does: Select Count(*) From Table So it won't hurt performance at all.

I disagree. I just don't understand how you can say it "won't hurt performace". IMO, it hurts performance. At the minimum, I'm having to make one extra db call. There are issues with count(). If I'm counting on a view, there are joins, there are calculated fields, there are filters, there are order bys, etc, then it will perform even slower. I even stopped using GetDbCount and made my own custom count to only count on the primary key field, and while that helped, it didn't help enough for our slowest screens. That's yet another reason why I don't understand how you can say that GetDbCount() doesn't hurt performance.

We had GetDBCount in place and for most tables and views, it wasn't hurting performance enough that it mattered. But there are a few large tables and views where it was hurting performance. So we temporarily turned off GetDbCount and just had testers use the app with one page for a short while and they noticed the extra performance in a lot of places especially on those few really really really really slow ones. The slow ones were still slow but at least now they were much more acceptable.

This is the initial thread were I tried to solve our GetDbCount performance problems:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15299

I'm not trying to find a way to make getdbcount performance faster. The client is no longer in need of knowing how many pages there are. They just want performance. I just want to make the one call that I have no choice but to make, fetching that page of data, count more.

Any ideas on how I can implement my original idea to fetch a page of data, but with one extra row?

happyfirst wrote:

Walaa wrote:

GetDBCount() does: Select Count(*) From Table So it won't hurt performance at all.

I disagree. I just don't understand how you can say it "won't hurt performace". IMO, it hurts performance. At the minimum, I'm having to make one extra db call. There are issues with count(). If I'm counting on a view, there are joins, there are calculated fields, there are filters, there are order bys, etc, then it will perform even slower. I even stopped using GetDbCount and made my own custom count to only count on the primary key field, and while that helped, it didn't help enough for our slowest screens. That's yet another reason why I don't understand how you can say that GetDbCount() doesn't hurt performance.

I think Walaa was commenting on your statement:

happyfirst wrote:

Yes, we've used that. And for most tables it performs great. But some tables that will return lots of rows, it does hurt performance.

A GetDBCount doesn't return "lots of rows".

happyfirst wrote:

I'm not trying to find a way to make getdbcount performance faster. The client is no longer in need of knowing how many pages there are. They just want performance. I just want to make the one call that I have no choice but to make, fetching that page of data, count more.

I don't think your first approach will work in some cases. Consider this:

Situation when you fetch the data rowcount = 95 pagesize = 10 pagecount = 10

Situation when you want to know if there are more rows Some guy insert a new row, and this is the new situation: newrowcount = 96 newpagesize = pagesize * pagecount = 100 pagenumber = 2 limitrows = pagesize + 1 = 11

If you fetch that, that will return the 2nd. page of size 100 limiting 11. That wont return anything, but a row was added indeed.

I also don't see what exactly isn't working on your first post. Please post more info (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7722).

Back to the real need: I will recommend a COUNT. You can use GetScalar to get COUNT(SomeField). This is an example:

int rowCount = (int) customers.GetScalar(CustomerFieldIndex.CustomerId, AggregateFunction.Count);

See the generated code.

Also please test the performance of both solutions and see what is the best for you.Are you using this setup to display data on a grid using LLBLGenProDataSource? Because if you do, you can handle the GetDBCount event and perform your own COUNT.

daelmo wrote:

A GetDBCount doesn't return "lots of rows".

I understand that GetDBCount returns one number. What I meant was that we have some screens based on views with a very large amount of data behind them. And also include joins, calculated fields, etc. The only way I can think of to speed those up would be to flatten the data and start storing the calculations like in some data warehouse tables but the client won't let us.

In your counter example, page 2 is the tricky one that we handle with another special algorithm. Page 1 is straightforward and Page 3 and on I can use my custom page "fudging" algorithm that I described earlier. The issue with that that no one has answered yet is why didn't that second parameter, limitRows, limit the data returned? Because of that, anything after page 3 starts returning more and more rows that I don't want. Inserting/Deleting rows shouldn't be an issue. At any point that I'm retrieving a page (and you already do this), I just want to know if there was one more row.

I wish GetMulti had an overload that would take startRow and endRow as parameters. At this point it seems like I have to make the change in your library myself.

daelmo wrote:

I also don't see what exactly isn't working on your first post. Please post more info (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7722).

I finally got my initial issue in that other thread resolved and was able to get it counting only on the primary key field which did speed things up.

We are not using LLBL data source. It's our own custom search data source control.

I'm having the same problem on SQL Server 2005 with LLBLGen 2.6.9.1005 (SelfServicing).

GetDbCount is slow (10 seconds) while GetScalar using Count or CountRow is fast (< 1 sec)

I tried the query from GetDbCount directly on the database and the result was generated immediately.

In my opinion it just takes a while to generate the dynamic query, but why?

Are you sure the speed increase in running the query directly is not just due to SQL having cached the execution plan/data...?

Can you profile the code to see where the slow down is coming from ?

Matt

GetDbCount() wraps the query with a count(*) so the query results are actually created and counted. This might cause some slowdown, but it's the only reliable way to do a count of a resultset.

I indeed think you've seen the db return the value from cache, as GetDbCount()'s pipeline is pretty short (and the query creation is pretty quick (<1ms)

The GetScalar with the PK field is a different route, which could be a bit faster, but it's not always the most reliable way to do it (i.e. when the resultset has joins: the # of rows in the entire resultset is the same as doing select count(*) from (full query) )