Insert only unique records to new table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rakesh Parekh - 30 Sep 2006 09:21 GMT

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

Reply to this Message

Gary Walter - 30 Sep 2006 11:05 GMT

> I have following query it just work fine and faster with say below 100
> records or so. But I want the same query to
[quoted text clipped - 9 lines]
> 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;

Reply to this Message

MGFoster - 30 Sep 2006 20:23 GMT

> Hello,
>
[quoted text clipped - 9 lines]
> FROM CUSTOMER
> WHERE ID NOT IN (SELECT ID FROM ORDERS);

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 **

Reply to this Message