Hi there Guru's,
I posted this same topic last year (almost to the date!)
First try! [webmasterworld.com]
I've got an issue that's driving me mad, this code:-
Select serialnumber from
serial
where id_number not in
(SELECT serialnumber FROM newbuild
Works fine on my mock table that's got about 20 rows in it (trialing the code first), then when I try it on my test server which compares the data of one table (serial, approx 6000 rows), to the other (newbuild, approx 10000 rows).
This takes in excess of 1 min. 50 secs to complete, and constantly returns ALL rows from the serial table.
I am stumped; the two rows being evaluated are both primary keys in their repective tables and are both ints.
I've checked the collation of each table and made sure that they are identical in all structure.
Why would it work on 20 rows with a 4 row check where 1 row isn't in the 20 row table!
Is there a more efficient way of checking for data present in on table that isn't in another?
Failing this; I will re design the database to be more fluent and less clunky! I've inherited this system from someone who wasn't that bothered about longevity of task.
Thanks,
MRb
