I need a more efficient version of this query

G

Guest

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
..
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Why do I get a prompt when running this query? 1
Join on a UNION query 2
how can I use a table as a list of records not to includ in a qry 3
Is a query on a word in a memo field possible ? 11
Filter based on results of a parameter query 1
Heavy data munching - is there a more efficient way of doing this? 8
query runs very very slow 4
is this query efficient 1

Top