Hello,

I'm right now evaluating using llblgen 2.6 with server side paging in asp.net using LLBLGenProDataSource2

Basically the asp.net markup looks like:

 <llblgenpro:LLBLGenProDataSource2 ID="llds" runat="server" DataContainerType="EntityCollection"
        EntityFactoryTypeName="[factory]"
        EnablePaging="True" AdapterTypeName="[adapter]">
    </llblgenpro:LLBLGenProDataSource2>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="llds">
    </asp:GridView>

Unfortunately, I get very bad performance using an oracle db. I cannot atm trace what sql commands LLBLGen creates, but the performance is as if no DB-Server Side paging is performed at all and the query takes serveral minutes. 

I had a look at the query that LLBLGen generates on SQL Server, and the code seems suboptimal, first doing a select top XX into a temptable, then reading from this temptable. This would read the entire table into a temptable first when paging to the last page, no? I've heard you could switch that to use CTEs instead but I have not investigated that further as my problem really is with Oracle only. 

As I said, I can't trace the Oracle Server atm, but I suspect that the LLBLGen query generates isn't really paging on the server. Even the first page takes several minutes to load. Of course, not paging also takes serveral minutes so I suspect no server side paging is used at all. 

I will also try to enable DQE tracing, perhaps that will give some further insights.

Until then, can anyone confirm that what I'm doing should work, that server side paging on oracle is supported in 2.6 and if not, if this is supported better in the new and shiny 3.x version (that unfortunately I'm currently not allowed to use by management).

Thanks Ben

How can you evaluate v2.6 when the demo for v2.6 hasn't been available since may 2010?

bens wrote:

I'm right now evaluating using llblgen 2.6 with server side paging in asp.net using LLBLGenProDataSource2

Basically the asp.net markup looks like:

 <llblgenpro:LLBLGenProDataSource2 ID="llds" runat="server" DataContainerType="EntityCollection"
        EntityFactoryTypeName="[factory]"
        EnablePaging="True" AdapterTypeName="[adapter]">
    </llblgenpro:LLBLGenProDataSource2>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="llds">
    </asp:GridView>

Unfortunately, I get very bad performance using an oracle db. I cannot atm trace what sql commands LLBLGen creates, but the performance is as if no DB-Server Side paging is performed at all and the query takes serveral minutes. 

switch tracing on by enabling DQE tracing through a config change

Paging on Oracle is done using ROWNUM based paging.

I had a look at the query that LLBLGen generates on SQL Server, and the code seems suboptimal, first doing a select top XX into a temptable, then reading from this temptable. This would read the entire table into a temptable first when paging to the last page, no? I've heard you could switch that to use CTEs instead but I have not investigated that further as my problem really is with Oracle only. 

SQL Server compatibility in v2.x is set to sqlserver 2000 by default. This uses a temptable, as 2000 doesn't support CTEs. Switching it to 2005 makes it use a CTE. The temp table approach is actually very fast, it has great performance even when paging over millions of rows. If you look closely, it doesn't insert all source rows into the temptable, just the ones up till the page to fetch. There's no other way to page on sqlserver with reliable results: CTE on 2005+ and temptable on 2000. yes, I know there are 'tricks' with @@rownumber and other shortcuts, but they don't work reliable on all queries.

As I said, I can't trace the Oracle Server atm, but I suspect that the LLBLGen query generates isn't really paging on the server. Even the first page takes several minutes to load. Of course, not paging also takes serveral minutes so I suspect no server side paging is used at all. 

Perhaps your query is simply slow due to the massive data you're consuming? Paging over a resultset which takes 3 minutes to create is not going to be lightning fast because you use paging: paging in general requires ordering, which can only be done after the projection has been completed. Distinct filtering also is required to avoid duplicates, which also takes place after the projection has been completed.

Paging really is for the data transportation and materialization of entities: if you want to display 10 rows out of a resultset of millions, you need to fetch only those 10, not the millions of rows. However to determine which 10 rows, the RDBMS has to do mainly all the work in the query and then do an extra projection of that resultset. This can make the optimizer do less work, but in general that's not the case (e.g. due to ordering of the resultset).

I will also try to enable DQE tracing, perhaps that will give some further insights.

DQE tracing will show you the generated SQL queries, so it's the first thing to look at.

Until then, can anyone confirm that what I'm doing should work, that server side paging on oracle is supported in 2.6 and if not, if this is supported better in the new and shiny 3.x version (that unfortunately I'm currently not allowed to use by management).

Paging does occur for oracle as well as any other supported DB. Only access doesn't page on the server side, as it has no construct to do so. All code looks at whether it can page on the server or not. It does that by checking whether DISTINCT is required (i.e. when your resultset returns duplicates) and if so, if DISTINCT violating types are in the resultset. If so, it switches to client-side paging on the datareader. This is simply fetching as much rows till the page is read. The extra overhead is in general rather minimal: if you fetch page 6 with 10 rows, 60 rows are fetch minimum and if you have a lot of duplicates, more than that, till the page you requested is read.

Oh, and rule number 1 for O/R mapper performance tuning: don't assume anything Performance tuning guide

Hello, thanks for the very quick and detailed response!

TL;DR:_It's not llblgen's fault that my paging performance is bad, rather the select count(*) query to get the record count is slow_

Otis wrote:

How can you evaluate v2.6 when the demo for v2.6 hasn't been available since may 2010?

To clarify: I'm evaluating llblgen in the context of a project I'm consulting for, they already have a llblgen generated DAL/BL. So I don't really evaluate llblgen (i.e. I don't use the demo version), rather I'm switching some of the existing DAL/BL to oracle. Or rather, some other dev created a layer for some oracle views, I'm simply trying to consume that data and have run into performance issues with some of the larger tables. Apparently the company now considers upgrading to llblgen 3.1, that why I was asking if things may be different in 3.1. I sure hope that the company has bought sufficient licenses for 2.6 and 3.1, but I'll check with my lead and/or the actual llblgen dev later on today Do I need a license even if simply consuming a llblgen layer?

Otis wrote:

SQL Server compatibility in v2.x is set to sqlserver 2000 by default. This uses a temptable, as 2000 doesn't support CTEs. Switching it to 2005 makes it use a CTE. The temp table approach is actually very fast, it has great performance even when paging over millions of rows. If you look closely, it doesn't insert all source rows into the temptable, just the ones up till the page to fetch. There's no other way to page on sqlserver with reliable results: CTE on 2005+ and temptable on 2000. yes, I know there are 'tricks' with @@rownumber and other shortcuts, but they don't work reliable on all queries.

Ok, great. I had no performance problems with the SQL server access and if I should ever get any, I will ask the llblgen guy here to set compatibility to 2005. But it's no issue for me atm, perf is fine

DQE tracing will show you the generated SQL queries, so it's the first thing to look at.

Cool, DQE is great and the generated query itself uses rownum, looks reasonable and is fast when I execute it manually.

The problem is that llblgen first executes a query to get the number of records, this query is very slow. But this is also the case if I do a

SELECT count(*) from table 

in SQL Devloper, so it's definitely no LLBLGen problem. Sorry for suggesting that it was!

I think I could disable that the llblgen datasource gets the record count first and/or set it manually using the OnPerformGetDbCount event, but unfortunately one of my requirements is that I have to display the total number of records and let users always jump to the first and last page. I think I may talk to the oracle db guys for advice, if there is a faster way to get the record count, perhaps by modifying the view or get the count from one of the underlying tables.

Thanks Ben

bens wrote:

Hello, thanks for the very quick and detailed response!

TL;DR:_It's not llblgen's fault that my paging performance is bad, rather the select count(*) query to get the record count is slow_

Otis wrote:

How can you evaluate v2.6 when the demo for v2.6 hasn't been available since may 2010?

To clarify: I'm evaluating llblgen in the context of a project I'm consulting for, they already have a llblgen generated DAL/BL. So I don't really evaluate llblgen (i.e. I don't use the demo version), rather I'm switching some of the existing DAL/BL to oracle. Or rather, some other dev created a layer for some oracle views, I'm simply trying to consume that data and have run into performance issues with some of the larger tables. Apparently the company now considers upgrading to llblgen 3.1, that why I was asking if things may be different in 3.1. I sure hope that the company has bought sufficient licenses for 2.6 and 3.1, but I'll check with my lead and/or the actual llblgen dev later on today Do I need a license even if simply consuming a llblgen layer?

You only need a license for operating the designer / code generator, so not for using the generated code.

In v3.0 and v3.1 we have made further performance improvements, which might come in handy, but as your problems are DB based, it's not help you in this specific case, I'm afraid.

DQE tracing will show you the generated SQL queries, so it's the first thing to look at.

Cool, DQE is great and the generated query itself uses rownum, looks reasonable and is fast when I execute it manually.

The problem is that llblgen first executes a query to get the number of records, this query is very slow. But this is also the case if I do a

SELECT count(*) from table 

in SQL Devloper, so it's definitely no LLBLGen problem. Sorry for suggesting that it was!

Ah I see Yes that's unexpected perhaps. The count query is done once to avoid the overhead with every page action. If you set the LivePersistence on the datasource to manual, you can avoid the query, and simply return a number. This might not be correct, but who's counting the rows? . I.e., you could cache the number if things are really slow with the count() query and return the number using this mechanism.

I think I could disable that the llblgen datasource gets the record count first, but unfortunately one of my requirements is that I have to display the total number of records and let users always jump to the first and last page. I think I may talk to the oracle db guys for advice, if there is a faster way to get the record count, perhaps by modifying the view or get the count from one of the underlying tables.

There's no direct way to get a faster row count from a table, but if you're executing the count() on a view, it might be costly indeed, if the view is using a lot of joins. perhaps the # of rows is equal to the # of rows in a table used in the view, which could be made fast by doing a count on the PK of that table using a scalar query

Yes, it's a somewhat complex view, unfortunately. I just tested it with a more restricted filter, so that getting the row count becomes fast, and things work like a charm. It sure beats having to implement server side paging myself!

If I can't get the count faster by going via an underlying table or by some other means, perhaps we can simply drop the "show record count and allow jumping to the last page" requirement for pages where we really need unfiltered selects on huge views, which shouldn't happen often anyway. I mean, does any user really want to page through 60000 pages? And to get to the last page, they can simply reverse the sort order...

Thanks again, Ben

bens wrote:

Yes, it's a somewhat complex view, unfortunately. I just tested it with a more restricted filter, so that getting the row count becomes fast, and things work like a charm. It sure beats having to implement server side paging myself!

If I can't get the count faster by going via an underlying table or by some other means, perhaps we can simply drop the "show record count and allow jumping to the last page" requirement for pages where we really need unfiltered selects on huge views, which shouldn't happen often anyway. I mean, does any user really want to page through 60000 pages? And to get to the last page, they can simply reverse the sort order... Thanks again, Ben

Be aware that the datasource control by default (using the property LivePersistence=true, which is the default) does calculate the count over the query to execute. To disable it, use LivePersistence=false. Documentation link