rs.MoveLast freezes after a complex SQL statement.

[mepaco]

I am executing a SQL query that is rather complex because it has to do with overlapping parts on a computer board. After the SQL statement the next thing I do is move the recordset to the last record, but it freezes. Here is the code:

Set rs1 = db.OpenRecordset("Select TVia.X, TVia.Y From TVia, TCompPad, TPadStackPads 
Where TVia.X Between TCompPad.X + XOffset - (Width / 2) And TCompPad.X + XOffset + (Width / 2) And 
TVia.Y Between TCompPad.Y + YOffset - (Height / 2) And TComPad.Y + YOffset + (Height / 2) And 
Layer = 1 And TCompPad.PadID = TPadStackPads.PadID", vbOpenSnapshot)
 
rs1.MoveLast

The program runs through the SQL statement fine, and pretty quickly. However, it freezes as soon as it hits the .MoveLast. I was wondering if anybody had any ideas as to why it would be hanging up on that statement.

[jim mcnamara]

No surprise there. It has to process all of the recordset to get to the last.

What the DB engine is doing: return the first rows matching the query because it is a complex one. Usually the prefetch queue is 10 or 20 rows - that's how many are returned for messy queries.
Blame it on the optimizer.