Optimization of TOP-clause in SELECT

[AndersO]

Hi!

I'm a Swedish programmer, using MS Access in some applications. I encountered a strange phenomenon today. I would be most grateful if some Access/SQL "wiz" could help me out... :-)

I have a table with a lot of rows. The rows have a type field, and for every type of row there are a number of rows, each with a unique number (in a number field).

I want to retrieve the row with the LOWEST number, for a certain type. To make this search fast, there is an index in the table - sorting the rows by TypeField-NumberField.

I retrieve the row I want with this SQL-statement: "SELECT TOP 1 * FROM TableX WHERE TypeField='123' ORDER BY NumberField". The SQL parser should be able to find the row I want very quickly - using the index.

But this doesn't work. When there were only a couple of thousand rows of each row-type, everything seemed to work fine. But now we have almost 200 000 rows of one of the row-types - and a search for that type takes several seconds.

I thought the Access driver would optimize my query. And only read one record. The index contains the NumberField (within every type), so the first row found would be the one to return. But it seems like all 200 000 records are retrieved, and THEN the first row only is returned. I was hoping the optimizer would be smarter than that... :-)

Does anyone know what I can do to speed things up? E.g., is there anyway I can change my SELECT statment, to make my search go faster? Or something I could change in the database?

Best regards

Anders, Sweden

[GolferGuy]

Have you tried to make a recordset of the table, then MoveFirst within the recordset with a criteria of "TypeField='123' " I don't have enough records in any of my files to test this speed, but here is what I did do in VBA.

QryTxt = "SELECT * FROM tblMaterialActivity WHERE MaterialID=8657 ORDER BY MaterialID, MaterialOrder;"
Set rs = CurrentDb.OpenRecordset(QryTxt, dbOpenDynaset)
rs.MoveFirst
Debug.Print rs!JobID, rs!MaterialID, rs!MaterialOrder, rs!MaterialType

Hope this helps

[AndersO]

Hi GolferGuy!

I've tested something similar, but it still takes the same time. It is the first-building-the-recordset that seems to take time. I thought the SQL-parser would be smart enough to optimize that away, when I clearly state I only want the first record. (If I add a "NumberField=456", the query takes no time, of course. But I want the record with the LOWEST number...)

Thanks for you help! If you (or anyone else) have more ideas, I'll be most grateful. I guess I'll have to do some sort of work-around otherwise.

A O

[TomT]

Have you tried using the Min function if you're looking for the lowest value?

I'm assuming your table is MS Access, and not SQL Server....