Hi there,

We are looking at adopting your products. From what we can tell though we are still locked into the database at a stored procedure level i.e. There is not database agnostic way to create the stored procedures so that it truly is database agnostic.

Can you tell me whether you product can actually do this in any way or whether you know of another product that could work in conjunction with yours to allow us to make a truly database agnostic system.

Thanks ALan Meier

generating stored procs for CRUD operations is a planned feature but at the moment we don't have it. DB Agnostic work with stored procs is somewhat difficult however: oracle returns cursors, sql server returns resultsets, and not all db's have the possibility to return multiple resultsets. Oracle's proc interface uses PL/SQL, which doesn't work with precision/scale (a NUMBER(10, 3) is simply 'NUMBER') while other systems do support all type info on proc parameters.

If you have the procs already, you can map them to calls and map the resultsets to typedviews in llblgen pro. A stored procedure call can be mapped to a procedure in multiple databases (e.g. oracle and sqlserver) and to an extend use type converters to convert e.g. a bool value to NUMBER() on oracle, as oracle doesn't support bool.

However entity operations with procs aren't supported natively, you have to manually call a proc with the fields. This can be somewhat cumbersome.

I'm not sure what you mean with 'locked into the stored procedure level' though. Could you elaborate on that please?

For procs, one simple system is usable, which is iBatis, but requires a lot of hand-writing config code. It's about dynamic adapt to the model vs. have a rigid api layer with procs: if the api is procs, there's not really a model, it's simply a matter of creating calling code for the procs. This can be tedious in the end, so unless the restrictions are very clear, I'd seriously consider looking into using O/R mapper generated sql queries, which are db agnostic (as they're generated by a db specific engine).

Thanks for the reply.

I was concerned with the speed of execution by having to pull data back to process it on the client side and was looking for a solution where the ORM would give me the choice to handle the processing within the database if this was possible.

The main benefit of stored procedures that I can see is execution speed on the server side

alanm wrote:

Thanks for the reply.

I was concerned with the speed of execution by having to pull data back to process it on the client side and was looking for a solution where the ORM would give me the choice to handle the processing within the database if this was possible.

The main benefit of stored procedures that I can see is execution speed on the server side

That doesn't make a difference. In modern databases, parameterized queries and procs are both compiled to an execution plan which is cached: if the same parameterized query or proc is executed, the same execution plan is executed. This has advantages for optimization as the execution plan can be tweaked at runtime by the db engine based on the live statistics of the data involved.

So if you do: INSERT INTO Foo (Field1, Field2) VALUES (@V1, @V2)

or

CREATE PROCEDURE pr_InsertFoo(int @v1, int @v2) AS INSERT INTO Foo (Field1, Field2) VALUES (@V1, @V2) GO

that doesn't make a difference: calling pr_InsertFoo or executing the parameterized query doesn't make a difference, performance wise.

For an O/R mapper there are advantages which stored procs don't have. For example, if I have a 10 field table Customers, and I want to update a customer, and want to change the contactname field, the query executed will be: UPDATE Customers SET ContactName = @p1 WHERE ID = @p2

If you want to do stored procedure updates for this, you have to either - write for each update scenario in your application a stored procedure, this can be cumbersome and tedious or - write a single stored procedure and use nullable parameters and coalesce. This is much slower than the update above.

Another example is graph fetching. Say I want all customers from germany and their orders, I can fetch that in 2 queries and get the graph accordingly. With stored procedures I can't, as I don't know the filter on the customers to fetch the orders.

So the queries you get with an O/R mapper are much more tailored towards the situation at runtime and therefore can be more optimal. Due to the parameterized nature, they're not slower than procs, and you don't have to write the procs As a bonus, they're automatically db agnostic: they're generated at runtime, so you write the code once, and you can utilize it on the db's supported by your o/r mapper framework.

It is important to keep in mind that the queries generated from an O/R mapper could need tweaking. This is because you can query a database in numerous ways and from the abstraction level of .net code, it might be a simple query which might result in a slow db query. Luckily in many cases you can tweak an o/r mapper query to make it faster, e.g. by rewriting it a bit, so the sql generated is more optimal. With a proc, you can change it in the proc directly, which is an advantage of procs. However, with a larger system, with a lot of tables, the # of procs you have to deal with is quickly getting out of hand. 3000 or more procs is not an exception.

Most O/R mappers support executing sql on the DB directly and/or a call to a proc as well, for situations where you need to consume a lot of data for example: in that situation it's not feasible to pull the data to the client, consume it there in a method and send it back. A good example is when you want to update a lot of rows with an expression based on a filter. It's easier to simply run 1 Update query than to first fetch all entities, update all properties and then send them back. Our own framework for example supports that kind of queries, besides stored procedure calls, for the situations when you want to write a stored procedure.

Hopefully this explains things a bit.

Thanks yes that is helpful. We will download the trial version and will let you know the outcome