Stories


Performance is king

The first story we want to share is a project we worked on for another developer. The vertical market application was originally deployed using Visual FoxPro tables with great success. The company had a large and very satisfied customer base and the application had a reputation of having the best billing engine in the industry. The application started to suffer performance issues with the larger installations because of index caching and very wide tables (many of the tables had over 100 columns ). The second problem was a buzzword compliance problem. Despite the company ‚ s reputation with the product, some of their clients and maybe more importantly, potential clients started pushing for SQL Server on the backend. IT managers often read trade magazines and form directives to move their data to a SQL backend.

The company ‚ s programming resources were spread thin keeping up with government regulations and standard customer enhancement requests so they called us to perform the migration from using Visual FoxPro data to SQL Server and the Microsoft SQL Server Desktop Engine (MSDE). Fortunately, the application was originally architected to be migrated to a SQL backend. We performed the migration of 500 local views to remote views and stored procedures. We made numerous changes to each form and report to point to the new remote view database container (DBC). We also made changes to handle some of the SQL incompatibilities between Visual FoxPro and SQL Server and Xbase coding differences like removing _TALLY checks throughout the code. Querying SQL Server through remote views does not update the _TALLY system memory variable as it does with a local view.

During the testing phase, our client wanted to get a feel for the potential performance improvements by implementing an alpha version on the real live network. His client was also interested in seeing if the performance was really as good as we told them it was during our in- house testing. To benchmark the performance we set up four computers (different processors and memory configurations) with the production application and tested adding and saving records in several forms, running some batch processes, and executing several reports. Some of the record updates in the old system were taking up to 17 minutes. Processing one batch process and many of the reports easily took hours. The slowest computer was an old clunker with 64MB RAM and a 266 MHz processor. The high-end machine was a 1 GHz with 256MB RAM. We were quite concerned with the low-end machine because our office benchmarks were done with a 750 MHZ processor and 256MB RAM.

We installed the new client/server application and began the testing. Even on the lowest machine the performance was instantaneous. This is not an exaggeration. The users would perform the operation typically taking seventeen minutes and stare at the screen. We had to tell them the data was saved because it was sub-second. They did not believe us at first. Our customer was even shocked at how fast it was. Frankly, we were even a little surprised about the performance on the busy network. The batch process and reports finished in minutes, not hours.

So what was the difference? SQL Server processing is performed on the server. Indexes and data are not continuously cached to the local workstation. This improves network performance. This particular site had a powerful server and an optimized SQL Server installation with weak client machines. This was our customer ‚ s largest site and they still have outstanding performance. The end users wanted us to leave the partially ready software. They were ready to work with buggy , incomplete software than the slow feature rich application and were quite angry when they found out we uninstalled it before we left.

Indexes still matter

A different client who called us had a SQL Server-based application with a VFP front end. The forms were sluggish and updates were taking longer than the FoxPro DOS application it replaced. The load was not very heavy, maybe 10 users, and the amount of data was not even stressing the Visual FoxPro tables it replaced . We were brought in to examine the application architecture, code, and the database.

The base problem with performance was simple: the original developer did not have a single index on any of the tables. We added a primary index (basic database 101 fundamental) to the main table and improved the lookup of one record out of 200,000 from 20 seconds to instantaneous. Updates to the same record went from more than 10 seconds to instantaneous. There were other bugs with the application, but with only minor changes to the database, we were able to prove our value to the customer. Later we deployed a one form prototype showing a much better approach to solve their problem and later secured a complete rewrite of the FoxPro DOS application.

Security fundamentals from school of black and blue

A big difference between Visual FoxPro tables and a SQL backend is the built-in security you have in a SQL backend. It is not unusual to find a dedicated database administrator working on large database server installations. They dictate the security of the server and the security to the data your application manages . If they choose, they can tighten down the security to the column level, which can break the application if it is not designed to handle this.

The most common issue we experienced is database administrators do not want users to access the data from tools like Excel or Crystal Reports unless authorized so they tighten down access so users cannot even run SQL Selects on specific tables. If the user does not have access to the tables, they do not have access to the tables when they run your application.

We ran into this problem late in the beta of one of our deployments. All of a sudden, the database administrator changed security from Windows Authentication to SQL Server Authentication and tightened down the access to our tables. Our application broke because users did not have authority to access the data and they were presented with a SQL Server login each time they ran the application (in addition to the application login).

To get around this situation we deployed an application role in the database. The application role has its own set of security settings, separate from the users. We needed security to one table to open up for all users so we could establish a connection to the database. Once the connection is established, we associate the connection with the application role so our application has the access rights to function and the administrators can lock users out as they see fit. We address some of the issues with security in the section ‚“Database Administrators and security ‚½ later in this chapter.




Deploying Visual FoxPro Solutions
Deploying Visual FoxPro Solutions
ISBN: 1930919328
EAN: 2147483647
Year: 2004
Pages: 232

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net