Hi,

We are being like a year working with LLBLGen (Selfservicing) and we notice a slow performance in Oracle when using a the LLBLGen and a grid with more tha 500 records. It can take more than a minute to come up or change paging between a group of records.

If we use the asp.net SQLDataSource it work really fast making the reading/displaying or changing between pages in a second.

1. what can be?
2. What i can do to verify the problem?
3. Any parameter that needs to be declared/set?

it is really challeging this situation that we are considering changing to another OR/Mapper

thanks for the help..

PD. If you need access to the application to see the difference in performance let me know.

Elvin

Hi Elvin,

This is because the lazy-loading nature of the SelfServicing template set paradigm. At some point the grid or your own code is triggering lazy-loading causing LLBLGen to execute fetches on every fetched entity (as many rows as you grid has).

First we need to evaluate what is going on. To do that show us how are you binding the collection into the grid. Are you using LLBLGenProDatasource? Please show us the relevant aspx and the code behind.

Also, enable sql tracing to identify what are the queries that are actually executed. That would give you a hint of what is causing the overhead. This usually is a Field mapped on related field (f.i., a custom Order.CustomerName field that is actually accessing Order.Customer.Name, which leads to an extra fetch) or something like that.

It could also be the grid trying to explore the nested properties of each object. What grid (vendor) are you using?

1. there's a way to turn off this "lazy-loading"?

2. Yes, we are using the LLBLGenProDatasource. Here is an example declaration. The Parameter just give filter the list by a given state. But this situation happens in all pages no matter if we use filters or not. Most of them are only linked to one table.

<llblgenpro:LLBLGenProDataSource ID="dsZip" runat="server" DataContainerType="EntityCollection" EntityCollectionTypeName="DARenovaSolutions.CollectionClasses.SysZipcodeCollection, DARenovaSolutions"> <SelectParameters> <asp:SessionParameter Name="State" SessionField="varState" /> </SelectParameters> <UpdateParameters>

</UpdateParameters>
<InsertParameters>
    <asp:SessionParameter Name="State" SessionField="varState" />
</InsertParameters>

</llblgenpro:LLBLGenProDataSource>

1. This situation happens in all pages no matter if we use filters or not. 99% of all LLBLGenDatasources used are only linked to one table.

2. We try the MS Grid, ComponetOne Grid, Telerik Grid, all of them with the same slow result using the LLBLGenProDatasource. If I use the SQLDatasource the response is like 90% more faster (1-3 seconds) compare to 1-2 minutes.

3. If you still have the code you can see the ZipCode page.

Is recommended to use another type of connection instead of the SelfServicing??

thanks,

Hi Elvin,

I tested your zipCode page. The only query generated related to zipcodes is:

SELECT 
    "RENOVA"."SYS_ZIPCODES"."AREACODE" AS "Areacode", 
    "RENOVA"."SYS_ZIPCODES"."CITY" AS "City", 
    "RENOVA"."SYS_ZIPCODES"."COUNTY" AS "County", 
    "RENOVA"."SYS_ZIPCODES"."COUNTYANSI" AS "Countyansi", 
    "RENOVA"."SYS_ZIPCODES"."LAST_UPD_DATE" AS "LastUpdDate", 
    "RENOVA"."SYS_ZIPCODES"."LAST_UPD_TERMINAL" AS "LastUpdTerminal", 
    "RENOVA"."SYS_ZIPCODES"."LAST_UPD_USER_NAME" AS "LastUpdUserName", 
    "RENOVA"."SYS_ZIPCODES"."STATE" AS "State", 
    "RENOVA"."SYS_ZIPCODES"."STATEANSI" AS "Stateansi", 
    "RENOVA"."SYS_ZIPCODES"."TIMEZONE" AS "Timezone", 
    "RENOVA"."SYS_ZIPCODES"."ZIPCODE" AS "Zipcode" 

FROM 
    "RENOVA"."SYS_ZIPCODES" 

WHERE ( ( ( "RENOVA"."SYS_ZIPCODES"."STATE" = :p1))) 

ORDER BY 
    "RENOVA"."SYS_ZIPCODES"."ZIPCODE" ASC,
    "RENOVA"."SYS_ZIPCODES"."COUNTY" ASC
    
Parameter: :p1 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "AE".

It's only one query, you don't have any related fields on related entities associated in the Grid. Can you reproduce your problem in the ZipCode.aspx? I can't

Please enable tracing to see in what pages you get the performance problem:

<configuration>
  ...
  <system.diagnostics>
    <switches>  
      <add name="OracleDQE" value="4" />    
      <add name="ORMPersistenceExecution" value="3" />
    </switches>
  </system.diagnostics>
  ...
</configuration>

You also could use ORMProfiler.

Also check performance tuning

to get pointers how to find performance bottlenecks.

David,

Almost all pages with the grid just generate a simple query to a single table. Performance is seen in all pages with the grids.

If you are running local it looks fine but when you post it to a website you notice the differece. If you run the query directly on the database there is no performance problem. If you run it using the SQLDatasource there is no problem. Problem comes when you use the LLBLGen.

I will sent you an email so you can see the difference in 2 pages that have the same code but one with LLBLGen and the second with SQLDataSource.

Any setting to turn off the "Lazy Loading"??

Elvin.

David,

i sent you an email.

Elvin

Hi Elvin,

Your posted links do have a difference in the retrieval time, however is not that much (5s vs. 8s). Also we couldn't make sure just by seeing the application. I tested ObjectDataSource vs. LLBLGenProDataSource locally and there is not any relevant difference. I know it's local but I measure the time on each approach using ORMProfiler. Aslo the generated SQL doesn't have any relevant difference.

The code I have here didn't use paging. So I added paging, and it works great. I see from your above snippet that you are not enabling paging on the LLBLGenProDataSource, that means that the whole resulset is fetched the first time and the paging is controlled on the client by the grid control. So, my guess is that the poor performance you are experiencing is due to the whole control state data traveling on whatever you have the cache at (viewstate or session). To make sure we have to see your Grid aspx, as I think I don't have your final version of it.

To avoid this, enable paging on the datasource:

<llblgenpro:LLBLGenProDataSource ID="dsZip" runat="server" DataContainerType="EntityCollection" EntityCollectionTypeName="DARenovaSolutions.CollectionClasses.SysZipcodeCollection, DARenovaSolutions" EnablePaging="true" >
    <SelectParameters>
        <asp:SessionParameter Name="State" SessionField="varState" />
    </SelectParameters>
</llblgenpro:LLBLGenProDataSource>

... Doing that, LLBLGen will generate code to fetch only the data in the involved page (say 10 records).

You should also check the Generated SQL to see what is going on behind the scenes, see my link about tracing above.

I will test it tomorrow and will let you know...

thanks,

Elvin

Sorry for the delay...was out the country...

after enabling the Paging of the server the performance get a lot better, but still 2-3 seconds behind if i use the datasource control that came with VS2010.

Here is the SQL that LLBL generate:

Generated Sql query:

    Query: SELECT * FROM (SELECT a.*, rownum r___ FROM (SELECT "RENOVA"."SYS_ZIPCODES"."AREACODE" AS "Areacode", "RENOVA"."SYS_ZIPCODES"."CITY" AS "City", "RENOVA"."SYS_ZIPCODES"."COUNTY" AS "County", "RENOVA"."SYS_ZIPCODES"."COUNTYANSI" AS "Countyansi", "RENOVA"."SYS_ZIPCODES"."LAST_UPD_DATE" AS "LastUpdDate", "RENOVA"."SYS_ZIPCODES"."LAST_UPD_TERMINAL" AS "LastUpdTerminal", "RENOVA"."SYS_ZIPCODES"."LAST_UPD_USER_NAME" AS "LastUpdUserName", "RENOVA"."SYS_ZIPCODES"."STATE" AS "State", "RENOVA"."SYS_ZIPCODES"."STATEANSI" AS "Stateansi", "RENOVA"."SYS_ZIPCODES"."TIMEZONE" AS "Timezone", "RENOVA"."SYS_ZIPCODES"."ZIPCODE" AS "Zipcode" FROM "RENOVA"."SYS_ZIPCODES" WHERE ( ( ( "RENOVA"."SYS_ZIPCODES"."STATE" = :p1))) ORDER BY "RENOVA"."SYS_ZIPCODES"."ZIPCODE" ASC) a WHERE rownum < :p5) WHERE r___ >=:p3
    Parameter: :p1 : AnsiString. Length: 2. Precision: 0. Scale: 0. Direction: Input. Value: "AL".
    Parameter: :p5 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 834.
    Parameter: :p3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 817.
Method Exit: CreatePagingSelectDQ

why it has 3 "SELECT FROM"?

eirizarry wrote:

Sorry for the delay...was out the country...

after enabling the Paging of the server the performance get a lot better, but still 2-3 seconds behind if i use the datasource control that came with VS2010.

Hi Elvin. The LLBLGenProDataSource inherits from the MS DataSourceControl, so there is not much difference in what how it works in conjunction with data containers controls. The difference is that it converts entities into databound control readable data and back. So, maybe the only performace difference would be the entity's entityView translation, but it (the time difference) shouldn't be that big. Please test it with some profiler software like ORMProfiler to see whether it's a relevant performance difference.

eirizarry wrote:

why it has 3 "SELECT FROM"?

This is the way the paged query is built.