Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2008
    Posts
    7

    Question Unanswered: Improving App Performance over Multi-User Networked Environment

    We are currently exploring ways to improve the performance of our application. We utilize Paradox 10, a Paradox database, on Windows OS's, with approx. 10-15 concurrent users. Over the past few years we have experienced poor software performance which we've typically attributed to poor network connectivity from the individual user's pc to the file server database.

    Our application currently employs many TCursors to locate/edit (to manipulate data) and QBE queries (to retrieve and report data). These practices work just fine in a LAN but not so well in a WAN environment that we've branched out to.

    We're looking for a way to bridge the gap to provide ample improvement with minimal impact to our current architecture, databases, and source code. Can anyone provide some advice into which direction we should go?

    I appreciate your help in advance!

  2. #2
    Join Date
    Dec 2007
    Posts
    369
    These practices work just fine in a LAN but not so well in a WAN environment that we've branched out to.

    absolutely correct.. the only way to make a major difference in that context, is a complete rewrite to a client/server system.. desktop database systems (paradox, dbase, access, etc.) weren't designed for this.. too much of the data has to pass thru to the remotes..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  3. #3
    Join Date
    Jul 2008
    Posts
    7
    So, what do you suggest in the rewrite to client/server?

    Also, we're currently experiencing hung .lck files that require a reboot of the database server in order to delete and clear out. This is occurring from one of our client's WAN locations....do you have any suggestions on how to prevent this or worst case, deal with without requiring server reboot?

    One last question....If we're not client/server currently, how would you categorize/describe the architecture of Paradox applications?

  4. #4
    Join Date
    Dec 2007
    Posts
    369
    So, what do you suggest in the rewrite to client/server?

    what brand, what platform? (MS SQL, SQL Server, Oracle, etc.?).. it's not my place, at this point, to make a recommendation.. I don't know anything about your business, your network, your finances, etc..


    we're currently experiencing hung .lck files that require a reboot of the database server

    that's why you're at-risk using Paradox, or anything like it, on a WAN.. don't know exactly how you're configured, or how heavy the remote use is, but here are some of the questions that need to be asked:

    who or what is locking your files?
    is it specific users, specific processes, or both?
    are you experiencing table damage and/or table/index failures?
    are you having code problems? cancel/debug and/or gp/aborts?

    the problem with a remote system like this is, if the remote fails and leaves behind open files, the network holds open the files until the remote re-logs into the network or you reboot the server.. if an app failure caused the remote failure, it an ause the whole app universe to fail (all the users)..

    occasional remote access is one thing.. constant remote access is a different story, and much more dangerous.. shared data systems aren't the same as simple spreadsheets, Word docs, e-mail, etc..


    how would you categorize/describe the architecture of Paradox applications

    desktop database..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  5. #5
    Join Date
    Dec 2007
    Posts
    369
    it an ause

    that should have been "it can cause"..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  6. #6
    Join Date
    Jul 2008
    Posts
    7

    Thumbs up

    here are some of the questions that need to be asked:

    who or what is locking your files? pdoxusrs.lck files created from user's pc by software accessing networked server tables via tcursor locate/edits

    is it specific users, specific processes, or both? yes, 2 pcs at this problematic location with low connectivity (6mbps), and yes, specific process that accesses a handful of tables on the server, all other processes are local...and of course no problem there. These users do not cause lck files on every attempt to process the these server tables, but probably at least 50% of the time.

    are you experiencing table damage and/or table/index failures? yes, some of the tables have require rebuilds due to corruption, index problems

    are you having code problems? cancel/debug and/or gp/aborts? no, no errors presenting themselves, the users systems seem to operate normally other than slow, but they don't halt.

    Now that we know that the application needs a high bandwidth in order to perform properly, how would we go about determining what our minimum specification is? Is a minimum mbps connection appropriate? We dabbled a little bit with ping and tracert tests, but we not networking wizards...We just need a way to clearly express to our customers what our minimum specifications are regarding network connectivity.

    Also...can you help with giving some insight to the indepth workings of Paradox during query procedures. A typical QBE query in our app may look something like this:

    q1 = "query \n"

    q2 = tableName + "| TYPE | ID | TX# | VX# | \n"
    q3 = " | CheckPLUS " + getType + " | Check | Check | Check " + vxMatch + " | \n"
    q4 = "\n"
    q5 = tableName + "| DATE | TIME | PLU CODE | DESCRIPTION RECEIPT | \n"
    q6 = " | Check " + drange + " | Check | Check " + pluMatch + " | Check | \n"
    q7 = "\n"
    q8 = tableName + "| MAIN CATEGORY | VENDOR | QTY | GATE | DISCOUNT | TOTAL | COST | \n"
    q9 = " | Check " + mainMatch + " | Check " + vendCode1 + " | Check | Check | Check | Check | Check | \n"
    q10 = "\n"
    q11 = tableName + "| PROFIT | CUSTOMER | CLERK | \n"
    q12 = " | Check | Check | Check | \n"
    q13 = "\n"

    q14 = "endQuery"

    sQuery = q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 + q11 + q12 + q13 + q14

    q.readFromString(sQuery)

    if not q.executeQBE() then

    2 scenarios....which one describes how Paradox works?
    Scenario 1: Report program running on user machine executes queries to retrieve data from source database, returned to user PC via network connection before processing report
    All data retrieved in a single data set before data is sorted, selected and formatted for report output.

    Scenario 2: Report program running on user machine executes query to retrieve the first record matching selection criterion. Record is stored locally and additional requests for single records are executed until end-of-file or similar conclusion is reached. Local temp stored data is sorted and formatted for report display


    I really appreciate your help in gaining valuable insights into these puzzling aspects. Collectively we've been searching for answers on various web groups, knowledgebases, you name it. I just happened to stumble upon dbforums this week and finally answers are coming in. Thank you!

  7. #7
    Join Date
    Dec 2007
    Posts
    369
    so you think that the processes are running completely, but still leaving lock files behind?.. same instances with bad table/index problems, or do they seem to be same machines, but unrelated instances?

    typical QBE query in our app may look something like this:

    who came up with that?.. you don't need all that "q equals" nonsense.. just put the query text out there as-is.. nothing wrong with it the way it is, other than the fact that it's extra work..

    2 scenarios....which one describes how Paradox works?

    this one:

    All data retrieved in a single data set before data is sorted, selected and formatted for report output
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  8. #8
    Join Date
    Dec 2007
    Posts
    369
    the reason the query stuff is stupid:

    letter.a = "T"
    letter.b = "H"
    letter.c = "E"

    my.word = letter.a + letter.b + letter.c

    that's what you're doing with your query text..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  9. #9
    Join Date
    Jul 2008
    Posts
    7
    Well, thanks for your honesty!

    Here's the reason for the query formatting:

    The query is in a scanning loop against a list of various tables. Therefore, the tablename is variable, as also the query contains variables within the various fields. And then all is read into the query before execution:

    sQuery = q1 + q2 + q3 + q4 + q5 + q6 + q7 + q8 + q9 + q10 + q11 + q12 + q13 + q14

    q.readFromString(sQuery)

    If memory serves, the passing of variables thru a query could be handled easily with a ~, but not the tablename....thus the readFromString....

  10. #10
    Join Date
    Jul 2008
    Posts
    7
    so you think that the processes are running completely, but still leaving lock files behind?.. yes, appears that way

    same instances with bad table/index problems, or do they seem to be same machines, but unrelated instances? I'm not sure I understand your question...The table that appears to get the hung lock file most frequently is not indexed, and is a little of 1 MB in size. We think it's happening during a tcursor locate/edit process. It instance does not corrupt the table, just leaves pesky lock files that cause issues for other users, then require reboot to clear...

  11. #11
    Join Date
    Dec 2007
    Posts
    369
    thus the readFromString

    correct..

    The table that appears to get the hung lock file most frequently is not indexed, and is a little of 1 MB in size. We think it's happening during a tcursor locate/edit process

    so.. others use the table way, but only this user causes the problem? or this user is the most likely to cause the problem?

    if the lock file doesn't clear, it means that the OS hasn't released the table file.. or some other file used in the same operation.. if everyone is just doing something that places a "prevent exclusive" lock (view, simple edit, simple query, etc) then it really shouldn't matter.. but if somebody else tries to do something like a changeto or insert query, you're screwed..

    if you've done the repair process, and it still happens, try this.. export the data in the table to ascii, make a new table from scratch, import from the ascii.. might not help, but can't hurt..

    externally, it's not "minimum bandwidth", it's "uninterupted bandwidth".. a network "burp" can be a disaster for a remote..

    also, make sure oplocks and write caching are disabled, local share is true, etc..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  12. #12
    Join Date
    Jul 2008
    Posts
    7
    externally, it's not "minimum bandwidth", it's "uninterupted bandwidth".. a network "burp" can be a disaster for a remote..

    got it! That makes sense! Well, what would you say that the minimum bandwidth would be then, with no interuptions...?

    also, make sure oplocks and write caching are disabled, local share is true, etc..

    Ok, sorry for my naivity, but you lost me on the oplocks and write caching. I recognized the local share set to true from BDE System Init Settings....but not sure about the others

  13. #13
    Join Date
    Aug 2004
    Location
    UK
    Posts
    9
    We moved our Paradox 9 system from 30 users to about 60 users from 1 site to 7 using Citrix. Works well with the exception that Printing can be a bit of a bitch (Could be out version of Paradox along with our version of Citrix which is Metaframe XP. Might be different under CPS where I think they introduced the Universal Printer Driver)

  14. #14
    Join Date
    Jul 2008
    Posts
    7

    Cool .lck file problem resolved

    Just circling back on this thread, and remembering the nightmare we were experiencing with the hung .lck files.

    We did solve the problem, and you'll never guess what it was!

    McAfee Antivirus software was running on the database server which was causing opportunistic locks and not letting them go. Thus the reboot was necessary.

    Thanks to Steve's comment about oplocks, I mentioned this in a conference call, they immediately thought about McAfee.

    Apparently an upgrade of McAfee fixed the problem. When the change was made the problem disappeared instantly!

    Thought I'd let others know in case they run into this problem too.

  15. #15
    Join Date
    Dec 2007
    Posts
    369
    Apparently an upgrade of McAfee fixed the problem.

    that's very good to know.. AV software can also commonly slow down a system, when the default of "check all files" isn't changed to something more restrictive (and more useful).. thanks..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •