>Hi Everybody,
>
>I am developing an application which will be run by 100+ users. From a single master table located in a server, the user application needs to get a single record but this record comes from a filtered subset of the entire master table. Each user/workstation needs to get its record in less than a second. The master table is just plain ordered according to its sequential arrangement - meaning by record number (RECNO()).
>
>The SQL syntax I tried is something like this:
>
>"SELECT TOP 1 RECNO() AS RECORDNO, * FROM Mastertable WHERE Taken=.F. ORDER BY RECORDNO"
>
>I tried using local views, remote views but it takes MINUTES(!) to execute (to return the first 'available' record that is). Am I missing out on anything?
>
>My advanced gratitudes to anyone who will look into this!
Local view - fox tables but what about RV ? Sounds like they're fox tables too.
In fox SQL top 1 doesn't necessarily mean you'd get only 1 record. If say you've 100 recs table and 98 had taken=.f. then top 1 query would return all those 98 records (different from SQL server - or ANSI SQL92).
Logical fields aren't a good candidate for optimization. Either .t. or .f. would be encountered in more than 5%.
If all this is about fox tables than a nonSQL approach would be much better :
locate for !Taken
It'd stop as soon as one found.
If you really should do that SQL way do not use recno() which would force 'nofilter'. And again be notified 'top 1' has no effect in your SQL.
Cetin