Hi all, I am having a great concern over the use of LLBL to generate code for my application in terms of performance. My application would be having millions of data, using Oracle, and the largest table might consists of a few hundred thousands rows of data. As we are not given the way to modify the underlying "Update, select, delete" SQL statements using the generated code, how can we ensure that these are handled optimally and would not cause any latency in terms of speed?

Really hope to hear from you guy's valuable opinion. thanks

yj wrote:

Hi all, I am having a great concern over the use of LLBL to generate code for my application in terms of performance. My application would be having millions of data, using Oracle, and the largest table might consists of a few hundred thousands rows of data. As we are not given the way to modify the underlying "Update, select, delete" SQL statements using the generated code, how can we ensure that these are handled optimally and would not cause any latency in terms of speed?

Really hope to hear from you guy's valuable opinion. thanks

I don't quite follow what your concern is. The update/insert/delete statements are simple single-table targeting SQL queries like you would write them yourself as well. Insert/update work on only those fields which have changed.

Selects on millions of rows require indexes and paging. As oracle has efficient paging that's not a problem at all.

Could you give a specific example which in your eyes seems inefficient ?

Hi Otis, thanks a lot for the prompt reply. I have to clarify that i m still a newbie, yet needed to come out with a quick solution for my project, therefore i am interested in the the code generator. So based on your explanation, there should be no worries for me to use the code generator in terms of the performance issue, am i rite? (Provided that we have set up properly inside the Oracle database)

My other concerns are: 1) How do we log the errors if they happened to be coming from the generated code? 2) Based on my study, every row of the data from the table are retrieved all in once (for that particular row). If given a table with 50 attributes (e.g. name, address, attrib3...attrib50), and i would only need to retrieve the name and address, will the use of the EntityClass objects drag down the speed as all attributes will be fetched? 3) How does LLBLGen handle the SQL statements which needed Join and/or Union?

Thank you very much for the help.

yj wrote:

How do we log the errors if they happened to be coming from the generated code?

LLBL has a series of errors that will be thrown if it encounters a problem with the generated sql statement, for a full list search the docs for ORMException. there are exceptions for: out of sync entities concurrrency exceptions validation excepetions invalid sql statements etc.

It is your task to handel these errors. usually 1 of 3 methods is choosen 1. log4net 2. ent lib 1.0, 2.0 or 3.0 3. catch and ignore (not recommended )

yj wrote:

Based on my study, every row of the data from the table are retrieved all in once (for that particular row). If given a table with 50 attributes (e.g. name, address, attrib3...attrib50), and i would only need to retrieve the name and address, will the use of the EntityClass objects drag down the speed as all attributes will be fetched?

yes this is by design/definatetion of what an Entity represents. you have some options on how to retrieve partial rows using LLBL. 1. TypedLists (similiar to typed datasets they are defined at design time) 2. DynamicLists (defined at runtime and populate datatables) 3. Projections (project a result set onto a custom object [usually a generic list of dtos or entitycollection]) 4. use the LLBL designer to parse a single table into multiple entities. this can also create an entity heirachy which may be beneficial for your project. 5. use the ExcludedFields override when populating the collection (2.5 only)

yj wrote:

How does LLBLGen handle the SQL statements which needed Join and/or Union?

I haven't found a single sql statement that LLBL can't create. Granted this has required me to change how I design my queries or what object I use to design my queries. all in all I would say that LLBL has increased by query writing skills.

there are only 2 limitations I have found with LLBL 2.0 Sql Server. 1. case statements (now available in 2.5) 2. UDF's that return a table type. both can be agrued that this belongs in the business logic, not the db. And most times I agree. plus with scalar queries, prefetch paths and a rich predicate system. I find I don't need these anymore.

I haven't worked with Oracle so the limitations (I hesaite to use that term) may be different, and/or mute.

Thanks guys, u opened my eyes