>Hi,
>
>I am working on converting an existing application from native database to SQL Server database using CursorAdapters. Since records returned from a cursoradapter are not indexed, I added a couple of INDEX ON statement in AfterCursorfill() so that I can do a SEEK in the cursor. However, I soon realized that this is causing a bad performance issue when the table associated with the cursoradapter is large.
>
>Does anyone have a better idea on how to do a record search in a cursoradapter that matches the speed of an index seek?
>
>Thanks in advance for your help!
>
>Wen

Wen

I agree with Sergey. You are using VFP's file based methods to work with SQLServer remote data. Your first priority should be to bring down the least number of records and fields by creating better filters in the select.

In fact if you do this, you would not even have to use SEEK since locate would be fast enough.

The only time you'd bring down many records would be for a lookup table like POSTCODES and such.

For a single client you'd craft your SQL statement so you get one Customer record, and invoices for that customer only by filtering your selects. Don't try to maintain the same selects that were used in the app because those are for VFP File based tables.

As an example I worked on a FPW2.6 app to convert it to SQLserver and VFP CA's. The old app had a list box where every customer's (70000) name was listed. To open a record you'd dbl click the customer name to open the records.

When I redesigned it, the first thing I did was remove the list box. The user gets a search form where the name or part of it could be entered and once confirmed (there are many people with same names) then only the record for that member was brought down.

You need to rethink your design now.