1.11M Members

Timing Out - Compare 2 tables

 
0
 

I've got 2 tables, basically this weeks stock list and last weeks. Each has around 25,000 SKU's. I'm trying to run a comparison programme to see where there are stock-outs i.e. SKU missing from the latest table. Table structures identical. Both about 20mb in size.

Script is timing out so wondered if there is a way to avoid this

<?php {foreach (glob('test.txt') as $fn) {unlink($fn);}}?>
<?php 
include 'db_dave.php';
$query = "SELECT con_inv_old.SKU
FROM con_inv_old
LEFT JOIN con_inv
ON con_inv_old.SKU = con_inv.SKU
WHERE con_inv.SKU IS NULL";
$result = mysql_query($query) or die (mysql_error() . "<br />Couldn't execute query: $query");
$num = mysql_num_rows($result);
$i = 0;
while ($i < $num) { 
$EAN = mysql_result($result, $i, "con_inv_old.SKU");
$File = "test.txt";
$Handle = fopen($File, 'a');

$Data = $EAN;
fwrite($Handle, "$Data\r\n");

fclose($Handle);
 $i++;
}
mysql_close();
?>
 
0
 

SKU is indexed I assume.

You can try this to see if it makes a difference (it avoids a costly join):

SELECT `SKU`
FROM `con_inv_old`
WHERE `SKU` NOT IN (
    SELECT `SKU`
    FROM `con_inv`
    WHERE `SKU` IS NOT NULL
)
 
0
 

ahouldn't even need the where on the inner query

 
0
 

No, but it reduces the result set.

 
0
 

Thanks for the code.

This works after I had reduced the table size - not enough processing power.

SELECT b.SKU
FROM con_inv_old b
WHERE b.SKU NOT IN (
    SELECT a.SKU
    FROM a.con_inv
    WHERE b.SKU IS NOT NULL
)

The list gives me those in "b" (old data) that are not in "a" (new data). I now want to use the list of those not in "a" to select data from "b" but it is timing out again.

SELECT b.SKU, a.SKU, a.quantity
    FROM con_inv_old b
    WHERE b.SKU NOT IN (
        SELECT a.SKU
        FROM a.con_inv
        WHERE b.SKU IS NOT NULL
    )

Or is my code wrong.

Question Answered as of 1 Year Ago by pritaeas and jstfsklh211
 
0
 

what data are you trying to select
probably what you want is

SELECT SKU, quantity
FROM con_inv_old
WHERE SKU NOT IN (
    SELECT SKU FROM con_inv
)

this should give you the sku and quantity for each item missing from con_inv

@pritaeas
the only the only null sku's would have been the ones from the outer join
without the outer join the where is not null should be extra

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article