Is it possible to simplify this query?

[mondo3a]

This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:

INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card
WHERE Card.CardID NOT IN
(
SELECT Shipped.CardID
FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID
WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007#
)
AND SystemID=10

 

[PHV]

Why a LEFT join ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

the thing most likely to slow it down is the IN statement, you can try to arrange your query to not use that, something like:

insert into table
select columns
from
tableA A inner join
tableB B on A.id = B.id
where
B.id is null

--------------------
Procrastinate Now!

 

[mondo3a]

Is this close to what you mean? Doesn't seem to do anything:

INSERT into SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT A.CardID,'Wpg',#1/1/2007#,0
FROM
Card A INNER JOIN
Shipped B on A.CardID = B.CardID
WHERE
B.ShippedFrom='Wpg' AND
B.ShipDate=#1/1/2007# AND
A.SystemID = 45 AND
B.CardID IS NULL

 

[PHV]

mondo3a, you didn't reply to my question:
why a LEFT join in your subquery ?

 

[mondo3a]

I used the Left because I wanted all the Cards that were in the Shipped table in that part of the query, but it should have been an inner join.

 

[maxhugen]

I found a big speed improvement by removing an IN from a query recently...

Save the subquery (and use an INNER join as PHV suggested), as say 'qryCardsShipped':

[blue]SELECT Shipped.CardID
FROM Card INNER JOIN Shipped ON Card.CardID=Shipped.CardID
WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007#[/blue]

In your main query, add this subquery and join it using an outer join, and test for a null on the outer side of the join:

[blue]INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card LEFT JOIN qryCardsShipped
ON Card.CardID=qryCardsShipped.CardID
WHERE SystemID=10
AND qryCardsShipped.CardID Is Null[/blue]


Max Hugen
Australia

 

[mondo3a]

Thanks everyone. The query now runs in seconds instead of minutes!

 

[mauricionava]

hello there...
Reading thru this thread remembered that one of my most used queries runs very slow.

Can you please help me fixing it like you did with mondo3a?
Thank you.

I see that my query has LEFT Join and maybe with the INNER Join it would speed up a little but I dont know how to do it.

Here's the code:

SELECT DISTINCT [Holdback Income Report New].DEALER, [Holdback Income Report New].BUYER, [Holdback Income Report New].ACCT, Sum([Holdback Income Report New].CURDUE) AS SumOfCURDUE, Sum([Holdback Income Report New].AMTPAID) AS SumOfAMTPAID, Sum([Holdback Income Report New].LATEFEE) AS SumOfLATEFEE, Sum([Holdback Income Report New].PRINPAID) AS SumOfPRINPAID, Sum([Holdback Income Report New].INTPAID) AS SumOfINTPAID, Sum([Holdback Income Report New].MISCPAID) AS SumOfMISCPAID, [Holdback Income Report New].RES_PCT, Sum([Holdback Income Report New].Hpay) AS SumOfHpay, Sum([Holdback Income Report New].GDoc) AS SumOfGDoc, Now()-[TRDATA.LASTPAID] AS DAYSAFTERPYMT, ([trdata.principal]-Nz([GP_AMT],"0")-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay] AS REMHB, Dealers.Name, TRDATA.BALANCE, IIf([TRDATA.CURRENTDUE]<0,0,[TRDATA.CURRENTDUE]) AS CURRENTDUE, Last([Holdback Income Report New].DATEPAID) AS LastOfDATEPAID, IIf([trdata.status]="C","X","") AS STATUS
FROM Dealers INNER JOIN ((([Holdback Income Report New] INNER JOIN HBKPaid ON [Holdback Income Report New].ACCT = HBKPaid.ACCT) INNER JOIN TRDATA ON [Holdback Income Report New].ACCT = TRDATA.ACCT) LEFT JOIN paytypelookup ON [Holdback Income Report New].PAYTYPE = paytypelookup.Paytype) ON Dealers.Short = [Holdback Income Report New].DEALER
GROUP BY [Holdback Income Report New].DEALER, [Holdback Income Report New].BUYER, [Holdback Income Report New].ACCT, [Holdback Income Report New].RES_PCT, Now()-[TRDATA.LASTPAID], ([trdata.principal]-Nz([GP_AMT],"0")-[trdata.docfees])*([trdata.res_pct])/100-[HBKPaid.hpay], Dealers.Name, TRDATA.BALANCE, IIf([TRDATA.CURRENTDUE]<0,0,[TRDATA.CURRENTDUE]), IIf([trdata.status]="C","X","")
ORDER BY [Holdback Income Report New].RES_PCT;

 

[PHV]

but I dont know how to do it
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886