Application slow on network - please advice

[Bogdan]

Hi,

I have an application, run over network.
Tables contains between 2000 and 80000 reords (client table has about 2000
records, while invoice table has about 80000).
There are also other tables (also tens of thousands of records).

One processing require calculation of a total sold for clients.
For this I have to run several SQL statements (the database structure is a
little complicated, because it is inhedited from a old DOS application, and
I have to live with)

The problem is application is fast enough locally (it takes about 2-3
seconds to run all SQL's and then update clients cursor with their sold.
But on the network the application is quite slow (17-20 sec)

I set up all indexes I need (at least I hope).
If I run sys(3054, 1), sys(3054, 2) and test my queries they seems to be
optimized (if I make index on deleted they seems to be fully optmimized,
othwise partially), but I get this loading time.

Please send me any advice who could help me to speed up this.

Best regards,
Bogdan

[Cindy Winegarden]

Bogdan <ams at fx dot ro> wrote in message news:3ce02306@10.1.10.29...
> I have an application, run over network.
> Tables contains between 2000 and 80000 records (client table has about
> 2000 records, while invoice table has about 80000).
> There are also other tables (also tens of thousands of records).

As you know, these are relatively small tables for VFP.

> One processing require calculation of a total sold for clients.
> For this I have to run several SQL statements (the database structure
> is a little complicated, because it is inherited from a old DOS
> application, and I have to live with)
>
> The problem is application is fast enough locally (it takes about 2-3
> seconds to run all SQL's and then update clients cursor with their
> sold. But on the network the application is quite slow (17-20 sec)

What do you mean by "update clients cursor?" Is this a local result
cursor or are you competing with other users to lock records?

How is your network? Is it also slower than local when doing other
things?

> I set up all indexes I need (at least I hope).
> If I run sys(3054, 1), sys(3054, 2) and test my queries they seems to
> be optimized (if I make index on deleted they seems to be fully
> optimized, towhees partially), but I get this loading time.

With the index on Deleted() you may be "fully optimized" but actually be
slower. It's best when there are no indexes on fields which have only a
few discrete values such as sex, yes/no, etc.

--

Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org http://cindywinegarden.adsl.duke.edu
http://msdn.microsoft.com/vfoxpro http://foxcentral.net

[Bogdan]

Hello,

> > I have an application, run over network.
> > Tables contains between 2000 and 80000 records (client table has about
> > 2000 records, while invoice table has about 80000).
> > There are also other tables (also tens of thousands of records).
>
> As you know, these are relatively small tables for VFP.

Yes, I know, and this bothers me.

> > One processing require calculation of a total sold for clients.
> > For this I have to run several SQL statements (the database structure
> > is a little complicated, because it is inherited from a old DOS
> > application, and I have to live with)

This takes about 2 sec locally, and more than 17 over network.
It is a complex processing, involving 4 complex sql statements, with
multiple joins, one with 4 union sections, and finally updating a temporary
cursor, used as datasource for a grid (2000 records) in a scan / endscan
loop with result of those sql's.

> > The problem is application is fast enough locally (it takes about 2-3
> > seconds to run all SQL's and then update clients cursor with their
> > sold. But on the network the application is quite slow (17-20 sec)
>
> What do you mean by "update clients cursor?" Is this a local result
> cursor or are you competing with other users to lock records?

No multiuser acces here, see above for deails.

> How is your network? Is it also slower than local when doing other
> things?

It was tested on heterogenous network. with win98, win2k and nt, no
dedicated server, so several PC's was used as server for several tests.

> > I set up all indexes I need (at least I hope).
> > If I run sys(3054, 1), sys(3054, 2) and test my queries they seems to
> > be optimized (if I make index on deleted they seems to be fully
> > optimized, towhees partially), but I get this loading time.
>
> With the index on Deleted() you may be "fully optimized" but actually be
> slower. It's best when there are no indexes on fields which have only a
> few discrete values such as sex, yes/no, etc.

I tried with and wthout deleted(), and I didn't saw important changes. So
finally I added deleted() back.
Also I have some two indexes in such fields:
One field with 1 char size, about 6 distinct values (it is used in where
clause of several queries)
And a field char with 6 size, with 8 values (used in where too)
Should I remove those indexes?

And another question.
In client table, I have all name parts separately, but in the browe fird
users wants the name combined ina single column (to allow incremental
search).
So I made a view like this:

select clients.*, MakeClientName(first, last, middle) as FullName.

and use it instead of client table.
MakeClientName is a stored procedure who actually is a concatenation of name
parts.
Could this view to slow down things? I mean, if client table is opened,
opening such a view download all clients data again over network, o uses
opened client table?

Thank you.
Bogdan


> cindy.winegarden@mvps.org http://cindywinegarden.adsl.duke.edu
> http://msdn.microsoft.com/vfoxpro http://foxcentral.net
>
>
>
>

[G]

Sometimes getting more speed can be found in little things.
A while a go we had also a problem with the speed of a calculation. Instead
of SQl we used an scan endscan routine... it took more lines but it increased
speed with a factor 3 . previous 12 seconds ---> 4 seconds...

Mayb this approach can help you...

[Tamar E. Granor]

On Tue, 14 May 2002 09:53:12 +0300, "Bogdan" <ams at fx dot ro> wrote:

>And another question.
>In client table, I have all name parts separately, but in the browe fird
>users wants the name combined ina single column (to allow incremental
>search).
>So I made a view like this:
>
>select clients.*, MakeClientName(first, last, middle) as FullName.
>
>and use it instead of client table.
>MakeClientName is a stored procedure who actually is a concatenation of name
>parts.
>Could this view to slow down things? I mean, if client table is opened,
>opening such a view download all clients data again over network, o uses
>opened client table?
>

You might try replacing the function call with an inline expression.
If the result set for the query is small, you might also be better off
selecting the 3 fields separately, then doing a 2nd query on the
results to consolidate them.

Tamar

[Cindy Winegarden]

Bogdan <ams at fx dot ro> wrote in message news:3ce0b0da@10.1.10.29...

> Also I have some two indexes in such fields:
> One field with 1 char size, about 6 distinct values (it is used in
> where clause of several queries)
> And a field char with 6 size, with 8 values (used in where too)
> Should I remove those indexes?

That would be something to try.

> And another question.
> In client table, I have all name parts separately, but in the browe
> fird users wants the name combined ina single column (to allow
> incremental search).
> So I made a view like this:
>
> select clients.*, MakeClientName(first, last, middle) as FullName.
>
> and use it instead of client table.
> MakeClientName is a stored procedure who actually is a concatenation
> of name parts.
> Could this view to slow down things?

Try something like this to replace the stored proc. PADR to the largest
expected size.
VFP has to go through your stored proc twice, once to compute the
expected size and again to select the data.

SELECT PADR(;
ALLT(First) + [ ] + ;
ALLT(Middle) + ;
IIF([.] $ Middle, [], [.]) + ;
ALLT(Last), 40) AS FullName ;
....

--

Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org http://cindywinegarden.adsl.duke.edu
http://msdn.microsoft.com/vfoxpro http://foxcentral.net

[Bogdan]

Hi,

> > select clients.*, MakeClientName(first, last, middle) as FullName.
> >
> > and use it instead of client table.
> > MakeClientName is a stored procedure who actually is a concatenation
> > of name parts.
> > Could this view to slow down things?
>
> Try something like this to replace the stored proc. PADR to the largest
> expected size.
> VFP has to go through your stored proc twice, once to compute the
> expected size and again to select the data.
>
> SELECT PADR(;
> ALLT(First) + [ ] + ;
> ALLT(Middle) + ;
> IIF([.] $ Middle, [], [.]) + ;
> ALLT(Last), 40) AS FullName ;

Actually I used such an expression, not a function, but I was to lazy to
write it in the message.

Thank you for your answers anyway.

Regards,
Bogdan

> ....
>
> --
>
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy.winegarden@mvps.org http://cindywinegarden.adsl.duke.edu
> http://msdn.microsoft.com/vfoxpro http://foxcentral.net
>
>
>
>