The smaller table will surely take less time to search. There are less records to search through and there is more chance that the index will fit entirely into memory, speeding the search.

Now, would I split up the table? That depends. If there is a greater chance that you find the partno in the first table, then it will be probably faster to split the table. If there is an egal chance of finding the partno in either one of the table or a greater chance of finding it in the second table, then splitting up the table will reduce performance, because you will need to open and look through 2 tables instead of one. You have to take into account also the maintainability of these tables. In wich table do you add new records? How do you maintain the uniqueness of the primary key? etc.

---

>I have a general question for the crowd:
>
>Let's say I have 10 million records in an inventory table with an indexed field on part number. We have a related table of 10,000 different parts.
>
>If I SQL-Select things from the inventory with:
>
>SELECT * FROM inventory WHERE partno = 54321 INTO CURSOR cTemp
>
>the select will take a small amount of time.
>
>Now, let's say we archive off 5 million records (and pack!) the inventory table. Now, only 5,000 different parts are in the inventory table, because we archived off records for the other 5,000.
>
>My question: "Would you expect SQL-Selects by part number from the 5 million record table to be faster or slower, and why?"
>
>The reason I'm asking is that we have a client who wants to split their large tables in two for speed reasons.