SQL question

[Ron Hinds]

I have the following SQL statement. It runs *very* slow, we're talking 5
minutes or more. TblInventory has a little over 21,000 rows and
tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
how to improve the performance of this statement.

strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
quoCurrent, quoVendorPartnumber )" _
& " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
Expr3, TblInventory.invItemID" _
& " FROM TblInventory" _
& " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
tblVendorQuote WHERE quoVendorID='1')"
db.Execute strSQL

 

[Danny J. Lesandrini]

The NOT IN clause is going to be slow. Better to use a LEFT JOIN, if you can
make it do what you need. I've tried to rewrite the SQL below, as I believe it
should be written. Try grabbing the SELECT piece of it and pasting it into the
QBE. If it returns the data you want, test it for performance against the other
SQL with the NOT IN () clause.

INSERT INTO tblVendorQuote (
quoPartnumber
, quoVendorID
, quoPrice
, quoCurrent
, quoVendorPartnumber )

SELECT TblInventory.invItemID, '1', 0, True, TblInventory.invItemID
FROM TblInventory LEFT JOIN tblVendorQuote
ON tblVendorQuote.quoPartNumber = TblInventory.invItemID
WHERE tblVendorQuote.quoPartNumber IS NULL
AND tblVendorQuote.quoVendorID='1'

 

[Ron Hinds]

Thanks Danny. Unfortunately, that statement won't work. That was what was
there originally, but if the quoPartNumber IS NULL, then so also will be the
VendorID (the two of them form a unique primary ID). I did however solve the
speed issue by creating a Query (qryVendorQuote) that selects quoPartNumber
and quoVendorID WHERE quoVendorID='1'. I then use that in a similar
statement to the one you proposed and there is no speed issue:

INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
quoCurrent, quoVendorPartnumber )
SELECT TblInventory.invItemID, '1' AS VendorID, TblInventory.COST, True AS
Current, TblInventory.invItemID
FROM TblInventory LEFT JOIN qryVendorQuote1 ON TblInventory.invItemID =
qryVendorQuote1.quoPartnumber
WHERE qryVendorQuote1.quoPartnumber Is Null;

Again thank you for the effort!

 

[Guest]

TblInventory.invItemID most likely should be indexed if it is not now.

As indexes slow down inserts, try removing indexes from tblVendorQuote. Keep
primery keys and unique constraints of course. The tblVendorQuote.quoVendorID
field may or may not benefit from indexing. One way to tell if an index is
slowing down an insert is to just run the SELECT statement and see how long
it takes.

If a NOT IN statement is slow, often changing it to a NOT EXISTS statement
speeds things up. See if the following runs quicker than the original select
statement.

SELECT TblInventory.invItemID,
'1' AS Expr1,
0 AS Expr2,
True AS Expr3,
TblInventory.invItemID
FROM TblInventory
WHERE NOT EXISTS (SELECT *
FROM tblVendorQuote as VQ
WHERE TblInventory.invItemID = VQ.quoPartNumber
AND VQ.quoVendorID='1') ;

 

[Ron Hinds]

You're right, Jerry - NOT EXISTS runs much faster than NOT IN! Interesting -
thank you!

 

[John Vinson]

I have the following SQL statement. It runs *very* slow, we're talking 5
minutes or more. TblInventory has a little over 21,000 rows and
tblVendorQuote has over 25,000 rows. I'm hoping someone has a suggestion on
how to improve the performance of this statement.

strSQL = "INSERT INTO tblVendorQuote ( quoPartnumber, quoVendorID, quoPrice,
quoCurrent, quoVendorPartnumber )" _
& " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2, True AS
Expr3, TblInventory.invItemID" _
& " FROM TblInventory" _
& " WHERE TblInventory.invItemID NOT IN (SELECT quoPartNumber FROM
tblVendorQuote WHERE quoVendorID='1')"
db.Execute strSQL

The NOT IN clause can be very poky - apparently the optimizer doesn't
handle it all that well. You might want to try a "frustrated outer
join". Make sure that quoPartNumber and invItemID are indexed
appropriately in their respective tables, and try

strSQL = "INSERT INTO tblVendorQuote" _
& " (quoPartnumber, quoVendorID, quoPrice, quoCurrent," _
& " quoVendorPartnumber)" _
& " SELECT TblInventory.invItemID, '1' AS Expr1, 0 AS Expr2," _
& " True AS Expr3, TblInventory.invItemID" _
& " FROM TblInventory" _
& " LEFT JOIN tblVendorQuote " _
& " ON tblVendorQuote.quoPartNumber = tblInventory.invItemID" _
& " WHERE TblVendorQuote.quoPartNumber IS NULL" _
& " AND quoVendorID='1'"
db.Execute strSQL

I'm not sure where the quoVendorID = '1' fits - if it's a criterion on
a field in tblVendorQuote you may need to use a left join to a
Subquery rather than directly to the quote table.

John W. Vinson[MVP]