I need a more efficient version of this query

M

mscertified

This query runs very slow. There are around 19,000 records in each table.
Both TicketNo and TicketID are indexed.

SELECT TicketNo, Keywords
FROM tblCSTickets
WHERE TicketNo NOT IN (Select TicketID from tblKWXref)
ORDER BY keywords;

Does anyone know how to speed this up?
 
J

Jason Lepack

It's probably the ORDER BY that's taking so long,

Take the Order By Statement off and see if it gets a lot better, if so
then you may want to put an index on Keywords.

Cheers,
Jason Lepack
 
J

John Spencer

Try a "frustrated" outer join query.

SELECT T.TicketNo, T.Keywords
FROM tblCSTickets as T LEFT JOIN tblKWXRef as K
ON T.TicketNo = K.TicketID
WHERE K.TicketID is Null
ORDER BY keywords;

Not In queries are slow. There is not a lot you can do to speed them up.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

mscertified

Wow! that made an amazing difference, it cut the time down from over 5
minutes to about 1 second. Thanks.
 
Top