I will say that I am not a database expert so I really don’t know how to answer this question.

I am working on a demonstration to my group about the benefits of LLBLgen pro.

One question I get asked a lot is does it generate stored procedures to insert, update, and delete records to the database.

When I say it does not, I get “well that is the way to handle these functions, since the database stored procedures are much more efficient”.

How do I answer this question? I know that LLBLgen is optimized to handle this, but I am not sure how to answer how.

Richard

Heh. Frans will surely answer this, but the very short answer is that stored procs do not handle these functions more efficiently than dynamic SQL or parameterized queries. Stored procs are more efficient for heavy logic that requires a lot of data to get processed that's better kept on the server instead of sent over the wire. However, these functions are most definitely not your typical CRUD functions.

Here's a link to Frans' latest public response to the issue. Make sure to read the original article, then make your way through the comments. Frans' response begins in the second comment. http://www.theserverside.net/news/thread.tss?thread_id=31953

Jeff...

richardcherry wrote:

I will say that I am not a database expert so I really don’t know how to answer this question.

I am working on a demonstration to my group about the benefits of LLBLgen pro.

One question I get asked a lot is does it generate stored procedures to insert, update, and delete records to the database.

When I say it does not, I get “well that is the way to handle these functions, since the database stored procedures are much more efficient”.

How do I answer this question? I know that LLBLgen is optimized to handle this, but I am not sure how to answer how.

As Jeff pointed out, insert/delete and update procs aren't more efficient, at least not on SqlServer and the newer oracle boxes. This is because they're compiled when they're executed, not when they're stored. THis is the same for parameterized queries, like LLBLGen Pro uses. Both will be cached in compiled form and the execution plans of both will be re-used when they're re-executed, be it parameterized queries or procedures.

So you don't win anything by using a procedure in this case. DB2 compiles procedures to C code which is then compiled natively, which can make a bit of a difference, though I doubt it will be much, as runtime-optimization is impossible when the procedure is compiled.

Procedures actually can be slower in the update area. LLBLGen Pro produces update queries which only update the fields which have to be updated. This means that if you have a 10 field table and 1 field is changed, just 1 field is updated. In procedures you have to work around this by using nullable parameters. To filter out which field to set, you often have to use slower constructs as you have to setup the tests for the null-ed parameters. Furthermore, you can also just update all fields (which is often done) but that requires you to send all the data to the database server for all update fields, while just 1 is perhaps changed.

The thread on theserverside.com was my last one about stored procedures. These debates are too hostile to waste any more time on. So I replied to the starter of the debate, the serverside.net had asked me if I wanted to do that, but it wasn't a pleasant discussion. Similar discussion was held in the reactions part of an ancient blogposting of mine, which is often quoted in these pro/con debates: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Thank you so much. I just hate people that say

Well "all the microsoft documentation says use stored procedure".

Why? because all the documentation says to use it. So why do they say to use it?

Performance!! Okay but parameterized queries and stored procedures are both cached in compiled form and the execeution plans of both will be re-used. So there is not a difference in performance.

But all the microsoft documentation says to use stored procedures.....

okay lets say for argument that you are correct. But we are not doing High transaction computing.

How about maintainability and flexiability. You change a field you have to update all your stored procedures. With LLBLGen you just regenerate your classes.

But "MICROSOFT documentation says to use stored procedures".

Any idea why "microsoft documenation says to use stored procedures?"

Richard

richardcherry wrote:

Thank you so much. I just hate people that say

Well "all the microsoft documentation says use stored procedure".

Why? because all the documentation says to use it. So why do they say to use it?

Performance!! Okay but parameterized queries and stored procedures are both cached in compiled form and the execeution plans of both will be re-used. So there is not a difference in performance.

But all the microsoft documentation says to use stored procedures..... okay lets say for argument that you are correct. But we are not doing High transaction computing.

How about maintainability and flexiability. You change a field you have to update all your stored procedures. With LLBLGen you just regenerate your classes.

But "MICROSOFT documentation says to use stored procedures".

Any idea why "microsoft documenation says to use stored procedures?"

Yes, stored procedures cause database-vendor lockin. When you generate SQL on the fly, you can switch sql engines and your app runs on Oracle. Though when you use stored procedures, porting them to oracle is a pain. The more hype that's created to use stored procedures the more lock-in is created. No database vendor will thus say: "Stored procedures are not the way to go", on the contrary

Otis wrote:

richardcherry wrote:

Thank you so much. I just hate people that say

Well "all the microsoft documentation says use stored procedure".

Why? because all the documentation says to use it. So why do they say to use it?

Performance!! Okay but parameterized queries and stored procedures are both cached in compiled form and the execeution plans of both will be re-used. So there is not a difference in performance.

But all the microsoft documentation says to use stored procedures..... okay lets say for argument that you are correct. But we are not doing High transaction computing.

How about maintainability and flexiability. You change a field you have to update all your stored procedures. With LLBLGen you just regenerate your classes.

But "MICROSOFT documentation says to use stored procedures".

Any idea why "microsoft documenation says to use stored procedures?"

Yes, stored procedures cause database-vendor lockin. When you generate SQL on the fly, you can switch sql engines and your app runs on Oracle. Though when you use stored procedures, porting them to oracle is a pain. The more hype that's created to use stored procedures the more lock-in is created. No database vendor will thus say: "Stored procedures are not the way to go", on the contrary

Given database vendors comment I think that is the REAL reason the documentation says use stored procedures. Plus I think it has to do with warning developers to not write there own SQL statements that are not paramitized and cause peformance problems and can have SQL inject attects peformed against them.

richardcherry wrote:

Well "all the microsoft documentation says use stored procedure".

This bring back memories... Stored procedures are bad, m'kay? WOW what a flame WAR!!

I have to say that the M$ documentation is also not always up to scratch and can sometimes be very confusing.

Like for Example:

SQL Server Architecture

In SQL Server version 6.5 and earlier, stored procedures were a way to partially precompile an execution plan. At the time the stored procedure was created, a partially compiled execution plan was stored in a system table. Executing a stored procedure was more efficient than executing an SQL statement because SQL Server did not have to compile an execution plan completely, it only had to finish optimizing the stored plan for the procedure. Also, the fully compiled execution plan for the stored procedure was retained in the SQL Server procedure cache, meaning that subsequent executions of the stored procedure could use the precompiled execution plan.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

And

Recompiling a Stored Procedure

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.

Is it just me or does these statements contradict each other?

My personal optinion about SProcs is that; they do have their place in an application and that you should not exclude SProcs just because "SProcs are bad". Sometimes they are convinient to use be cause you can make a logic change without recompiling and distributing your application but one thing that is for sure true is that LLBLGen makes it so much easier to do the normal things like inserts, updates and deletes.

Honestly both have their places, if your talking Fetch, insert, Update, Delete for 1 record or even returning a list of records then there really isn't a benefit to Stored Procedures, this is where LLBLGen shines in providing this functionality and allowing you to integrate it into your business layer.

In a high volume enviornment a well written stored procedure that loops through records is far more effecient than pulling back a huge set especially if you consider bandwith as an issue, looping through the records and processing various logic. On the flip side you lose a level of control and reusability of 'business logic'.

I find it most interesting where SQL Server 2005 is moving with CLR integration, I'm not sure where Frans is on the topic, but the shift back to centralized logic here could be substantial.

In 2005 I should be able to move my C# business layer onto the SQL Server and gain the benefit of local execution for bandwith/performance and keep my reusability I have with my existing infrastructure.

In 2005 you can write stored procedures in C# and they can automatically be exposed as web service interfaces, It will be a compelling argument for integrated business logic and ORM logic on SQL Server in my opinion.

Its my hope the SQL 2005 version of LLBLGen while may not come out of the box with this type of support a set of templates can be constructed to work in this manner (similar to a web services style today).

=)

John

jtgooding wrote:

Honestly both have their places, if your talking Fetch, insert, Update, Delete for 1 record or even returning a list of records then there really isn't a benefit to Stored Procedures, this is where LLBLGen shines in providing this functionality and allowing you to integrate it into your business layer.

In a high volume enviornment a well written stored procedure that loops through records is far more effecient than pulling back a huge set especially if you consider bandwith as an issue, looping through the records and processing various logic. On the flip side you lose a level of control and reusability of 'business logic'.

I find it most interesting where SQL Server 2005 is moving with CLR integration, I'm not sure where Frans is on the topic, but the shift back to centralized logic here could be substantial.

In 2005 I should be able to move my C# business layer onto the SQL Server and gain the benefit of local execution for bandwith/performance and keep my reusability I have with my existing infrastructure.

That's what I thought, but it's not true. I wrote about it some time ago: http://weblogs.asp.net/fbouma/archive/2004/09/12/228419.aspx

In short, you can write stored procs in C#, but no BL layer as you would now.

In 2005 you can write stored procedures in C# and they can automatically be exposed as web service interfaces, It will be a compelling argument for integrated business logic and ORM logic on SQL Server in my opinion.

This isn't possible I'm afraid. I also see absolutely no gain in writing procs in C#. On oracle you can write procs in Java for years, but almost no-one does this, for the sole reason that a set-based language like SQL is much more efficient.

When it comes to CRUD and reading lists of data, LLBL saves me a LOT of time and effort. Of course. Thanks Frans!

I am REALLY struggling with the not-so-easy stuff that I have always written procs for (note: I've been using LLBL seriously for only a few weeks now). I'm trying really hard to drink the Kool-Aid and stick to the LLBL methodology, but right now it is killing my productivity. For example, I am currently beating my head against a pretty basic issue: batch processing a queue of records on multiple threads for performance. I've written many variations of the query I need in SQL and can solve my current problem in minutes with something like....

BEGIN TRAN
    
    UPDATE
        QueueData
    SET
        status = 2,  -- in process
        process_id = @process_id
    WHERE
        id IN (
            SELECT TOP 200
                id
            FROM
                QueueData
            WHERE
                status = 1  -- ready
            ORDER BY 
                [whatever]
        )

    SELECT
        [whatever]
    FROM
        QueueData
    WHERE
        status = 2  -- in process
    ORDER BY 
        [whatever]

COMMIT

I've spent over an hour now fiddling with PredicateExpressions, FieldCompareSetPredicates, etc. trying to work out the LLBL equivalent to what I want. I can get close, but can't quite figure out how to get the TOP 200 restriction to work within the subquery. Maybe there's an easy (and performant) way of doing it that I can't figure out, but then, that's really my point. I already KNOW how to do it through SQL...

I'm curious from others where they draw the line. Do you work out EVERY data access call through LLBL, regardless of complexity? Surely there are some things that simply can't be done (or can't be done as efficiently) via LLBL that would be better done via direct SQL? Vendor independence is great, but getting my project done is probably more important at this point (for me at least).

Just curious if anyone else is struggling with this issue or not... Please don't take this as a complaint against the product -- I really like it. Just getting a little frustrated at the moment .

Here's another query that I cannot figure out. Seems simple in SQL...

SELECT  
    Preferences.PreferenceID,
    MemberPreferences.PreferenceValue
FROM
    Preferences
    LEFT JOIN MemberPreferences
    ON  (
        Preferences.PreferenceID = MemberPreferences.PreferenceID
        AND MemberPreferences.MemberID = @memberID
    )
)

Preferences is a reference table that lists all available user prefs. MemberPreferences relates preferences to users by ID. A given user may have no MemberPreferences saved yet, in which case I want to still return the entities for each available preference with NULL values.

I have tried every which way I can think of to get this to work using RelationPredicateBucket filtering. I can't seem to accomplish two main things: 1) getting the two predicates ANDed together within the SAME join clause (I can get two separate join clauses to show up), and 2) getting the memberID comparison to work as shown within the JOIN instead of in a WHERE clause (which makes the query logic incorrect). I don't see a way that I can even specify a comparison to a value like this within a JOIN clause through LLBL -- it looks like it always wants a specific table relation.

Should I be using a typed list instead of an entity collection, or does this even make a difference? I haven't tried typed lists yet.

I am really spinning my wheels and would appreciate any help. I had the SQL query working in Query Analyzer a long time ago, so again, I am starting to hit the limit of how much learning curve I can absorb into my project...

bmoeskau wrote:

When it comes to CRUD and reading lists of data, LLBL saves me a LOT of time and effort. Of course. Thanks Frans!

I am REALLY struggling with the not-so-easy stuff that I have always written procs for (note: I've been using LLBL seriously for only a few weeks now). I'm trying really hard to drink the Kool-Aid and stick to the LLBL methodology, but right now it is killing my productivity. For example, I am currently beating my head against a pretty basic issue: batch processing a queue of records on multiple threads for performance. I've written many variations of the query I need in SQL and can solve my current problem in minutes with something like....

BEGIN TRAN
    
    UPDATE
        QueueData
    SET
        status = 2,  -- in process
        process_id = @process_id
    WHERE
        id IN (
            SELECT TOP 200
                id
            FROM
                QueueData
            WHERE
                status = 1  -- ready
            ORDER BY 
                [whatever]
        )

    SELECT
        [whatever]
    FROM
        QueueData
    WHERE
        status = 2  -- in process
    ORDER BY 
        [whatever]

COMMIT

I've spent over an hour now fiddling with PredicateExpressions, FieldCompareSetPredicates, etc. trying to work out the LLBL equivalent to what I want. I can get close, but can't quite figure out how to get the TOP 200 restriction to work within the subquery.

Please use the overload of the FieldCompareSetPredicate constructor which accepts maxNumberOfItemsToReturn and set that to 200. A TOP 200 clause will then be emitted into the query. TOP has some restrictions which are checked, if ORDER BY is specified but these apply to plain SQL as well, so if you can write the proper SQL it should be ok and turn up in your query.

Maybe there's an easy (and performant) way of doing it that I can't figure out, but then, that's really my point. I already KNOW how to do it through SQL...

Your query is a multi-entity update query so you should definitely use the method UpdateEntitiesDirectly() on adapter or collection.UpdateMulti() if using selfservicing. That way the query is directly executed on the DB so you don't have to load the entities directly.

The select has to be specified separately. So first execute the update, then execute a getmulti(selfservicing) or fetchentitycollection(adapter)

I'm curious from others where they draw the line. Do you work out EVERY data access call through LLBL, regardless of complexity? Surely there are some things that simply can't be done (or can't be done as efficiently) via LLBL that would be better done via direct SQL? Vendor independence is great, but getting my project done is probably more important at this point (for me at least).

Just curious if anyone else is struggling with this issue or not... Please don't take this as a complaint against the product -- I really like it. Just getting a little frustrated at the moment .

Some things can be a bit difficult to specify in / with predicates etc. As a rule of thumb what not to convert I always use: if the SQL has to process a lot of data and the processing is simple to do in SQL, and the code to produce the alternative in LLBLGen Pro is very huge, it's more efficient to use a procedure for that, as the data doesn't have to leave the database server, i.e.: you don't have to fetch entities first, process them, save them back. Though if the processing is slow in SQL (and SQL is interpreted on most database systems) or the data to process isn't very huge, a proc is pretty meaningless, as it leaves an extra point of maintenance.

bmoeskau wrote:

Here's another query that I cannot figure out. Seems simple in SQL...

SELECT  
    Preferences.PreferenceID,
    MemberPreferences.PreferenceValue
FROM
    Preferences
    LEFT JOIN MemberPreferences
    ON  (
        Preferences.PreferenceID = MemberPreferences.PreferenceID
        AND MemberPreferences.MemberID = @memberID
    )
)

Preferences is a reference table that lists all available user prefs. MemberPreferences relates preferences to users by ID. A given user may have no MemberPreferences saved yet, in which case I want to still return the entities for each available preference with NULL values.

I have tried every which way I can think of to get this to work using RelationPredicateBucket filtering. I can't seem to accomplish two main things: 1) getting the two predicates ANDed together within the SAME join clause (I can get two separate join clauses to show up), and 2) getting the memberID comparison to work as shown within the JOIN instead of in a WHERE clause (which makes the query logic incorrect). I don't see a way that I can even specify a comparison to a value like this within a JOIN clause through LLBL -- it looks like it always wants a specific table relation.

You should check the documenation about 'Custom filters' for relations, please see: Using the generated code -> Adapter -> Filtering and sorting -> "Custom filters for EntityRelations".

Should I be using a typed list instead of an entity collection, or does this even make a difference? I haven't tried typed lists yet.

As you fetch fields from 2 different entities, you can't use an entitycollection for this, as entitycollections are used to fetch entity objects. You should use a dynamic list, or a typed list.

A dynamic list is setup pretty easily for your query:

// define the fields in the resultset
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(PreferenceFieldIndex.PreferenceID, 0, "PreferenceID");
fields.DefineField(MemberPreferenceFieldIndex.PreferenceValue, 1, "PreferenceValue");

// specify the relations and specify a custom filter for the join.
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(PreferenceEntity.Relations.MemberPreferencesUsingPreferenceID, JoinHint.Left)
    .CustomFilter = 
        new PredicateExpression(
            PredicateFactory.CompareValue(MemberPreferenceFieldIndex.MemberID,
                    ComparisonOperator.Equal, memberID));

// grab the results in a datatable.
DataTable results = new DataTable();
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchTypedList(fields, results, filter);

You can also define a typed list, you then have to add the custom filter like this:

RelationPredicateBucket filter = myTypedList.GetRelationInfo();
filter.Relations[0].CustomFilter = ....
adapter.FetchTypedList(myTypedList.GetFieldsInfo(), myTypedList, filter);

Thanks for the replies Frans. I will be out of town the next couple of days, but I will try your recommendations when I get back and let you know.

I really would like to get these queries working in LLBL but I have to admit that it gets frustrating spending hours on stuff I already "know how to do." I guess that's the price you pay when learning a new framework. It's just that I don't have anough of a grasp of it yet to know whether or not there is no way to do what I'm doing, or there is a way and I'm just too thick-headed to figure it out. After you spend an hour trying to write a simple LEFT JOIN you start to wonder...

bmoeskau wrote:

Thanks for the replies Frans. I will be out of town the next couple of days, but I will try your recommendations when I get back and let you know.

I really would like to get these queries working in LLBL but I have to admit that it gets frustrating spending hours on stuff I already "know how to do." I guess that's the price you pay when learning a new framework. It's just that I don't have anough of a grasp of it yet to know whether or not there is no way to do what I'm doing, or there is a way and I'm just too thick-headed to figure it out. After you spend an hour trying to write a simple LEFT JOIN you start to wonder...

I can imagine that . Though it's hard to describe a lot of 'from SQL to predicate' examples which you can fall back on, as SQL too has various ways to do the same thing, if you're used to method A, and B is described, it might not seem to be the one you're looking for.

It's best to divide the queries you want to port to LLBLGen Pro in a couple of groups: 1) entity fetch queries 2) list fetch queries (i.e. data from various entities in one list) 3) bulk update queries perhaps some others.

1) is done with Entitycollection objects fetched by adapter.FetchEntityCollection where you pass in a filter which defines the subset of the objects you'd like to fetch 2) is done by either typed lists or dynamic lists. 3) is done using adapter.UpdateEntitiesDirectly.

This might give you the proper enter points into the manual where to look.