Application slow on network - please advice


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Application slow on network - please advice

  1. #1
    Bogdan Guest

    Application slow on network - please advice

    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





  2. #2
    Cindy Winegarden Guest

    Re: Application slow on network - please advice

    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





  3. #3
    Bogdan Guest

    Re: Application slow on network - please advice

    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
    >
    >
    >
    >




  4. #4
    G Guest

    Re: Application slow on network - please advice


    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...

  5. #5
    Tamar E. Granor Guest

    Re: Application slow on network - please advice

    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

  6. #6
    Cindy Winegarden Guest

    Re: Application slow on network - please advice

    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





  7. #7
    Bogdan Guest

    Re: Application slow on network - please advice

    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
    >
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center