big backend tables and cursordapters 1

[mejiaks]

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

 

[Olaf Doschke]

That's giving much info, yet some important things are missing.

Speed is determined by
a) time the server needs for the query, which usually is not much time for 2K records. Also taking into account you experience fast results working at the server.
b) time needed to fetch the data. 2K records is a low number, but if you have 5 Varchar(MAX) fields in each record and that's filled with megabytes of text or documents, that takes it's time.

Questions:
How large is a single record?
What's the table schema?
How is it indexed?
What's the query?

Bye, Olaf.

 

[Mike Lewis]

One other question ot add to Olaf's list:

- What back end are you using?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

Also note that the network itself is often the choke point. What kind of network are you running? What speed is the connection? How many users? What server operating system? What speed are the routers/hubs? (Note that "autoswitch" almost always means it will auto-switch to the lowest common denominator.) Is load an issue?

If you get fast results at the server but slow results at the workstation, it pays to investigate the network that sits between them.

 

[Olaf Doschke]

And one more:

>...it is 2,000 records per week, ....
>4. I am using a CursorAdapter parametrized for bringing only the records for an specific week

>3. for me 500K records is not that much, but is taking to long to retrieve, i might be doing something wrong?

Isn't that contradicting each other?

What are you doing now, retrieving all 500k records or 2k? Eg if you SELECT * FROM (CA.alias) WHERE week=X, and the CA query simply is SELECT * FROM TABLE, you do retrieve 2k from all 500K records, which are fetched first. That wouldn't be a parameterized CA.

Bye, Olaf.

 

[Olaf Doschke]

Another scenario you might be doing is having the CA with a query SELECT * FROM TABLE WHERE Week=?parameter in a forms DE, but you only set the parameter in form.init, where DE already has run. Then you set the CA param and requery. Depending on the initial value of the parameter you fetch lot's of data in the first DE run, then requery only 2k records.

Other problems might be not using a connection shared.

We need to know more about your code and environment to put the finger on the issue.

Bye, Olaf.

 

[craigber]

Instead of bringing back all the records, don't get any records, then display a query form. Based on the date range, etc, only retrieve the records requested. This is how client/server queries are done and is a better practice.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[mejiaks]

Hi
sorry for the late reply

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

 

[Mike Lewis]

I'm with Craig on this one. Are you sure you need all 2,000 records every time? If you only fetch the records that you actually need to process, that could substantially reduce network traffc, and might well solve your problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mejiaks]

Mike

This is one of my points mentioned originally

if i pull only one record at a time, this will affect the behavior of my Search windows which uses a grid to display all records
also will affect my next, prev, first, last buttons behavior

i understand this is the best approach, i am just looking for an option to improve the performance with out affecting those behavior

 

[Mike Lewis]

if i pull only one record at a time, this will affect the behavior of my Search windows which uses a grid to display all records
also will affect my next, prev, first, last buttons behavior

That could well be the root of the problem. In a well-designed client/server system, you do not use VCR-style navigation (Next, Previous, etc), and you aim not to display all records at the same time. The idea is to focus on one record at a time.

If you run a search that could potentially return many records, you should at least limit the number of fields that are returned. Ideally, return the primary key, plus just enough of the data to permit the user to identify the record (typically, a person's name and company, or a product name). Display that information in your grid. Then, when the user identfies the record of interest, retrieve just that one record, using the primary key in your WHERE clause.

I realise this could be a substantial change to your user interface. But that's the choice you have to make when you move to a client-server system.

Another thing you can do is to limit the search results to the first, say, 50 records (ideally, the closest matches). Display those in the grid while you fetch more records in the background. It's quite easy to do that in VFP (for details, check the Help re the FetchSize and FetchAsNeeded properties).

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>What's the table schema?
Schema dbo

I was rather referring to the table structure, fields and types, lengths. Not the DB schema dbo.

Doesn't matter much....

CA query: Select employeeid, name, lastname, seccionid from premployees where semana = ?ldSemana
Code:

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

Local Variables are not seen by the CA, are they?

How is your CA configured, to create it's cursor automatically?
I'd prefer to call CursorFill expllicitly, when ready, eg:

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

Change the query to: Select employeeid, name, lastname, seccionid from premployees where semana = ?This.dSemana

You have to have access to query parameters from the perspective of the CA object, don't you? You rather depend on ldSemana being a private variable, which it may be, as you don't show any LOCAL definition, only your naming convention would be wrong, then, as private vars should be prefixed by p.

How about making this more self contained? Eg add the properties for parameters to the CA class itself, and set the property to a default in the property window eg by setting it to [=GetMonday(DATE() ]. (The square brackets just hint on the property window right side white area for entering values, so you enter the = in there as first char, to denote the following is not a char value, but an expression to be evaluated, you don't enter any of the square brackets).

Bye, Olaf.

 

[mejiaks]

thanks to all of you guys

after all your replies I have to conclude my design is wrong and I need to change it

 

[mejiaks]

Olaf

i tried you approach "Change the query to: Select employeeid, name, lastname, seccionid from premployees where semana = ?This.dSemana" and like for a magic enchant it worked!!!!

I am very happy, now on the client side takes milliseconds

thanks to all of you

 

[Olaf Doschke]

That Change wouldn't explain the time lag. If the CA had no access to the variable ldSemana, the query wouldn't just take longer, it wouldn't work. Or the user would be prompted for a value, which of course could take half a minute....

Bye, Olaf.