Another problem in migrating from DBISAM 4 to ElevateDB.

In DBISAM 4 I had a Select statement that had an Inner Join. One table
was on disk, the other table in Memory.

For example :-
  SELECT Category, ...
  FROM TASKS t INNER JOIN "\MEMORY\List" l
  ON t.Number = l.Number
  ORDER BY Category

The table Tasks is on disk in a database say MyDatabase.
The table List is in memory in a database say MyDatabaseMem.

In ElevateDB both these databases are in the one configuration file. I
get the error "401 The table or view \MEMORY\List does not exist in the
schema Default".

If I read the manual for the Select statement correctly then the tables
need to be in the same database. Obviously they are not.

So how do I mix disk and memory tables with ElevateDB?

Do I have to have both on disk in the database MyDatabase?

I have a lot of memory tables so need to apply the solution to all of them.

Regards,
  Peter Evans

Hi Peter,

> In ElevateDB both these databases are in the one configuration file. I get
> the error "401 The table or view \MEMORY\List does not exist in the schema
> Default".

When you created the database in memory, you gave it a name.  You need to
reference the database name instead of \MEMORY\ when you specify the table
in the join, so your query becomes...

SELECT Category, ...
 FROM TASKS t INNER JOIN "DBNAME"."List" l
 ON t.Number = l.Number
 ORDER BY Category

Just replace DBNAME with the name of the memory database and you should be
good to go.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
---------------------------------------

On 30/08/2013 4:03 PM, Terry Swiers

> Just replace DBNAME with the name of the memory database and you should
> be good to go.

Thank you for that advice. I will try it out this weekend.

Regards,
  Peter Evans

>
> Thank you for that advice. I will try it out this weekend.

The Select is now working. Thank you very much.

Regards,
  Peter Evans

Peter,

Two things I'd like to point out when using memory tables that may not be obvious if you are using EDB C/S.

If you are using C/S, then the memory tables are shareable and visible on the server just like a disk based table (EDB 12.3 or later). If you want the memory table to be visible only to the client, then create the memory table as Temporary. The Temporary memory tables are STILL stored on the server machine.

So if you want the fastest (non-shareable) memory tables possible, the temporary memory database should NOT be in a C/S database because memory tables are stored on the server machine and the TCP/IP operations takes time and you are also transferring the data from the memory table from the server to the client over the network. For speed, the non-shareable memory table should be stored in a local memory database. (I'm assuming EDB can join a table in a local database to a table in a C/S database - I haven't tried it.)

Hope this helps.

Barry

Barry

(I'm assuming EDB can join a table in a local database to a table in a C/S database - I haven't tried it.)

Bad assumption. ElevateDB can JOIN tables across databases but not across sessions and since you'd need two sessions to achieve your suggestion (1 local, 1 remote) its a no go.

The other point is that joining two tables both located on the server (one memory, one disk) is joining two tables on their local machine so no network traffic for the joining. If you join one local and one remote one of them would have to be transfered over the network in its entirety. Hence with C/S it makes sense for the memory table to reside on the server not the client.


Roy Lambert [Team Elevate]

Roy Lambert wrote:

>>(I'm assuming EDB can join a table in a local database to a table in a C/S database - I haven't tried it.)

>Bad assumption. ElevateDB can JOIN tables across databases but not across sessions and since you'd need two sessions to achieve your suggestion (1 local, 1 remote) its a no go.<

You're right.

>The other point is that joining two tables both located on the server (one memory, one disk) is joining two tables on their local machine so no network traffic for the joining. If you join one local and one remote one of them would have to be transfered over the network in its entirety. Hence with C/S it makes sense for the memory table to reside on the server not the client.<

Right again. I think this deserves a technical article by either yourself or Tim.

I tried using EDB memory tables a few months back and saw no speed performance on C/S. This was because the client spent too much time waiting for the data from the server. My client app was manipulating the data locally. Now I suppose I could have written a procedure on the server to do it but it was rather complicated. This would have eliminated the network traffic and TCP/IP delays. If I was more proficient at EDB scripts, this may have been the best approach. To borrow a quip from Vegas "What happens on server, stays on server".

I ended up using kbmMemTable which was 10x faster than a C/S memory table. I could have used a local EDB session and kept the memory local and it would have been faster than a C/S memory table, but it still would not have been as fast as kbmMemTable.

Barry

Barry


I use F/S rather than C/S (I keep threatening to move but never have) so I don't know exactly what can be done, however, you want to check out external procedures written in Delphi but called via SQL.

Roy Lambert [Team Elevate]

On 9/1/2013 2:24 PM, Barry wrote:
> I could have used a local EDB session and kept the memory local and it would have been faster than a C/S memory table, but it still would not have been as fast as kbmMemTable.

Did you test the speed difference ? I'm curious what makes you think
local EDB in-memory table would not be as fast ?

BTW - Serious question not picking on the post - we use in-mem tables
extensively in our DBISAM app and if we do ever move to EDB we'd want
the EDB in-mem to be as fast as possible (meaning we can ask Tim to
investigate if kbmMemTable is really a lot faster).

Raul

Raul


If you can produce a test case I'm happy to convert to ElevateDB and compare. I'll need data and code.

Roy Lambert [Team Elevate]