Is it normal that it takes 5 seconds for a form to open?

[Paul T. Rong]

I use Access 2k under Windows XPPRO.The form is based on a query which is
based on another query.What should I do to improve the performance?

Thanks,

Paul

[Keith Wilby]

"Paul T. Rong" <et***@hotmail.com> wrote:

What should I do to improve the performance?

Look here:

http://members.iinet.net.au/~allenbr...html#usability

Regards,
Keith.

[(Pete Cresswell)]

RE/

I use Access 2k under Windows XPPRO.The form is based on a query which is
based on another query.What should I do to improve the performance?

This is probably grasping at straws, but does the form have any graphics in it?

I inherited an app once that had a form with a cutsie picture of
something-or-other (strictly decoration) that took forever to load.
Remove the pic and it loaded normally - i.e. almost instantly.
--
PeteCresswell

[Salad]

Paul T. Rong wrote:

I use Access 2k under Windows XPPRO.The form is based on a query which is
based on another query.What should I do to improve the performance?

Thanks,

Paul

Besides the great tips the others provided...
Does your query have a sub-select in it? Such as
Select Id From Table1 Where ID In (Select ID From Table2)
Sub-selects are great performance inhibitors.

[Paul T. Rong]

exactly. My query has sub-select in it. Maybe i should give it up, to find
other solution than sub-select query.

thanks.

Paul
"Salad" <oi*@vinegar.com>
??????:9Z*****************@newsread1.news.pas.eart hlink.net...

Paul T. Rong wrote:

I use Access 2k under Windows XPPRO.The form is based on a query which is based on another query.What should I do to improve the performance?

Thanks,

Paul

Besides the great tips the others provided...
Does your query have a sub-select in it? Such as
Select Id From Table1 Where ID In (Select ID From Table2)
Sub-selects are great performance inhibitors.

[Salad]

Paul T. Rong wrote:

exactly. My query has sub-select in it. Maybe i should give it up, to find
other solution than sub-select query.
Yes. You should give it up and find another solution.

thanks.

Paul

[(Pete Cresswell)]

RE/

Does your query have a sub-select in it?

Another one: do the backend tables behind the form have SubDataSet=[Auto]?
If so, and if subdatasets are not being used, changing to SubDataSet=[None] can
speed things up - more/less depending on the number of tables/relationships.
--
PeteCresswell

[Tony Toews]

"Paul T. Rong" <et***@hotmail.com> wrote:

exactly. My query has sub-select in it. Maybe i should give it up, to find
other solution than sub-select query.

How long does your query take to run when you run it all by itself? Without the
form.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"Paul T. Rong" <et***@hotmail.com> wrote:

I use Access 2k under Windows XPPRO.The form is based on a query which is
based on another query.What should I do to improve the performance?

Is this the only form that takes 5 seconds and other forms take a lot less time?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Paul T. Rong]

The problematic form is based on a query that has a sub-select. The query
takes 5 seconds to run. So this is the key, and is why it is slow. I already
gave it up. I am looking other solutions.

Many thanks.

Paul

"Tony Toews" <tt****@telusplanet.net>
??????:6h********************************@4ax.com. ..

"Paul T. Rong" <et***@hotmail.com> wrote:

I use Access 2k under Windows XPPRO.The form is based on a query which is
based on another query.What should I do to improve the performance?
Is this the only form that takes 5 seconds and other forms take a lot less

time?
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Pieter Linden]

"Paul T. Rong" <et***@hotmail.com> wrote in message news:<Sr******************@news.chello.at>...

The problematic form is based on a query that has a sub-select. The query
takes 5 seconds to run. So this is the key, and is why it is slow. I already
gave it up. I am looking other solutions.

Many thanks.

Paul

1. what happens if you just run the query - how long does that take?
2. what if you change the subselect to a left join and filter that
way? May not be feasible, but without seeing the SQL, it's hard to
tell.
3. Can you set filters so you don't have to load all the data all at
once?

[Paul T. Rong]

Hi Pieter,

these are the codes, they can be useful, and I did get some inspiration from
them. But it is slow. I have already given it up.

Sql codes:

query1

SELECT DCount("*","table1","[id]<=" & [id]) AS ID, table1.Bank,
Table1.Amount
FROM table1;

query2

SELECT Max(IIf([ID] Mod 5=1,[Bank])) AS Bank1, Sum(IIf([ID] Mod
5=1,[Amount])) AS Amount1, Max(IIf([ID] Mod 5=2,[Bank])) AS Bank2,
Sum(IIf([ID] Mod 5=2,[Amount])) AS Amount2, Max(IIf([ID] Mod 5=3,[Bank])) AS
Bank3, Sum(IIf([ID] Mod 5=3,[Amount])) AS Amount3, Max(IIf([ID] Mod
5=4,[Bank])) AS Bank4, Sum(IIf([ID] Mod 5=4,[Amount])) AS Amount4,
Max(IIf([ID] Mod 5=0,[Bank])) AS Bank5, Sum(IIf([ID] Mod 5=0,[Amount])) AS
Amount5
FROM query1
GROUP BY ([ID]-1)\5;

it takes almost 5 seconds on my machine. could be less on newer pc.
greetings and thanks,

Paul

"Pieter Linden" <pi********@hotmail.com>
??????:bf**************************@posting.google .com...

"Paul T. Rong" <et***@hotmail.com> wrote in message

news:<Sr******************@news.chello.at>...

The problematic form is based on a query that has a sub-select. The query takes 5 seconds to run. So this is the key, and is why it is slow. I already gave it up. I am looking other solutions.

Many thanks.

Paul

1. what happens if you just run the query - how long does that take?
2. what if you change the subselect to a left join and filter that
way? May not be feasible, but without seeing the SQL, it's hard to
tell.
3. Can you set filters so you don't have to load all the data all at
once?

[Salad]

Paul T. Rong wrote:

Hi Pieter,

these are the codes, they can be useful, and I did get some inspiration from
them. But it is slow. I have already given it up.

Sql codes:

query1

SELECT DCount("*","table1","[id]<=" & [id]) AS ID, table1.Bank,
Table1.Amount
FROM table1;
This may be the slowest part of your queries.

query2

SELECT Max(IIf([ID] Mod 5=1,[Bank])) AS Bank1, Sum(IIf([ID] Mod
5=1,[Amount])) AS Amount1, Max(IIf([ID] Mod 5=2,[Bank])) AS Bank2,
Sum(IIf([ID] Mod 5=2,[Amount])) AS Amount2, Max(IIf([ID] Mod 5=3,[Bank])) AS
Bank3, Sum(IIf([ID] Mod 5=3,[Amount])) AS Amount3, Max(IIf([ID] Mod
5=4,[Bank])) AS Bank4, Sum(IIf([ID] Mod 5=4,[Amount])) AS Amount4,
Max(IIf([ID] Mod 5=0,[Bank])) AS Bank5, Sum(IIf([ID] Mod 5=0,[Amount])) AS
Amount5
FROM query1
GROUP BY ([ID]-1)\5;

it takes almost 5 seconds on my machine. could be less on newer pc.

What would happend if you created the following queries and dropped query2?

Query1A. Create a "Mod" key from the result of query1
Select RankID : [Id] Mod 5, ID, Bank FROM Query1

Query1B. Get the dollar amount sum.
Select RankID, Sum(Amount) As SumAmount From Query1A Group By RankID

Query1C. Get the greatest bank id
Select RankID, Max(ID) As MaxID From Query1A Group By RankID

Now, create another query and drop in Query1B and Query1C and Table1.
Create relationship lines from 1b and 1c on the RankID. Create Link
between MaxID and ID in Table1. Drag the bank and amount into the
query, if you like RankID. Use this as the recordsource.

I would think this would be faster.

You should have a max of 5 records....1 record for each mod type.

[Tony Toews]

"Paul T. Rong" <et***@hotmail.com> wrote:

SELECT DCount("*","table1","[id]<=" & [id]) AS ID, table1.Bank,

DCount is almost certainly your problem. If you can somehow get rid of it, and Salud
has a posting with more details, it should spead up dramatically.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm