PDA

View Full Version : Active link problem


ABC123

Anis
03-25-2008, 01:38 PM
I created a very simple form with 2 fields.
Account number & total quantity ( total qty for account group) which means it has to add all records that are being added thru the day ).
so the form shows:

account total all day quantity grouped for that account

If I try to open that form at 8:00 AM I leave the office at 5:00 PM and the form is still not open................

The active link is to a MYSQL table starting with 20,000 records and growing to 300,000 during the day.

It couldn't be a design issue on my part, it very simple form.
How could V9 be used in this environment ?

Selwyn Rabins
03-25-2008, 01:51 PM
I created a very simple form with 2 fields.
Account number & total quantity ( total qty for account group) which means it has to add all records that are being added thru the day ).
so the form shows:

account total all day quantity grouped for that account

If I try to open that form at 8:00 AM I leave the office at 5:00 PM and the form is still not open................

The active link is to a MYSQL table starting with 20,000 records and growing to 300,000 during the day.

It couldn't be a design issue on my part, it very simple form.
How could V9 be used in this environment ?

active -link tables execute a sql statement to populate the table. that's it. there's no magic here.

by definition, an active-link table can't be slow. what can be slow is the SQL that the server has to execute.

what is the SQL that your active link is trying to execute?

it might be that you have defined an inappropriate, or meaningless SQL statement.

in general, the speed of an active link table is directly related to the speed of the server you are connected to and the speed of the connection between your machine and the server.

we have test databases that have millions of records and active-link tables based on these tables open in seconds.

Anis
03-25-2008, 02:54 PM
Selwyn;

I haven't defined any SQL statements, I created the active link and built the connection string thru an ODBC. I want to see all records.

the form has the account number read from the first record, and then has a calculated grouped total qty for all records with the same account, just like a report would.
Now is it taking this time to do the calcualtion ? is it because the database is adding records (many records) very fast, is not returning the screen with a cutoff time?
I don't understand what is A5 doing at that time!

Bill Griffin
03-25-2008, 02:57 PM
Selwyn,

I think the speed perception comes when one treats an active link table as we would a dbf table. performing a function like reccount(sql_table) vs reccount(dbf_table) is excruciatingly slow on the sql table even when both tables reside on the same remote server. I don;t know how to demonstrate this to you other than tell you to put a dbf file on the same server where your sql server is. create a blank form and add a calculated field that does reccount and watch the difference. I don't know if this is normal, but I believe this is what anis is referring to.

If you open a browse with the sql and dbf file and hit Ctrl End you will see a huge speed difference as well.

Selwyn Rabins
03-25-2008, 03:52 PM
Selwyn;

I haven't defined any SQL statements, I created the active link and built the connection string thru an ODBC. I want to see all records.

the form has the account number read from the first record, and then has a calculated grouped total qty for all records with the same account, just like a report would.
Now is it taking this time to do the calcualtion ? is it because the database is adding records (many records) very fast, is not returning the screen with a cutoff time?
I don't understand what is A5 doing at that time!

all active link tables have an sql query.
if you built the active link using a genie, then right click on the table and select active link/show definition. then scroll to the 'select statement'.

as bill griffin points out - if your form has a calc field on it that does a summary calculation then you will get horrible performance. which is compeletely to be expected.

the correct way to do this is to define a sql statement that does the summarization on the server.

the basic rule in any client server application (not just a5) is: never do anything on the client that could be done on the server.

you don't want to be summarizing across records on the client when the same thing can be done much more efficiently on the server.

Anis
03-25-2008, 04:00 PM
Yes apparently; that is the problem.