What's New with Data in Visual Fox. Pro 9? The Microsoft Visual Fox. Pro team has a great reputation for responding to community requests and the next version of is no exception. Microsoft Lifecycle Policy. The Microsoft Lifecycle Policy gives you consistent and predictable guidelines for the availability of support throughout the life of a.
Visual FoxPro originated as a member of the class of languages commonly referred to as 'xBase' languages, which have syntax based on the dBase programming. From the new and extended functionality in SQL to new data types and a binary index, this release demonstrates the power of a mature development platform for data- centric solutions. A single SQL SELECT statement can contain more tables, more JOINs, more sub- queries, more nested sub- queries, and more UNIONs than ever before. Changes to the data engine can be summarized in five major areas: SQL Enhancements: Removal of most hard- coded limits, enhanced sub- query and correlation support, support for more complex expressions, and enhanced UNION support. Performance: A new index type, enhanced performance of filtered indexes and improved SQL performance with TOP n, MIN()/MAX(), and LIKE. Commands and Functions: Greater ability to fine- tune how data is accessed and committed, functions to supplement SQL showplan, and easier immediate expression nesting with ICASE(). New Data Types: Support for Var. Char, Var. Binary, and BLOB data types, a new CAST() function, and enhancements to existing functions to control and convert data types. Remote Data: Enhanced control over transactions, better visibility regarding fetched records, rowsets returned from the provider, and Cursor. Adapter enhancements that bring behavior in line with remote views. Many of these changes improve the Visual Fox. Pro 9 client/server story by providing stronger interoperability with SQL Server. With support for new data types and removing many limits from the SQL language, it's now easier to develop a single code base that works with the Visual Fox. Pro 9 native data engine and SQL Server. Enough overview, let's dig in! SQL Enhancements. Certainly the best word to describe changes to the SQL sub- language is MORE! There are no longer hard- coded limits to the number of elements in a SQL statement. A single SQL SELECT statement can contain more tables, more JOINs, more sub- queries, more nested sub- queries and more UNIONs than in previous versions. There are also no hard- coded limits on the number of items in a SQL IN list. In versions prior to Visual Fox. Pro 9, SQL IN was mapped to the INLIST() function; that dependency has been removed. This change allows an increase in the number of arguments for IN and for better optimization. Visual Fox. Pro 9 stops evaluating expressions from the list as soon as the match is found. This is helpful if the IN condition is not Rushmore- optimized, as performance can be improved by placing the most- likely- to- match values at the beginning of the list. The total number of items is still indirectly limited by the SYS(3. IN. Issues such as available memory and expression complexity can still have an impact on whether or not a very long and complex statement can be run, but you'll have to work hard to find many real- world limitations. Enhanced Sub- Query Support. Sub- queries have always been powerful in the SQL language. They can be used as filters by placing them on the right side of a comparison in the WHERE clause. In Visual Fox. Pro 9, sub- queries can also be used as part of the SELECT list (called a projection) and in the FROM clause (often called a derived table). Many of these changes improve Fox. Pro's client/server capabilities by providing stronger interoperability with SQL Server. When used as a projection, if the sub- query doesn't return any records, a NULL value is returned. Correlation is supported with projection (more on this in a minute). A SQL statement that uses projection looks like this: SELECT . C. Customer. ID, . C. Company. Name, . SELECT YTD. Now that's flexibility! A derived table as sub- query allows you to treat the results of a sub- query as though it were its own table. Consider the following example: SELECT . C. customerid, . P. It's also important to note that the sub- query can be complex (in this case, joining to two other tables) and that the results from the derived table can be used as a condition of the WHERE clause and in the ORDER BY of the top- most SELECT. Unlike a projection, the derived sub- query can return more than one column and more than one record. It cannot be correlated. All sub- selects are executed before the top- most SELECT is evaluated. Sub- queries are also supported in the SET list of a SQL UPDATE statement. Only one sub- query is allowed in a SET clause and if there is a sub- query in the SET clause, a sub- query in the WHERE clause is not allowed. Better Correlation Support. The SQL UPDATE and SQL DELETE commands now support correlation. A correlated statement includes a FROM clause to relate the records being affected to another table. For example: DELETE products . WHERE mfg. product. ID = products. product. ID. AND mfg. discontinued = . Just be aware that when using a sub- query it's like doing an outer join. For every record that is not found in the sub- query, the value returned is NULL. This may not give the desired results. UPDATE products . SET unitprice = . SELECT ( msrp *. 9. FROM mfg . WHERE mfg. ID = products. product. ID). This UPDATE statement sets the unit price for a product at 9. Manufacturers table. The price for products not found in the Manufacturers table is set to NULL. Note that this statement operates on every record in the Products table; in the previous statement, only updated records that matched in the Manufacturers table were involved. View and Query Designers. Unfortunately, due to the complexity of the SQL statements you can write with these enhancements, the Query and View Designers do not support many of the sub- query changes to SQL. Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority. Also, with the hard- coded limits of SQL IN removed, the Designers no longer convert IN to INLIST(). The INLIST() function still has a limit of 2. Enhanced UNION Support. In addition to having no hard- coded limits for the number of UNIONs, you can now use a UNION inside the result set that is used by an INSERT INTO. You can now also ORDER BY < fieldname> when using UNION. The referenced field must be present in the SELECT list for the last SELECT in the UNION. Performance. Regardless of whether you are doing remote data access or relying on the powerful native data engine, performance has always been a priority for Visual Fox. Pro. Visual Fox. Pro 9 enhances the data engine even further. Binary Indexes. This new index type is a specialized index, designed for one thing. Other restrictions preclude the use of a FOR expression and ASCENDING, DESCENDING, UNIQUE, and CANDIDATE keywords. SET ORDER TO is not supported and the INDEX ON command sets the current order to 0. Also, you cannot use a Binary index with any Seek operation. The big advantage of a Binary index is its size. A Binary index for a table with 8,0. Mb versus 3. 1. 5. Mb). Smaller means faster I/O and faster APPEND and REPLACE, all with the same Rushmore optimization as a non- binary index on the same expression. There is a trade- off to consider. Rushmore optimization is faster if the amount of records returned is more than 3% of the total records (about 9. However, Rushmore optimization is slower if the amount of records returned is less than 3% (about two times slower when 0 records match the condition). It is likely that the 3% threshold will become smaller as the number of records in the table increases. Turning your DELETED indexes into Binary indexes is an easy way to start taking immediate advantage of Visual Fox. Pro 9 performance enhancements. Just be sure that all clients accessing your data are upgraded, as this new index cannot be read by prior versions. Rushmore Optimizations. There are a few new Rushmore optimizations that do not require changes to data and index structures. Top N . This operation returns only the top number or percent of records in a result set as controlled in the ORDER BY clause. This change in Visual Fox. Pro 9 eliminates records from the sort process that don't belong in TOP N as early as possible, reducing comparison operations and decreasing file I/O in low memory situations. This also has the side- effect of only returning exactly N . In previous versions, if there was a tie for nth place, all records that matched the tie were included, resulting in getting back more than N records. If this change in behavior is not desired, consider bracketing the SQL call with SET ENGINEBEHAVIOR 8. The only limitation to this optimization is that TOP N PERCENT cannot be used unless the entire result set can be read into memory at once. When appropriate, Visual Fox. Pro 9 uses filtered indexes to optimize MIN() and MAX() aggregate functions in FOR DELETED() and FOR NOT DELETED() only. This improves MIN()/MAX() performance, if such an index exists. The Like . For example: INDEX ON DELETED() TAG DELETED. This index is used to optimize both NOT DELETED() and DELETED() conditions without the presence of a tag created by INDEX ON NOT DELETED(). Just like the MIN()/MAX() optimization, Visual Fox. Pro 9 uses a FOR NOT DELETED() filter on an index to optimize a DELETED() or NOT DELETED() query. Whenever it is possible to determine that a condition should filter on DELETED() or NOT DELETED(), a filtered index FOR DELETED() or FOR NOT DELETED() is used in the event that no non- filtered indexes exist. Take this upgrade opportunity to review the indexes you currently deploy. If you are unable to use a binary index, you may find that with these optimizations, you can at least drop a few existing indexes. If only indexes filtered FOR NOT DELETED() were used for Rushmore optimization and SET DELETED is ON, additional NOT DELETED() optimization is unnecessary. Commands and Functions. A few commands and functions have been extended to provide greater control over how and when Visual Fox. Pro reads and writes data to disk. Fine- Tune How Data is Accessed and Committed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
September 2017
Categories |