I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDesktop Data...Paradox & Ob...Paradox Tables   Print This     
  From the November 2015 Issue of Prestwood eMag
 
BDE Best Practices: Safely Use Paradox Tables
 
Posted 11 years ago on 6/3/2008 and updated 3/24/2009
Take Away:

Use a file server (not a workstation) to store your data, disable Oplocks on the filer server, and configure the BDE (local share to true and optimize if desired). Finally, if workstations are crashing, fix em so they don't. You want clean running computers that don't crash.

KB101149



[6/3/08: From an old article we use with our clients. I have additional notes and will update as I have time.-MP] 

[3/24/09: Added a "Use Most Current BDE" under the BDE standards.-MP]

Should you stay with Paradox tables?

Use a file server (not a workstation), disable Oplocks on the filer server, and optimize the BDE.
--Mike Prestwood

A few recommendations from Prestwood IT.

First off, should you use Paradox tables for your data? Our position on this is that you can use Paradox tables safely and effectively but if your data is valuable to you, you may want to move it to a better data storage solution or at least make sure you have implemented robust backup and restore procedures and implemented the best practices in this article. Paradox is a file based system and can be very fast. File based databases are still valuable and actively developed and marketed.

Recommendation 1: Business Database Applications
A business database application is a multi-user application with 2 or more users (A.K.A. an enterprise application). If your data is important to you, we recommend migrating your data to an SQL database or at least start planning on it. Our SQL database of choice currently is MS SQL Server 2005. We've also used many other databases with great success including SQL Server 2000, Interbase, and Oracle.

Specific Recommendations from Prestwood Software:

  • Paradox for Windows to Paradox Tables -- You have two choices for when you migrate. Keep Paradox as a front-end and migrate the data to an SQL database but keep the existing Paradox application. We keep Paradox application changes to a minimum. This is a very affordable approach. Although more expensive, the other recommendation from us is to migrate your data to an SQL database and convert your Paradox application to either a Delphi Win32 application or a DotNet application. Although we love Delphi, our slight preference is to migrate to a DotNet application using VS.Net (either C# or VB.Net).
     
  • Delphi to Paradox Tables -- Our recommendation is to leverage your existing Delphi application and swap out the Paradox tables for an SQL database if you are having problems with the Paradox tables or want a more robust data storage solution.
     
  • Other Systems Using Paradox Tables -- I would have to hear a bit more, but generally I recommend migrating your data from Paradox to an SQL database if you are having problems with the Paradox tables or want a more robust data storage solution.

Recommendation 2: Commercial Applications
A commercial application is a single user or multi-user application that requires easy installation and very little to no maintenance. Intuit would sell very few copies of Quicken if each user had to install SQL Server, Interbase, etc. If your commercial application currently uses Paradox, our position is that you should switch out Paradox for an embedded database such as Advantage, DBISAM, SQL Server Everywhere, etc.

Paradox Tables Best Practices

A best practice is a practice which is most appropriate under the circumstances. Because it is better to be safe than sorry, we've expanded the term best practice to include practices "believed" to be most appropriate under the circumstances.

The following terms are used throughout to help distinguish:

  • Standard - a practice reasonably proven to help stability
  • Suggestion - a practice generally accepted as helping stability but not proven
  • Optional Suggestion - a practice generally accepted as helping improve speed and either has no impact on stability or may have a positive impact on stability.

The following best practices are primarily for multi-user Paradox applications. If you're using a single user Paradox application, scan through these suggestions for applicable best practices. We've only included practices where the benefit out ways any disadvantage and we skipped over setup procedures such as setting the NetDir (although we do offer a few NetDir suggestions). Also included are settings that are known to increase data speed with no-known negative stability effects and may contribute to increasing stability.

File Server Best Practices

...if workstations are crashing, fix em so they don't. You want clean running computers...
--Mike Prestwood

1. Use a File Server Standard
Put your data tables on a real file server. If your application is a multi-user application and your data is worth at least $500, buy a file server and put your data on it. Do not use an existing workstation.

2. Disable OpLocks on File Server Suggestion
Opportunistic locks (OpLocks) are a feature of the LAN Manager networking protocol implemented in the 32-Bit versions of Windows. An OpLock is a guarantee made by the server for a shared logical volume to its clients. These guarantees inform the client that the file server will not allow another client to change the content of the file, or if some change is imminent, the client will be notified before the change is allowed to proceed. OpLocks are an additional protocol on top of the file-system locking protocol and was implemented for performance reasons.

Problem with OpLocks include:

  • block clients from performing operations immediately
  • were designed for file-sharing with medium concurrency
  • introduced an additional level of overhead
  • known problems with file-based databases such as MS Access.
  • some believe the problems with MS Access also apply to other file-based databases including Paradox tables

Our current recommendation is that if you're having problems now with Paradox tables, disable OpLocks on the file server. In addition, if you are not having problems now but you wish to take a conservative approach, disable OpLocks.

To disable OpLocks on your file server: Configuring opportunistic locking in Windows

Vista/Server 2008 Note: OpLocks changed with Vista and Server 2008. Microsoft introduced SMB2. I do not know if OpLocks still causes problems under a Vista to Server 2008 scenario or a 2000/XP to Server 2008 scenario. I do know if you disable OpLocks on Vista, the offline files feature fails. However, since we are recommending turning off OpLocks on the file server only (prevents all OpLocks), this is less of an issue. However, this begs the question, if I switch out my file server for a Server 2008 file server, are OpLocks still a problem? If anyone has information on this, let me know and I'll update this article.

Workstation Best Practices

3. Proper Shut-Down Standard
Some users get used to the idea of just turning off a computer at the end of the day without properly shutting down. Although it is fairly common knowledge that you must shut down your computer properly make sure all users understand that when working with live data on a network, it's critical.

4. No Freeze-Ups Standard
If you have a workstation that freezes up (crashes) every now and then, don't ignore it, resolve the issue. Your goal is to have stable well-running workstations that never crash.

Borland Database Engine (BDE) Best Practices

If your application uses the BDE to access Paradox tables, use the following best practices in order to maximize reliability and to increase speed where reliability does not suffer. Whether or not the BDE files are stored on the network or on each workstation, the BDE runs on each workstation so these best practices are also workstation best practices.

5. Use The Most Current BDE Standard
We recommend using the latest BDE available, currrently BDE 5.202.

6. NetDir In-A-Folder Suggestion
Some BDE users have recommended that you do not use a mapped root drive such as "N:\" for Paradox's NetDir. Most consider the default of C:\ a bad idea too that has lasted now for 15+ years and now in Vista is not allowed. So, when moving your NetDir out onto a common location on your network, do not put it in the root of a mapped or shared drive, use a folder.

7. NetDir Same Path Standard
When I worked at Borland, we recommended that all workstations get to the NetDir the same. If one system has the database mapped to drive "N", then every computer with access to that database must also have it mapped to drive "N". For example, although the following network shares resolve to the same location, we recommend all workstations use the same connect path.

  •  \\Taz\Network\Data\PdxNetDir\
  •  \\Network\Data\PdxNetDir\

My belief is that locking problems are sometimes caused by mis-configuring one of the workstations.

Note: Do not ignore the "use a file server" standard already discussed, but if you do choose to go down that dangerous road, you must be sure that the computer that actually hosts the database does not use a local drive letter for data access nor for the NetDir. If the database is accessed via N:\Data\, on one machine, it must use the same path on all the machines including the computer sharing the data. This note also applies if you are using a file server and occassionally access the Paradox tables using a BDE application installed on the file server.

8. Strict Integrity Standard
Set STRICTINTEGRTY to True (the default). When set to True, only applications that support referential integrity can use the tables preventing others, such as Paradox 4 for DOS, from accessing the tables. When set to False, Paradox 4.0 applications (and similar) can use the tables. If False, you risk the integrity of your data.

BDE Paradox Strict Integrity

9. Local Share Suggestion
Set Local Share to true. In the BDE Administrator, select the Configuration tab. The Local Share setting is located at Configuration | System | Init.

Note: Our Workbench for Paradox add-on utility requires Local Share set to true (Workbench is a Paradox for Windows developer tool).

10. MINBUFSIZE and MAXBUFSIZE Optional Suggestion
The defaults of 128KB and 2048KB for BDE's data cache are low considering the amount of memory available in today's computers. We recommend you raise MINBUFSIZE from it's default of 128 to at least 1024 and raise MAXBUFSIZE from it's default of 2048 to at least 16384 (16MB).

11. MAXFILEHANDLES Optional Suggestion
We recommend you raise MAXFILEHANDLES from the default of 48 to 255. I used to recommend raising it to 100 but enough CodeGear and dBASE users have suggested to me for various reasons that 255 is a better setting.

12. MEMSIZE Optional Suggestion
We suggest you raise the maximum amount of memory that the BDE will use from it's default of 16 megabytes to at least 32MB or larger on computers with more than 1GB of memory (the max you can set this value to is 205MB). Setting it to 25% of available memory is a common recommendation I hear from our developers here at Prestwood Software.

13. SHAREDMEMSIZE Optional Suggestion
We suggest raising it from the default of 2048KB to 16384KB (16MB). If you want to be more precise, there are formulas available on the Internet you can use. My belief is raising it to 16384 is fine for most modern users of the BDE.

Screen shot showing the above INIT specific settings:

BDE INIT Settings

Paradox ODBC Best Practices

The following applies if your application uses an ODBC driver to access Paradox tables (meaning you do NOT use the BDE). I have some more notes on this and once I dig em up, I'll add to this list here.

14. Use Paradox 9 Developer's Edition ODBC Driver Suggestion
If the ODBC driver you are using is working for you, stick with it. If you're looking for a recommendation as to which Paradox ODBC driver to use, we recommend the ODBC driver that shipped with the developer's edition of Paradox 9. This is the only ODBC driver we know of that works with Paradox 7 tables (I'm sure others exist) and over time has proven to be the best (in our opinion).

If you don't have Paradox 9 Developer's Edition, you can get the ODBC driver that ships with it from DataDirect Technologies.

Paradox for Windows Best Practices

If you built your application using Paradox for Windows, we offer the following best practices.

15. Working and Private Directory Suggestion
Some Paradox developers are recommending you set your working and private directories at least two folders down from the root.

More Info

KB Post:  BDE Broken? Recent Microsoft Betas may be the culprit
Download:  Borland Database Engine 5.202
KB Post:  Paradox Table Specifications
Article:  Repair Paradox Tables
Article:  Uninstalling the BDE

Linked Message Board Threads

 Paradox .lck in Paradox Tables MB Topic (3 replies)
 _qsq1.mb in Delphi News / Other MB Topic (6 replies)
 .net and .lck files in ObjectPAL MB Topic (2 replies)
 File size limit on .MB in Paradox Forms MB Topic (2 replies)
 *.DB and *.MB out of sync in Paradox Tables MB Topic (0 replies)
 Paradox.lck file (Path not found) in ObjectPAL MB Topic (6 replies)
 *.lck files in novell network in ObjectPAL MB Topic (1 replies)

Comments

1 Comments.
Share a thought or comment...
Comment 1 of 3

The following listing of resources on OpLocks and notes sent to me from Steve Caple. It's part of an email he sent to one of our clients recently.


From Steve...

A few notes on [OpLocks], before I go off on vacation (June 14 - July 11): 

So here is yet more on OpLocks, including links to "sources".

I have heard that MS recommends it be turned off if using their desktop database Access, and have found what seems to be the source of that. Also, almost everyone else seems to agree it is wise to disable them.

Title: "[Samba] Frozen applications on Citrix" - http://lists.samba.org/archive/samba/2002-March/039371.html

Sonmewhat older, but Citrix-centric. - http://support.citrix.com/forums/thread.jspa?messageID=205045 

Several more references to problems with Citrix, OpLocks, and file server databases. -

https://support.citrix.com/forums/thread.jspa?messageID=67581&fromSearchPage=true&

"Opportunistic Locking (oplocks) on the Network File Server - http://support.microsoft.com/kb/303519
Microsoft has discovered an issue where opportunistic locking can increase the risk of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This issue also applies to clients that are running Microsoft Windows NT 4.0, Microsoft Windows 2000, or Microsoft Windows XP, and that connect to a file server that supports opportunistic locking."

Client Programs Stop Responding - http://support.microsoft.com/?kbid=823272
Interesting title: "Client Programs Stop Responding While They Wait for File Locking to Occur"

OpLocks and Data Corruption - http://www.dbforums.com/archive/index.php/t-425184.html
This is a Microsoft pulic access support thread on the topic.

Corrupt Microsoft Access MDB Causes - http://www.granite.ab.ca/access/corruption/causesoplocks.htm
Quite a few references to various MS and other documents on the subject. 

File and Record Lockinghttp://us4.samba.org/samba/docs/man/Samba-HOWTO-Collection/locking.html
"Windows oplocks is a lightweight performance-enhancing feature. It is not a robust and reliable protocol. Every implementation of oplocks should be evaluated as a trade-off between perceived performance and reliability. Reliability decreases as each successive rule above is not enforced.

Consider a share with oplocks enabled, over a wide-area network, to a client on a South Pacific atoll, on a high-availability server, serving a mission-critical multiuser corporate database during a tropical storm. This configuration will likely encounter problems with oplocks." 

Superbase Networking - Oplocks Explained - http://www.superbase.com/services_tech_support_oplocks.htm
An excerpt from a relatively thorough discussion:

" The problem with oplocks is that they:

* are designed for file-sharing with medium concurrency

* introduce a additional level of overhead

* block clients from performing their operations immediately.

In practice the oplocks introduced by Microsoft are good enough when it comes to sharing files like Word documents or Excel spreadsheets in a networking environment. But they fail when it comes to heavy concurrency in enviroments with file-based databases such as Xbase++, Visual FoxPro and even MS-Access or VB applications with the Jet-Engine. There are other strategies available, such as the ones implemented with the Andrew's file-system which deal with the problem of distributed caches in a network, but for whatever reason Microsoft has decided to go their own route."

Posted 11 years ago

Comment 2 of 3

Today I added a "Use most current BDE version" to the BDE standards.

Posted 10 years ago

Comment 3 of 3

I need use paradox.db version 7 in excel 2010. code page 1250 ansi czech ch

I use bde 5.2 (Langdrive pdx ANSI Czech 'CH') and odbc.

DB is open to excel bad odbc translate language.

How I tern off the translate?

Posted 7 years ago
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1244A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #101149 Counter
144530
Since 6/3/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech