PowerBASIC Gazette CyberEdition #23 =================================== November Thirtieth 2001 From: Bob Zale, President PowerBASIC, Inc. As you probably know, we recently released version 1.1 of our PowerTREE BTree Manager. With better performance. With better reliability. And perhaps even more important, at a better cost! With PowerTREE 1.1, you'll receive the DOS version, the Win16 version, and the Win32 version, all three of them for just $99. Less than half the previous cost. So, with all this "hoopla", a lot of folks have started asking some serious questions about PowerTREE. Just what is it? Just what makes it so special? Just how do I use it? So "Stay Tuned" right here and "Read All About It!"... Pricing and ordering information can be found at the very end of this Gazette. Questions? Just email info@powerbasic.com We'll do our very best to help. Regards, Bob Zale, President PowerBASIC, Inc. ============================================================== ============================================================== PowerTREE -- "Under the Hood!" By Lance Edmonds, PowerBASIC, Inc. This article introduces the concepts of indexing, how to add PowerTREE to your code, and provides a brief look "under the hood" of PowerTREE. What is PowerTREE? ------------------ PowerTREE is a BTREE Manager that handles Index files using very high performance Index search algorithms. PowerTREE is designed to add indexing facilities to your own database files. Because PowerTREE works independently of your main database files, you can use it to add indexing facilities to any type of database. And with the release of PowerTREE 1.1, performance has been improved in many areas too. If you are already using PowerTREE 1.0, you'll love the reliability of PowerTREE 1.1... so upgrade today! What is an Index? ----------------- An Index file is simply a small database file containing a "key" (such as a Customer Name or Stock number) and a related "Reference Number" (such as a random-access record number) of the record stored in a "main" database file. The record number is the "glue" that associates an Index Key with a specific database record. Internally, PowerTREE stores these Index Keys in sorted order using a form of binary tree. As a result, PowerTREE allows searches for keys to be performed at much higher speeds than reading through an entire main database file, just to locate a single stock or customer record. Interesting concept - please tell me more! ------------------------------------------ Well, the easiest way to explain it is to look at a real world example. Let's say we have random-access database file that contains stock records, and the records in this file have been saved in the order that they were created in (ie, there is no particular sorted order to the stock records). In order to locate a specific stock record, we would have to read the entire data file, examining each record in turn. With a flat, random access file, this could involve reading a huge amount of data just to find one record. Moreover, the whole process would be repeated for the next record search. On a network, this will result in a very large amount of data being moved about, and it would be very slow. How does PowerTREE solve this? ------------------------------ We use PowerTREE to maintain an Index file for the stock numbers in the main database file. When we initially create the index file, we pass the stock number and the record number to PowerTREE, and PowerTREE handles all the details of sorting and storing all of the index keys. Later, when we need to locate a specific stock record, one function call to PowerTREE will tell us which record number holds the stock record. But PowerTREE would need to search through the data too? -------------------------------------------------------- Not at all! Internally, PowerTREE uses a dual binary tree algorithm to be able to locate a specific index key without reading every single key in the index file. In simple terms, PowerTREE can examine and traverse its internal binary tree to locate a specific key with just a few comparisons. The result is a virtually instantaneous search for the record number in the Index file, leaving us with the minor task of reading the specific stock record from the main database file. Once any key has been returned by PowerTREE, returning subsequent or prior keys in the sorted index is even faster again. We'll discuss this in a little more detail later in this article. How do I create an index? ------------------------- Simple. We simply define our key length, choose between single-user or multi user mode, then make one simple function call to PowerTREE. To add keys to PowerTREE, we simply call the "AddKey" function. Sounds easy, but how do we search for keys? ------------------------------------------- PowerTREE makes this very easy too - You can search for "exact matches" or "partial matches" too. For example, if you initially defined the keys to be 10 characters wide, your search key would be 10 characters long. By specifying a key that is shorter than the index key width, you can perform "partial matching". For example, "SMITH" would find "SMITH", "SMITHY", "SMITHSON", "SMITHE", etc. If you need compound index keys, for example, a name and a code, you format the index that way yourself before passing it to PowerTREE. PowerTREE offers a selection of search functions too, including FindFirst, FindNext, FindPrevious, FindLastBefore, FindNextAfter, etc. What about sorted reports? -------------------------- Another common problem with the type of database file mentioned above involves producing a sorted stock report. With the flat-file techniques discussed above, the entire database file would need to be read, and each stock number and record number would be stored in arrays. Next, the arrays would be sorted, then finally, each record would be read in sorted order, as the report is generated. PowerTREE to the rescue again! ------------------------------ PowerTREE solves this problem by allowing you to retrieve keys in sorted order. Once any key has been returned by PowerTREE, obtaining the subsequent or prior keys in the sorted index is even faster again. This means that producing sorted reports can be vastly simplified using Indexing with PowerTREE. Further, this approach will use a LOT less memory and much lower disk I/O is required too. Show me how it's done --------------------- Ok, you asked for it! The following shows the general strategy used to read a complete index file in sorted order, starting at the very first key. DIM db AS INDEXBLOCK db.MultiUser = 1 ' Multiuser mode enabled OpenIndex "datafile.idx", db ' Open the index file FindFirst "", db ' 1st key in the index file WHILE ISFALSE db.ErrType ' No errors? CALL GetMainDataRecord(db.RecordNum) ' Read/process actual data FindNext "", db ' Get the next key WEND CloseIndex db For a descending report, we could simply replace FindFirst with FindLast, and FindNext with FindPrevious. Is that it??? ------------- Absolutely. PowerTREE handles all the low level index file locking automatically, and PowerTREE for Windows is even thread-safe. Index keys can be up to 255 bytes long, and PowerTREE can handle index files of up to 2 Gigabytes (DOS) or 2 billion keys (Windows). PowerTREE for Windows also offers a couple of Soundex functions too. Can I delete keys? ------------------ Naturally! When keys are deleted, PowerTREE simply erases the key and updates the links from the previous key to the subsequent key - totally automatic, with high efficiency. PowerTREE 1.1 now maintains more compact index files too, with significant improvements in the "DeleteKey" function that contributes to ensuring top performance in the PowerTREE key search functions. Any other tricks up its sleeve? ------------------------------- You bet! The "record number" associated with each key does not have to relate to an actual record number in a database file. A PowerTREE index file could be used as a simple database in its own right, say, associating test scores with teams or individual players. In another example, it could be an invoice number. In one real-world application we heard about, a company required a "memo" database to be added to an established application. Rather than redesign the main database file, the solution was to store memo data in a separate file, and use PowerTREE to cross reference the memo number to the invoice number in the main data files. That is, the key contained the memo number, and the record number contained an invoice number. When an invoice is viewed on the screen, PowerTREE is used to quickly check for the presence of memos. If one or more memos exist, they can be retrieved from millions of other memos in a fraction of a second. The most interesting aspect of this example is that there is no direct relationship between record numbers inside the main data file and record numbers of the "matching" memo data held in another file. PowerTREE provides a high-performance method of joining or associating the data in one file to the data in another file. Using PowerTREE, you don't have to use a flat file database - you can create your own proprietary relative database file system using similar techniques. Summary ------- We've taken a quick trip through the world of Indexing, and by now you are probably already plotting new and cunning ways to use Indexing to boost performance and add flexibility into your own applications. PowerTREE offers fantastic performance at an affordable price. Can your applications afford to be without it? PS: If you have a PowerTREE success story, we'd love to hear about it! ============================================================== PowerTree BTree Manager 1.1 (Now $99) is a brand new release of this time-tested product. If you like "Faster/Smaller", then PowerTree is just what the doctor ordered. PowerTree offers a concise, proprietary B+Tree algorithm for indexed data management. You design data files any way you want. PowerTree uses them at lightning speed. You can access your list in zip code sequence, alphabetically, or use virtually any other criteria. You'll find a record for "Clint Eastwood", out of thousands, in a heartbeat. PowerTree is friendly. Just 14 functions to learn, not hundreds, and with auto-locking, multi-user/multi-threading is a breeze. PowerTree was priced at $129/Win plus $89/Dos, but now it's just $99 total for all versions combined -- Win32, Win16, Dos -- and there's never a royalty for distribution with your code. Buy it once, use and distribute it forever. To order, call us today at (800) 780-7707 or (941) 408-8700, fax an order to (941) 408-8820. Place an electronic order on our web site at www.powerbasic.com, or even mail it to us. But no matter the method, do it today and do it with confidence. PowerBASIC products, other than downloads, are offered with a money-back guarantee for a full 30 days from the transaction date. =================================================================== Order online at https://www.powerbasic.com/shop/ or just send an email with all pertinent information to sales@powerbasic.com We'll take it from there! ------------------------------------------------------------------- Most PowerBASIC products (those without printed books) can now be delivered by electronic mail. No wait for a package to arrive... No high shipping costs... For just $6 per order, no matter how many products, we'll deliver directly to your computer. If you're outside the U.S., savings might be greater. You won't pay taxes or duties to a freight company or postal service, because they aren't involved in the delivery. Check your tax code to be sure, but some countries charge no tax at all on transactions of this type. It could just be your lucky day! ==================================================================== Is your PowerBASIC Gazette Electronic Edition subscription coming to you at home or work? If you don't want to miss a single issue, why not subscribe from both email addresses? Send your subscription request to email@powerbasic.com and please include your name and all email addresses you'd like to add as well as your Zip or Postal Code. If you know someone else who would enjoy this newsletter please forward a copy to them so they can subscribe. ==================================================================== All contents Copyright (c) 2001 PowerBASIC, Inc. All Rights Reserved. PowerBASIC is a registered trademark of PowerBASIC, Inc. PB/CC, PB/DLL, and PowerTREE are trademarks of PowerBASIC, Inc. All other brand names are trademarks or registered trademarks of their owners. ==================================================================== PowerBASIC Gazette - Electronic Edition Volume 1 - Issue 23 PowerBASIC, Inc. (800) 780-7707 Sales 1978 Tamiami Trail S. #200 (941) 408-8700 Voice Venice, FL 34293-5006 (941) 408-8820 Fax Visit us on the World Wide Web at www.powerbasic.com Email Sales: sales@powerbasic.com This newsletter is only sent to email addresses in our subscription list. If you have received this newsletter by mistake or no longer wish to receive it, please send a simple unsubscribe request to support@powerbasic.com with your name and zip/postal code. This newsletter is best viewed with a fixed-width font. ====================================================================