sybase_unbuffered_query and memory

[cmay4]

I have a problem with large memory usage for sybase_query under php 4.2.3. I have a query which returns about 120,000 rows with about 30 fields (I am doing a data dump to csv). After doing a little poking around, I realized that the sybase_query function actually executes the query, loops over the entire result set and holds it in memory. When I run my query, the apache daemon gets up to almost 200MB of memory!

I read about 4.3 adding a function called sybase_unbuffered_query. I don't have the new php installed at work yet (next week I'm told). I was looking at the source code, and it looks like it doesn't load all the records as once. However, if I'm not reading this wrong, it does look like it doesn't discard result rows after you've retrieved them. Which would mean that after looping over all the records in your result set you would be in the same boat as before with sybase_query.

Has anyone done any testing with the new sybase_unbuffered_query?

Thanks,

Chuck

[PyroX]

Could be your code technique, can we see an example?

[cmay4]

I don't think it's got to do with my code, since the memory spike happens within sybase_query. Here is the code:

<?php

sybase_min_server_severity(11);

$con = sybase_connect('peach5026d', 'may', 'maymay');
if (!$con)
    die("Could not connect to the database: " . @sybase_get_last_message());

if (!sybase_select_db('nci_dev', $con))
    die("Could not select database: " . @sybase_get_last_message());

$fp = fopen('test.csv', "w");   // open the output file
if (!$fp)
    die('Error opening file for creation');
else {
    $sql = "SELECT v.sample_id, v.seq_num, v.parent_id, v.current_label, v.label_status, v.study_id,
                   v.repos_id, v.division, v.mat_type, v.vial_status, v.warning, v.batch_id, v.volume,
                   v.volume_unit, v.volume_est, v.freezer, v.rack, v.box, v.row, v.col, v.tests, v.thaws,
                   v.hemolyzed, v.date_processed, v.date_received, v.date_entered, v.date_modified,
                   v.msc_id, s.subject_id AS samp_subject_id, s.date_drawn, s.date_last_modified
            FROM   vial v, sample s
            WHERE  v.sample_id = s.sample_id
            AND    v.study_id = 'ACC'";

$query = sybase_query($sql, $con);
if (!$query)
    die('Error getting dump data: ' . sybase_get_last_message() . '\n\nSQL:\n\n' . $sql);

$max = sybase_num_fields($query);
while($row = sybase_fetch_row($query)) {
    $elements  = array();
    $sample_id = $row[0];
    $seq_num   = $row[1];

    for ($j = 0; $j < $max; $j++)
        $elements[] = $row[$j];

    fwrite($fp, implode(',', $elements) . "\r\n");
}
@sybase_free_result($query);

fclose($fp);   // close the file
}

@sybase_close($con);
?>