simple query taking so long to load

[buratti]

I have a simple query where ultimately my goal is to count the number of certain items sold between certain dates, but I am having problems before I can even get to that. It is a simple invoice/invoice item query but it is taking unusually long to open even before even I apply any filters to it. When I do apply filters, it takes even longer to load load/open the query.
Here is the Query without any filters...

 SELECT [Invoice].TxnID, [Invoice].TxnNumber, [Invoice].TxnDate,  [Invoice].RefNumber, [Invoice].DueDate, [InvoiceLine].Size
FROM [Invoice] RIGHT JOIN [InvoiceLine] ON [Invoice].SimpleID = [InvoiceLine].SimpleID

When opening the query it "opens" quickly, but if I try to scroll down or click the Last record button, it takes anywhere from 3 to 10 minutes to "load" the rest of the records. when it does complete the final number of records is 4849. Separately the invoice table holds 2689 records and the invoiceline table holds the same 4849. That doesn't seem like too large of a number, and I have dealt with queries that returned 100,000+ records which responded just fine. Other queries in the database run just fine, it just seems that when using those two tables together (invoice and invoiceline) I get this problem. Any suggestions as to what is making this so slow and how to fix it?

 

[CJ_London]

A couple of things:

Not sure why you are using a right join - suggest changing it to a left join or inner join. What your query is returning is all invoiceline records and an invoice record where it exists - normally this would be the other way round - all invoices and an invoiceline where it exists (left join) or all invoices and invoicelines where there is a match (inner join)

Is [InvoiceLine].SimpleID indexed? if not, then index it - duplicates OK

is [Invoice].SimpleID indexed? I presume it is your primary key in which case it should be but you also have a field called TxnID so maybe it isn't

 

[buratti]

Thanks for you response. I decided to go a whole other route with this. I don't need to go into the details since im not using it anymore, but I think my problem had to do with several dlookup() and insrt() functions used. You see the original tables were actually queries based on the original tables but these queries included several additional calculated fields which used the functions above as well as a few custom VBA functions to return the appropriate values needed. I am not 100% sure this was the problem but most likely the culprit. Either way, I decided to modify the original tables to "hard code" the results of those function into the original table whenever a new record was created as well as update all existing records. Seems to be running fine now.

 

[CJ_London]

if you were using domain functions (dlookup, dsum etc) it certainly will slow things down if you have anything but a few records. Don't pretend to understand what you are trying to acheive but hard codeing results is not always a good idea - I would investigate the use of subqueries