1 / 17

The Best Practices for Performance Improvement: Btrieve vs. SQL Case Study

Explore ways to enhance performance by comparing Btrieve and SQL, examining hardware, operating system, and application software for efficient client-server operations. Learn crucial strategies for faster reporting, transaction processing, and data purging in Elliott applications. Uncover the significance of network speed, server efficiency, and data retrieval methods in boosting overall performance.

zeno
Download Presentation

The Best Practices for Performance Improvement: Btrieve vs. SQL Case Study

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Best Practice for Performance Edward M. Kwang President

  2. Ways to Improve Performance • Client Server • Hardware • Operating System • Elliott Application Software

  3. Btrieve vs. SQL • Btrieve • Pro: Transaction • Con: Report • SQL • Pro: Report • Con: Transaction

  4. How Btrieve Work • It’s Record Manager • Retrieve One Record At A Time • Fast With Transaction Operation (Record by Record) Since There’s Very Little Overhead with Btrieve Engine

  5. How SQL Work • It’s Relational Database Engine • Client Issue SQL Statement to Server • Select Cus_No, Cus_Name, Cus_St From ARCUSFIL Where Cus_St = ‘CA’ • Server Compile The Statement And Decide The Best Way to Retrieve the Data • The Data Is Retrieved And Send Back to Client

  6. Performance Improvement • The amount of improvement by using SQL for report process range from a few times to more than 10 times: • It’s usually determined by three factors: • Network speed: • Less improvement with faster network • I.e. Big gain if you use 10-BaseT and less gain if you use Giga-Bit Ethernet • Server speed: • More improvement with faster server • CPU, Memory and Hard Disk (RAID) • The nature of the report

  7. SQL Is Faster With Report • In the Previous Example, SQL will • scan through the table of ARCUSFIL and pick out those customers that CUS_ST = ‘CA’. This is done on the server • return three columns back to client which reduce network traffic. • Vs. Btrieve will • Scan through the table of ARCUSFIL on the client side • Get entire record of customer, even though it only need three columns

  8. Btrieve Is Faster With Trx • For Record by Record Type of Operation, Btrieve is Faster Because It Does Not Have the Added Layer to Slow It Down. • Example of Transaction Processing: • Order Entry • Cash Receipt • New A/P Trx Entry • Inventory Trx Processing

  9. Elliott And Crystal Report • Elliott Work At Btrieve Level (Transactional) • The Future Direction of Elliott Report is Crystal • If We Use Crystal Report at The SQL level, then we have • “The Best of Both Worlds” • Pervasive’s Slogan

  10. Client Server • Crystal Report Writer • Use ODBC instead of Database Files • It’s SQL vs. Btrieve • Make no difference if • DB is small • key is readily available • All Data Need to Return to Client for Processing • Elliott • Running Elliott on Workstation • Running Elliott on Server • Defer Process on Server • Faster Report Processing • Less Chance of Data Corruption

  11. Hardware • RAID-5 • At Least Three Hard Drives • Form Capacity of Two Hard Drives • Three disk heads to retrieve data is faster than one disk head. • CPU • CPU Speed • Multiple CPU • Memory • 1 giga or higher is recommended • Network Speed, 10 vs 100 vs 1000 • Switch vs. Hub

  12. Operating System • Use Same Hardware Configuration • Windows NT vs. Netware 3.12 • Windows NT is about 50% faster • Windows 2000 vs. Windows NT • Windows 2000 is about 50% faster • PSQL Support • PSQL has better support on Windows NT/2000

  13. Elliott Application Software • Purge Your Data • Purge COP Posted Orders • Purge I/M Inventory Trx Audit Trails • Purge Distribution Files • Purge AR and AP Open Item Files • Do Not Purge These Files (Sales Analysis) • COP History Trx File (CPHSTTRX) • COP Invoice History Files

  14. Performance Demo • Run Sales Analysis by State Report • Run on Workstation • Run on Windows 2000 Server • Do the Same for AR Aging Report • Why The Performance Gain Between These Two Reports Are Different?

  15. Case Study • Lynn Robert International Recently Upgrade from Netware 3.12 to Win2000. They are disappointed that there’s not much performance improvement. • Network: 10-BaseT • Server: High End Windows 2000 Server • Aging Report: 5 Minutes • POST COP Invoices: 1 Hour 30 Minutes • Solution: run reports and posting on server • Aging Report: 10 Seconds • Post COP Invoices: 4 Minutes

  16. Analysis • Why Lynn Robert performance gain is higher than Netcellent? • Network Speed • 10 Base-T vs. 100 Base-T • Switch vs. Hub • Sever Speed • Not RAID-5 Configuration, CPU & Memory

  17. Formulating A Strategy • Use Windows 2000 Server • Run Defer Processing on Server • Faster • Reliable & Less Chance of Data Corruption • Use ODBC with Crystal • Performance with Client Server • Ease of Use with Views • Security • Purge Your Elliott Data

More Related