Insert only unique records to new table

[Guest]

Hello,

I have following query it just work fine and faster with say below 100
records or so. But I want the same query to
run in a table where there are about 50 fields and the number of records are
about 10,000. The query works but it is very slow.
Can anybody please advise how to make this query run faster with some in the
logic. I want to insert only unique records from customer to orders.

INSERT INTO ORDERS (ID, NAME, ADDRESS, ORDERNO)
SELECT ID, NAME, ADDRESS, ORDERNO
FROM CUSTOMER
WHERE ID NOT IN (SELECT ID FROM ORDERS);

Please advise.
Rakesh

 

[Gary Walter]

I have following query it just work fine and faster with say below 100
records or so. But I want the same query to
run in a table where there are about 50 fields and the number of records
are
about 10,000. The query works but it is very slow.
Can anybody please advise how to make this query run faster with some in
the
logic. I want to insert only unique records from customer to orders.

INSERT INTO ORDERS (ID, NAME, ADDRESS, ORDERNO)
SELECT ID, NAME, ADDRESS, ORDERNO
FROM CUSTOMER
WHERE ID NOT IN (SELECT ID FROM ORDERS);

"NOT IN" can be slow...

one alternative:

INSERT INTO ORDERS (ID, NAME, ADDRESS, ORDERNO)
SELECT
C.ID,
C.NAME,
C.ADDRESS,
C.ORDERNO
FROM
CUSTOMER AS C
LEFT JOIN
ORDERS AS O
ON
C.ID = O.ID
WHERE
O.ID IS NULL;

 

[MGFoster]

Hello,

I have following query it just work fine and faster with say below 100
records or so. But I want the same query to
run in a table where there are about 50 fields and the number of records are
about 10,000. The query works but it is very slow.
Can anybody please advise how to make this query run faster with some in the
logic. I want to insert only unique records from customer to orders.

INSERT INTO ORDERS (ID, NAME, ADDRESS, ORDERNO)
SELECT ID, NAME, ADDRESS, ORDERNO
FROM CUSTOMER
WHERE ID NOT IN (SELECT ID FROM ORDERS);

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put an index on the ID column in Customer and Orders. Try a query like
this:

INSERT INTO ORDERS (ID, NAME, ADDRESS, ORDERNO)
SELECT C.ID, C.NAME, C.ADDRESS, C.ORDERNO
FROM CUSTOMER AS C LEFT JOIN ORDERS AS O
ON C.ID = O.ID
WHERE O.ID IS NULL

The LEFT JOIN and the WHERE clause will cause the query to select all
rows in Customer where the Customer.ID value is not in Orders.

JOINs are usually faster than using sub-queries.

The best design of a table is usually "narrow is better than wide."
Which means it is better to have a small number of columns in a table,
'cuz it takes less time to retrieve a small number of columns than it
takes to retrieve a large number of columns. This obviously affects the
speed of queries. Also, a large number of columns in a table usually
indicates a bad table design. Try to re-design the table using the
Normal Form design concepts.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRR7ETYechKqOuFEgEQK81wCfRbSgLIOe3Uu5ocTqhvT63CNF6qUAoN53
S7yDvCSGMgCaZW5ApWa6B9sX
=dQKb
-----END PGP SIGNATURE-----