Manipulating datatables in memory

Manipulating datatables in memory

Postby john@haldi.net » Wed 22 Aug 2007 15:35

Is there a way to do JOINS and GROUP BY on memory resident data tables? My scenario is as follows:

I have a table 'positions' located on one MySQL db server ('server1'), and another table 'parameters' located on another MySQL db server ('server2'). I want to select the entire positions table do a GROUP BY on the 'symbol' field so that all similar positions are grouped. Then I want to do a LEFT JOIN from positions to parameters.

I have constructed this so that it retrieves the table in a grouped fashion and then marries the parameters table to the resulting grouped positions table. My problem is that this process is very slow, and the process needs to every few seconds to present the user with updated information.

I would rather pull both the positions and parameters table into memory in their entirety, and then perform the GROUP BY and JOIN in memory.

Is there a way to create a DataTable or DataView quickly that mimics the GROUP BY query? Is there a way to do a join between two tables that reside on two entirely different db servers?

Any suggestions would be greatly appreciated.

Thanks,

John
john@haldi.net
 
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby Alexey » Mon 27 Aug 2007 06:43

Is there a way to create a DataTable or DataView quickly that mimics the GROUP BY query?
We don't know anything about such possibility, unfortunately.
Is there a way to do a join between two tables that reside on two entirely different db servers?
This hardly can be done with InnoBD or MyISAM engines. Federated storage should be used instead.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL