Mauro


I'm not sure what point you're trying to make here or what question it is you're trying to ask. So here's some general gibberish....

A good way to approach the concept of parallelism for databases is, unless the database is SPECIFICALLY written to be parallel, to consider each action as atomic and requiring full isolation. I know there are some database engines that are written to have queries running in parallel but they are not that many and are generally very expensive.

Take a query: unless the database is written to split the query into several parts, carry out each part individually and then merge the results back together you can't parallelise it.

Take any sort of transaction: these rely on locking access (write at least) to the data whilst the update is taking place. It would be very difficult to spread that locking across several cores or computers.

Until such time as someone offers a parallelised database engine specifically for Delphi I suggest using standard threads as now. Accept you cannot use the new TParallel to do anything significant with unless you are prepared to put a lot of time an effort into it.

Roy Lambert

>This wouldn't surprise me. There is always an overhead to using parallel code, sometimes that overhead is greater than the gains.<

Usually, yes, unless the program has to wait for a request to complete from another process. One area I think threads would help is if the client software is executing a lot of queries against the C/S database. I find my C/S application does a lot of waiting (50%) because of the TCP/IP overhead. One or two additional threads in the program could make use of this wait time to execute their own queries against the server.

Just my 2 cents.

Barry

Elevate DB's threading model is fundamentally flawed (or perhaps antiquated) and is inherently incompatible to XE7's parallel library.

Tasks are executed in a pool of threads that is maintained 100% by the RTL based upon capabilities of the system at runtime.  There is no way to specify that a specific thread should pick up a particular task, so short of creating new edb sessions for each task you want to run, it's not going to work.

I wrote a helper unit that will get around this to some degree for our purposes, but it's prone to memory leaks and stale connections if the RTL decides it no longer needs threads in the thread pool.

Mauro Botta wrote:

Hi Tim

i can't use parallel programming of XE7 with EDB 2.
if i use in sub function ( isPrime ) edb component class, parallalel don't work more.


how i can manage it edb for tipical database procedure ?





function IsPrime (N: Integer): Boolean;
var
Test: Integer;
begin


Ho i can create EDB Table / Session here ?


IsPrime := True;
for Test := 2 to N - 1 do
  if (N mod Test) = 0 then
  begin
    IsPrime := False;
    break; {jump out of the for loop}
  end;
end;


 Tot := 0;
 Ticks := GetTickCount;

 TParallel.For(1, Max, procedure (I: Int64)
   begin
     if IsPrime (I) then
       InterlockedIncrement (Tot);
   end); // ,Pool);

 Ticks := GetTickCount - Ticks;

 Memo1.Lines.Add (Format (  'Parallel for: %d - %d', [Ticks, Tot]));

Ideal Software Systems wrote:

> Elevate DB's threading model is fundamentally flawed (or perhaps
> antiquated) and is inherently incompatible to XE7's parallel library.

Hmm, I'm not sure I'd put it that way around. ElevateDB does what every
other database I've used requires, and that is one connection (session)
per thread. If someone introduces a new threading mechanism, it is down
to that to resolve how to fit into this common paradigm, or to explain
the caveats. I think I'd say that a mechanism to "unroll" a for loop
(or similar) is not one that is automatically designed to do lots of
database operations. You'd be better off building a queue of requests,
and having another thread pool servicing those requests. But that takes
design of an architecture, something which the parallel library appears
to be trying to sweep under the carpet IMO. It's a nice facility, but
not a panacea.

--

Matthew Jones

> Hmm, I'm not sure I'd put it that way around. ElevateDB does what every
> other database I've used requires, and that is one connection (session)
> per thread. If someone introduces a new threading mechanism, it is down

The limitation in Elevate is that the one connection must stay with the thread that created it.  That makes connection pooling essentially impossible.

Ideal Software Systems wrote:

> The limitation in Elevate is that the one connection must stay with
> the thread that created it.  That makes connection pooling
> essentially impossible.

Is that really unique? It seems reasonable to me - many things require
the same thread to keep state. The reason being of course that it is
hard to have every aspect thread-safe beyond the designed locks etc.

And you can have a pool of connections managed by worker threads, but
it is a different architecture then - as I suggested, you put in
requests, and another thread does the database work (which could cache
or whatever).

--

Matthew Jones

Ideal Software Systems wrote:

>The limitation in Elevate is that the one connection must stay with the thread that created it. That makes connection pooling essentially impossible.

Not quite right, but if you mean you can't just throw it at Delphi's wonderful new parallel thingy you're spot on. However, as I intimated earlier on in this thread your expectations are incompatible with the price point of ElevateDB and Delphi.

Can you describe just what it is you want to move into parallel? I for one would be very interested.

Roy

> Is that really unique? It seems reasonable to me - many things require
> the same thread to keep state. The reason being of course that it is
> hard to have every aspect thread-safe beyond the designed locks etc.

The rule is generally one connection used by one thread at a time. This allows you to create a pool of connections and acquire when entering some thread work then releasing it back to the pool when finished.

In Elevate, you need to create a new connection every time you enter the thread work and free it when finished. This has huge overhead compared to a connection pooling mechanism.

The issue would be moot if you weren't locked into using a session only on the thread that created it.  It's a small distinction but with huge implications for anything where performance or parallelism is important.

> Not quite right, but if you mean you can't just throw it at Delphi's wonderful new parallel thingy you're spot on.

I am quite right.  Should you not maintain the rule of using a session only in the thread that created it, your code becomes riddled with Error 506 from Elevate.  The rule as espoused by Tim in answer to the problem...  every session must only be used by the thread that created it.  An unworkable solution for parallelism and pooling.

> However, as I intimated earlier on in this thread your expectations are incompatible with the price point of ElevateDB and Delphi.

MySQL, Firebird and Postgres are all free (as is MSSQL Express).  Each of those allow connections to be pooled and used from threads that did not create the connections so long as it is in use by one thread at a time.  My expectations are that something that costs significantly more than free would be at least as functional.

> Can you describe just what it is you want to move into parallel? I for one would be very interested.

The limitations for me pre-date the parallel library. Try working Elevate into any semi-scalable web server environment (or api server) and you'll quickly hit the same wall we did.  

But if you want a specific example where the parallel library would be useful in a typical gui application...

Imaging a data entry form with 15 lookup fields.  Each lookup query takes 200ms to run.  Showing that form would take a minimum of 3 seconds to show, feeling very sluggish.  If you ran all 15 queries in parallel, your form would show in the amount of time the longest query took to run, in this case a minimum of 200ms.

Ideal

>I am quite right. Should you not maintain the rule of using a session only in the thread that created it, your code becomes riddled with Error 506 from Elevate. The rule as espoused by Tim in answer to the problem... every session must only be used by the thread that created it. An unworkable solution for parallelism and pooling.

I know the rules about isolarion, but I don't recall ever seeing anything about only use in the creating thread. I do pass connections round but I've always made sure its within thread context because I can't guarantee that its been closed down and everything released if I try and use it outside of its creator.  I can't find error 506 in the manual - what is it?

>> However, as I intimated earlier on in this thread your expectations are incompatible with the price point of ElevateDB and Delphi.
>
>MySQL, Firebird and Postgres are all free (as is MSSQL Express). Each of those allow connections to be pooled and used from threads that did not create the connections so long as it is in use by one thread at a time. My expectations are that something that costs significantly more than free would be at least as functional.

Wrong approach. If its free you have to ask why someone does it - clue - its not for financial reasons. Tim is trying to make money.  Do the three you've quoted all offer file sharing as well as client server? I now Firebird has some sort of embedded capability but I'm not sure if its multi user or not.

It will be interesting to see what happens with V3 since that's apparently going all c/s and will give Tim a lot more control over what happens.

>> Can you describe just what it is you want to move into parallel? I for one would be very interested.
>
>The limitations for me pre-date the parallel library. Try working Elevate into any semi-scalable web server environment (or api server) and you'll quickly hit the same wall we did.
>
>But if you want a specific example where the parallel library would be useful in a typical gui application...
>
>Imaging a data entry form with 15 lookup fields. Each lookup query takes 200ms to run. Showing that form would take a minimum of 3 seconds to show, feeling very sluggish. If you ran all 15 queries in parallel, your form would show in the amount of time the longest query took to run, in this case a minimum of 200ms.

Interesting example, and it explains why so many web pages I visit are so slow.

However, to be my normal bloody awkward self, Why are you populating 15 lookups when a form is shown? If they're sensitive queries then they could have been opened in idle time, or when a lookup is called (by lookup I'm assuming you mean something like a drop down) if they're not sensitive you should definitely be populating them at drop down so that they are fresh.

In your best case parallel scenario you have a minimum of 200ms - in my populate when needed I have a minimum of 0 <VBG>

Roy