Efficient storage of large data sets

[khards]

Hi all,

 

I am investigating ways to save on memory usage within the export function of our application.

 

To cut a long story short it is desirable for the export feature to fetch a large data set from MYSQL.

 

We are storing the data in an array using mysql_fetch_array with the MYSQL_ASSOC parameter:

 

$data[]=mysql_fetch_array ($result,MYSQL_ASSOC)

 

This method seems to have a large overhead.

 

I have since setup a series of experiments

 

1.Replacing the associative indexing with numerical indexing. This seems to save around 2% memory usage in our case.

 

2.Storing the data into objects. The objects properties map directly to the array. This seems to save around 22%.

 

3..Storing the data into objects. The objects properties names replaced with constants (see examp[le below) This seems to save around 40%.

 

define ('NAME', 'a');
define ('ADDRESS', 'b');
define ('NUMBER', 'c');
define ('CONST', 'd');

$items = NULL;

Class item
{
function __construct($name,$address,$number,$const)
{
	$this->NAME=$name;
	$this->ADDRESS=$address;
	$this->NUMBER=$number;
	$this->CONST=$const;
}

var $NAME;
var $ADDRESS;
var $NUMBER;
var $CONST;
}

for ($i=0;$i<300000;$i++)
{
$items[] = new item("keith hards".$i,"Number ".$i,$i,"1002223");
}

 

Is there a more compact storage method than the example above? Does this seem like a sensible thing to do?

 

 

 

[ignace]

I find it hard to believe that #3 save's more memory then #2. Anyhow maybe Flyweight will do it for you?

[khards]

Thanks for the reply. My mistake, during my experiments I had made a mistake, I have corrected this now.

 

I have decided upon the following solution:

 

<?
//Memory usage 399999 items = 262,291,392 bytes used

$items = NULL;

Class item
{
function __construct($name,$address,$number,$const)
{
	$this->name=$name;
	$this->address=$address;
	$this->number=$number;
	$this->const=$const;
}

var $name;
var $address;
var $number;
var $const;
}

for ($i=0;$i<400000;$i++)
{
$items[] = new item("keith hards".$i,"Number ".$i,$i,"1002223");
if ($i>300000)
	echo $i." = ".memory_get_usage()."\n";
}

?>

 

This is far more efficent than using the associative arrays

 

<?
$items = NULL;

for ($i=0;$i<400000;$i++)
{

$items[]['name']="keith hards".$i;
$items[]['address ']="Number ".$i;
$items[]['number']=$i;
$items[]['const']="1002223";

if ($i>220000)
	echo $i." = ".memory_get_usage()."\n";
}

?>

 

The method using associative arrays can't even make 300,000 items before I run out of memory (I could increase memory, however I am just experimenting)

 

Using associative 242,626 Itmes takes = 268,417,688 Bytes

Using objects      242,626 Items takes = 157,168,672 Bytes

 

Objects take 58.5% less memory in this example!

 

[khards]

There is an error in the above.

 

The optimum solution is this :

 

$items = NULL;
for ($i=0;$i<400000;$i++)
{
$items[$i]=Array("keith hards".$i,"Number ".$i,$i,"1002223");
}

 

This takes the lease memory