>>I know that probably this question has been asked before but i haven't been able to get the answer i have been looking for.
>>
>>let me explain you guys in detail the scenario:
>>
>>1. I have a table PREmployees,
>>2. The employees for this customer are about 2,000 people
>>3. The application was requested to store an historic of every week, so it is 2,000 records per week, so more than 100K records a year
>>4. I am using a CursorAdapter parametrized for bringing only the records for an specific week
>>5. when the screen opens , the Cursorfill fires and brings 2,000 records for the specified week
>>6. The user can change the week as discretion so all years, all weeks must remain on the table
>>7. On the screen there is an option to display a search window
>>8. i am using odbc with a shard connection handler
>>
>>the problem:
>>when i run the screen on the server takes a couple of seconds, but when the users runs it on their desktop through the network, takes a little more, probably a minute or so
>>
>>what i am looking for:
>>1. an idea on how to reduce the time consuming for the user when opening the screen
>>2. understand if fetchasneeded might help with out affecting my search window cause in order to search uses the Cursoradapter resultset, all 2000 records
>>3. for me 500K records is not that much, but is taking to long to retrieve, i might be doing something wrong?
>>
>>what i am not looking for
>>1. ideas on how to split table. not interested in this
>>2. stop using CA, not an option, my whle system is bases on this
>>
>>tnx in advance
>
>Never expect the performance on the server (local access to data) to be the same as the user (remote access to data). The question has to be what to do to improve user performance as much as possible.
>
>1 - Show the user a grid of records. If there are 5 columns for that grid, only grab 5 columns x 2000 rows. When the user wants to access a particular record, grab all the fields for that record. If there are 50 columns on the table, doing 1 query = 50 * 2000 rows which is 100000 cells of data. If you only grab 5 columns x 2000 rows that is 10,000 cells. 1/10th the data will give you much more speed. You do not need ODBC for FoxPro to grab FoxPro data with cursoradapter.
>
>2 Ask the user for the employee id or name and only pull those records.
>
>3 Do you have the correct indexes? On the server foxpro can seem so fast that you may think you do not need indexes. You do need them. Show the SQL command you are running, and the indexes on the table please.


How large is a single record?
28 fields, including 1 PK, no FK

What's the table schema?
Schema dbo
How is it indexed?
3 Indexes, UID Clustered this is the primary key, Semana Non Clustered this is the filter paramter, EmployeID Non Cluster

What's the query?
a basic select statement with explicit fields (no *) and a where Clause ?Semana = ?ldSemana

What back end are you using?
MSSQL 2008

Network
I thought about this, buts doesn't seem to be the problem
backbone 1Gbit, Cisco Switches
Client Side
Win7 Professional, 2.8, I7, 8GB RAM

OS
Server 2008 Enterprise

Isn't that contradicting each other?
what i meant, the table in total is about 500,000 but I am only querying about 2,000, which represent the number of employees per week
so the CA is querying something like this
Select employeeid, name, lastname, seccionid from premployees where semana = ?ldSemana

my coding

I have a CA class
I create CA objects on the Init and add a property for every cursor



ldSemana = GetMonday(DATE())
Thisform.AddProperty("oCAEmployees",NEWOBJECT(Thisform.lsMainTable,"clases\lsPRData.vcx"))


That specific line of code takes :
in my development laptop 4 seconds
at the server 2 seconds
at client side 70 seconds

TIA