Win CE SDF Help

[jwebster]

I have a PDA application created with VB 2008. I used SDF files for my database. Within the database i have a table that has the following fields : Lot, Part, Expiration Date, Status. The user completes information on a form ( Lot, Part) and then i have to look up that combination in the table. However, the table has between 75,000 and 100,000 records and takes approx. 6 to 12 seconds to find the correct record, and display the expiration date. This is a huge problem. Is there any way what so ever to make this look up any faster? I need it to be around 1 second or less. Any help guys?

[Curtis Rutland]

What database are you using? I'm not familiar with SDF files.

[jwebster]

.sdf is the database. It is a SQL Server Compact Edition database.

[Plater]

Split up the tables
lets say you have 100,000 entries in a single table
If you divided them evenly between say 100 tables, then each table would only have 1,000 entries.
So you have one switch in code that decides which of the 100 tables to look in, then only 1000 entries to sift through.

This sort of technique is used frequently (not always in databases) with text lookups.
You have an A table, a B, a C and etc. ~26 tables for english letters.

[jwebster]

That's a good idea, i'll try it out and see if i can come up with some way to distinguish between which table to use.