Thursday, November 2, 2017

Using RushMode Query optimization to speed data access

First of, I did not make a typo when I said RushMode and not Rushmore; most of VFP users know the data access technique used by  the Fox Software when it was first introduce in FoxPro 2.0 and called it Rushmore technology, but frankly, I don't have any knowledge of how FoxPro Rushmore technology works "internally" inside VFP, but Managed Dbf RushMode is on the same boat as with Rushmore technology to speed things up in data access and has something to do in Query optimization.

Managed Dbf RushMode can optimize LINQ by using a pre condition RushWhile, RushWhere and RushWhileWhere overload parameters on LINQRush when querying, to help optimize query performance and data access by utilizing available table's index file.

Whether an index file associated with table is present or not, by using LINQRush, reading to disk will assure that it will meet the 512 or 4K sector alignment before reading the disk and whether the host disk has a 512 fixed bytes in each sector or for the latest standard  4096 bytes per sector "4K sector" it ensure sector bytes alignment; internally it has a file block disk caching for reading and writing to file more efficiency to the hard disk and record rows caching is  done in the background before rows yielding take place.

Now, let's see the code in action with a task to find all sales invoice of a particular customer with sales invoice amounting to $1,000.00 or more, it will be using Customer and Sales Master tables.


This is the slowest implementation a typical LINQ commands; querying with unspecified columns and not using RushWhere, RushWhile or RushWhileWhere  LINQRush overloads.



This is slow but not the slowest implementation, again a typical LINQ commands querying but with specified columns and still not using RushWhere and RushWhile techniques, specifying columns will save some memory and may speed up the process.



This is a fast implementation but not the fastest it utilize the use RushWhere as a pre condition before yielding record rows to LINQ.



This is the fastest implementation; first of, it search the particular customer on customer table and if  exist or found it captured the record row no. and do the same thing on sales master file.

On customer table LINQRush use RushWhile and set the while condition, index tag name and specify to start at specific record position, even with millions of records this will only read a single record on disk when querying take place, since the record pointer is already positioned and then immediately terminate the query on customer when the next record does not meet the while condition.

On sales table when Joining, LINQRush use RushWhileWhere overload and set the while and where condition, index tag name as the current index order to be use and the specified to starting at specific position, this will read the record while the while-condition is true and filter the yielding row only if where-condition is true.

This will read all of the particular customer record on sales table but starting at specified record position with respect to specified index order and yield only if invoice amount is greater $1,000.00.

Assuming the sales table has millions of records and the particular customer has only 100 records in the sales table this will only read 100 rows not millions and say 50% of it's records has an invoice amount reaches $1,000.00  it will only yield 50 rows. in short this  will read 100 rows only out of millions and yields 50 rows only.


Houston, we've had a problem here!

We're running out of memory using LINQ on gathering large amount of records, assuming a particular customer has thousands of records that meets the query criteria; with high volume of records to collect we might hit the OutOfMemoryException, when working on large records it might be a good idea to save it to a file rather than to memory. Managed Dbf can still use the xBase style of collating a record without consuming too much memory by using tables navigation technique to scan records in table.


Tuesday, October 24, 2017

Mimicking numeric TextBox of Visual Foxpro in WFP

Because Visual FoxPro is a RAD tools it's very easy to format a numeric on a TextBox  or even on a prompt screen as shown below, on a prompt screen it will only take a single line of code using PICT clause and on VFP Form using TextBox a simple Format and InputMask is all needed.

I'm trying to mimic the VFP numeric formatting on a TextBox using WFP, I achieved it (almost) It's a lot of work and the code is massive. try searching "WPF TextBox numeric formatting" over the internet to know what I'm talking about 😄

On the other hand in Visual Studio using WFP it's needs a massive line of codes to achieve this simple task, if any one has made a simple solution that can achieved the one shown in the video link below, hats off to you and I'm all ears to hear about it.

Wednesday, October 11, 2017

LINQ using two tables on Managed Dbf

A demonstration of querying two Managed Dbf tables using LINQ to find all sales record of a particular customer and display it's Sales Invoice No. and  Amount.

Managed Dbf can do it in the good old fashion but effective way, an xBase style of gathering information without using a querying language.

Managed Dbf Methods and Properties are almost identical to Visual FoxPro Commands.

Thursday, October 5, 2017

LINQ saved my day!

LINQ saved my day by writing my own OLEDB and OBDC driver for Managed Dbf, I'm in the process of learning, researching and studying how to build my own OLEDB like VFPOLEDB to support querying language until I read  Why LINQ beats SQL .

Now, I don't have to go through the lengthy process of creating my own Data Provider or to load any OBDC Driver or using any ADO/NET data connector for Managed Dbf  just to have a querying language to gather information, LINQ now can be use to query data in Managed Dbf  but it's still in early stage. I Quoted from LINQPad creator "LINQ is in most cases a significantly more productive querying language than SQL."

I wanted Managed Dbf to be free of any third party library or driver with only minimum set of .Net Assemblies, by using LINQ which is a major part .NET Framework since .NET 3.5 uses an SQL-like syntax to make a query expressions.

Managed Dbf using LINQ in action  :

XBase / Clipper / Visual FoxPro Equivalent Commands  :

Friday, June 16, 2017

Lambda Expression for Deletion

Lambda expression, it's an anonymous function it can use to create delegates or expression tree, by using lambda conditions, we can write local functions that can be passed as parameters. Let's see it in action for deleting a records using Lambda expression for Where and While condition in Managed Dbf and it's equivalent xBase/Clipper/Vfp code.

Delete all customers from USA country and state of New York or California.


Solution 1 :  ( Slowest )

This is the slowest solution, iterating to all records and check if we meet the condition criteria before
marking the record for deletion.

Equivalent xBase/Clipper/VFP commands :


Solution 2 :  ( Slow )

This is slow but not the slowest solution, any method using *All in Managed Dbf are using disk caching for reading all records and this solution is only using single line of code and lambda expression condition for deleting a record.

Equivalent xBase/Clipper/Vfp commands :


Solution 3 :  ( Fast )

This is a fast solution because it uses index file to searching country and continue reading WHILE the country still match the search and test for state conditions before marking the record for deletion.

Equivalent xBase/Clipper/Vfp commands :


Solution 4 :  ( Fastest )

This is a fastest solution because it uses index file for searching country+state and continue reading WHILE the search condition is true before marking the record for deletion.

Equivalent xBase/Clipper/Vfp commands :

Saturday, April 29, 2017

What's wrong extending the life of Dbf file format : - D

I already know what's your thinking... You may say maybe I'm just reinventing the wheel. Why not design my own programming languages and write my own compilers for them? Or even better create my own operating system from scratch? 😄 because there are fricking hundreds if not thousands of available database projects out there in the wild which are ready to use and battle tested.

Whenever you can’t find some information you are looking for a certain applications, or a good software library but lacking some option and features you need, why not create your own? If you have the luxury of time and you believe you can do it, why bother using a third-party software or the work of others.

Alright, aside from the pleasure I get writing codes, fulfilling my enthusiasm for extending the life of DBF file format and staying close to the xBase commands in terms of data manipulation; has given me a great deal of enjoyment particularly in the learning process, these already are greenlight indications that I should keep continuing what I’m doing.

Imagine without any heavy weight database engine or anything to be installed on ones computer but yet you can create a fast, multiuser and easy record manipulation even in billions of records.

C# Using Managed Dbf :

// Create table free instance
var _CUSTOMER = new MDb.Table();

// Open Dbf file in shared mode and read-only
_CUSTOMER.File.Open( @"\\MyServer\Fileman\Customer.Dbf",
                                       TableShare.Shared, TableAccess.ReadOnly, TableMaxSize.MaxOf16G );

// Set sort order by country + state


Visual FoxPro/xBase Equivalent commands :




What's has been extended so far  :

* Managed Dbf can open different types of DBF file format.
     - Can open Dbf file format even with unsupported field type.

* Extended the 2GIG limitation on table size.
     - The Dbf table is still compatible if the size less than 2GIG

* Extended the field name length to 32 default value is 10
     - The Dbf table is still compatible, Managed Dbf will only created
       an extension file to store some extended information.

* Not relying on CDX Index file, Managed Dbf uses ZDX for Index file
    - Since Managed is not using and abandoning VFP Runtime, VFPOLEBD, ADO/NET, ODBC
      I created my own custom index file and called it ZDX file : - D