Hi All,

We’re a new user of ElevateDB and are in the process of converting an existing enterprise application based on .NET and Entity Framework (using services to spread computational work and resources across many servers) into a desktop version of the same. The existing implementation uses Oracle but we found it unsuitable for this desktop purpose and are converting to ElevateDB for both enterprise and desktop. For the most part it has been very straightforward – the existing application does not depend heavily on any Oracle specific functionality that we couldn’t find Elevate versions… except one.

The application does a lot of heavy math on many rows in a table. The .NET application will connect to the database, pull a lot of rows (many thousand), do whatever computations they do, and then insert the results back into the database. Most of the time this process is completely under the control of .NET. The developers access the database via Entity Framework, do computations, update collections that represent the tables and then EF does the work of pushing the changes back to the database. This is generally a slow process, but in most cases it is “good enough.” There are a few places in the code where it’s not enough, and in those cases there’s a procedure on the database side that is called that can do bulk inserts. Basically each column in the table is sent to the procedure as a separate array, and Oracle has a mechanism (FORALL…INSERT) to “jam” the rows in quickly. Performance is significantly faster when rows are inserted this way.

As far as I can tell ElevateDB does not have the equivalent functionality. I thought I saw that arrays could be passed as parameters but I think I was mistaken, and that they can only be used WITHIN procedures/functions. Is that correct?

I realize a better long term approach to this problem is to instrument and see what makes these particular processes slower but for our first step we’re looking for as close as possible to a 1:1 conversion and then refactor. Obviously in this case we can’t and so we’ll probably end up just using Entity Framework in these cases, but I wanted to ask the assembled wisdom if there’s a better Elevate pattern we should be looking at if the existing bottleneck appears after converting.

Note: We are in the process of hooking up our application to ElevateDB now, so I can’t give actual timings from Elevate, only experience with our existing application.

Thanks,
Brian (AGI)

On 12/4/2019 2:34 PM, AGI wrote:
>
> The application does a lot of heavy math on many rows in a table. The .NET application will connect to the database, pull a lot of rows (many thousand), do whatever computations they do, and then insert the results back into the database. Most of the time this process is completely under the control of .NET. The developers access the database via Entity Framework, do computations, update collections that represent the tables and then EF does the work of pushing the changes back to the database. This is generally a slow process, but in most cases it is “good enough.� There are a few places in the code where it’s not enough, and in those cases there’s a procedure on the database side that is called that can do bulk inserts. Basically each column in the table is sent to the procedure as a separate array, and Oracle has a mechanism (FORALL…INSERT) to “jam� the rows in quickly. Performance is significantly faster when rows are inserted this way.
>

Answer based on a quick first read but how much table locking would be
allowed during this process ?

In general EDB transaction caches all updates during transaction and
actual tables are changed only during commit.

Issue is that there is a lock on tables involved or on entire database
if it's non-restricted transaction.

So in theory you could do a pattern of
- start transaction (or restricted transaction if only subset of tables
needed)
- make all the changes (edit/delete/insert) to tables in the app session
- commit - at which point data is written back

see
https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Transactions
for more info



Raul

AGI


This is a user supported forum. Tim pops in occasionally but generally its users supporting users. With something like this I'd suggest talking directly with Tim directly. You get some support calls when you buy ElevateDB - use one of them and open a support ticket.

If there's any budget available I'd seriously consider asking Tim toi develop a custom sultion for your needs.

Roy Lambert

Thanks for the responses all. I think while trying to be clear I over complicated my post. We will follow up with Tim directly.

If anyone has done any work around needing to shift large chunks of data into the database from client to server via a procedure I'd still be interested in hearing about your experience.

Thanks

Brian,

<< As far as I can tell ElevateDB does not have the equivalent functionality. I thought I saw that arrays could be passed as parameters but I think I was mistaken, and that they can only be used WITHIN procedures/functions. Is that correct? >>

Correct.

<< I realize a better long term approach to this problem is to instrument and see what makes these particular processes slower but for our first step we’re looking for as close as possible to a 1:1 conversion and then refactor. Obviously in this case we can’t and so we’ll probably end up just using Entity Framework in these cases, but I wanted to ask the assembled wisdom if there’s a better Elevate pattern we should be looking at if the existing bottleneck appears after converting. >>

The ElevateDB .NET Data Provider doesn't have EF support, so that will be a problem.  However, please send me an email (support@elevatesoft.com) with more details and we can discuss options for you.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> doesn't have EF support

Worth mentioning that few do. I tried to take a working EF application from SQL Server to another database and it just failed horribly. I asked my colleague who knows more about it and he said that EF was pretty much tied to SQL Server in reality. And it is a pain all over in my experience. Unlike when I used ElevateDB in an application (no EF) which was nice and solid for me due to being all "embedded".

--

Matthew Jones

"Matthew Jones" wrote:

Tim Young [Elevate Software] wrote:

> doesn't have EF support

Worth mentioning that few do. I tried to take a working EF application from SQL Server to another database and it just failed horribly. I asked my colleague who knows more about it and he said that EF was pretty much tied to SQL Server in reality. And it is a pain all over in my experience. Unlike when I used ElevateDB in an application (no EF) which was nice and solid for me due to being all "embedded".

--

Matthew Jones

--

I agree with you on all points, especially the pain points We have an internal resource who wrote the needed code to get it working so we'll be using that for now, although I hope that eventually we will drop it.

Matthew,

<< Worth mentioning that few do. I tried to take a working EF application from SQL Server to another database and it just failed horribly. I asked my colleague who knows more about it and he said that EF was pretty much tied to SQL Server in reality. And it is a pain all over in my experience. Unlike when I used ElevateDB in an application (no EF) which was nice and solid for me due to being all "embedded". >>

I really, really tried to implement it when it was first released, but it was going to end up being a many-month project that replicated much of the parsing/AST manipulation that already exists in the EDB engine, and I just had to punt for the time being.  It has always struck me as a bit odd, as I would consider knowing SQL a good thing that needn't be hidden away from the developer.  There *are* valid reasons for preventing arbitrary SQL construction for server-side web applications, namely SQL injection, but those are easily-solved issues.

Tim Young
Elevate Software
www.elevatesoft.com