1,105,375 Community Members

Timing Out - Compare 2 tables

Member Avatar
eman neercs
Newbie Poster
17 posts since Nov 2009
Reputation Points: -1 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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();
?>
Member Avatar
pritaeas
mod_pritaeas
11,305 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,833 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
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
)
Member Avatar
jstfsklh211
Junior Poster
155 posts since Apr 2011
Reputation Points: 38 [?]
Q&As Helped to Solve: 35 [?]
Skill Endorsements: 2 [?]
 
0
 

ahouldn't even need the where on the inner query

Member Avatar
pritaeas
mod_pritaeas
11,305 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,833 [?]
Skill Endorsements: 155 [?]
Moderator
Featured
Sponsor
 
0
 

No, but it reduces the result set.

Member Avatar
eman neercs
Newbie Poster
17 posts since Nov 2009
Reputation Points: -1 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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
Member Avatar
jstfsklh211
Junior Poster
155 posts since Apr 2011
Reputation Points: 38 [?]
Q&As Helped to Solve: 35 [?]
Skill Endorsements: 2 [?]
 
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