This post was prompted by my reference to TDAMemDataTables in my recent podcast about our wiki.
First, a bit of background: ever since I first started developing, I’ve always been a bit obsessed by execution speed. I think this started with my first ever contract back in 1973 in IBM 360/370 assembler for a book publisher. The task sounds simple – produce an application to print address labels fast! Back then though, the notion of printer drivers didn’t exist – we wrote directly to the hardware. The twist to this contract though was that we got paid by the speed improvement over the existing system. Currently, lines were printed one line at a time and the label size was inflexible. I produced a solution that took two parameters (label width and page width) and wrote complete pages with a single call to the printer, thus getting almost a factor of 50 in speed improvement.
Later, 20 years ago, I had a Paradox for Dos contract to rewrite budget reports for a large international country. The thirty reports themselves were fairly simple and similar, apart from currency conversions everywhere, but as before, speed was the problem as the existing reports were taking nearly all night to produce. On examination, the reports formed a hierarchy but they were all written against the main data tables. Paradox was particularly slow on wide tables (i.e. with many fields). It was a simple job to produce several temporary narrow tables at suitable places in the hierarchy, and this resulted in a total run time of less than two hours. Such things help one’s reputation and the initial three months was extended to nearly five years with me finishing up as a project manager :)
These two examples illustrate one point – the power of cacheing to improve efficiency. Since adopting Delphi in 1994 (very impressed by its compilation speed) I’ve always tried to write efficient code. Nothing annoys me more than tardy screen refreshes etc. So, over the years I’ve tried many cacheing techniques with Delphi. For example, MemoryStreams, StringLists, IniFiles etc but they all fell short in one way and/or another.
Since Data Abstract introduced the Local Data Adapter (LDA), I’ve found a much better way of cacheing and that is what this blog is all about. Returning to my podcast reference at the top, it is easiest to explain using a simplified version of the problem I had to solve.
We generate the basic structure of wiki pages using XML files as basic input. These XML files contain data obtained from the interface sections of source files of all our products. As some wiki pages contain data that ultimately comes from up to three different XML files, some pre-processing is inevitable. This data can be used many times before the XML files are re-processed, thus causing updates to the data actually used by wiki page generation.
To keep it simple, there are four main temporary files used:
- Pages: each page entry has a list of Types
- Types: one or more entries per page
- Members: contains child records for Types
- Signatures: contains child records for Members
These four are the most important of about eighteen I actually maintain. The extra tables are mostly subsets of these four. For example, the Parents table contains the parent of each class, interface etc. and its index within the Parents table. The display text for the parent is stored too (i.e. whether displayed as a page link or normal text, so page production does not have to check each time it processes the class hierarchy).
My app is written using Delphi 2009, so these tables are used via TDAMemDataTables and loaded/stored to/from .daBriefcase files. Why do I do this, you may ask? Well, this is a single user application and all these files can be generated from scratch but they are purely there to gain speed. The only change from the standard database use of TDAMemDataTable is to hook up the LDA instead of the normal Remote Data Adapter (although field creation is different, as discussed below). All the usual functionality is there but I tend to use them in a slightly different way.
Having dropped a new TDADataTable component onto the DataModule and hooked it up to an LDA component, you add fields manually to the table via a right click. All the standard types are available and once you have all needed fields, you can save to a .dabriefcase file. An empty table is then available to my app by merely loading it from the file. All the normal operations are available for inserting/updating/deleting etc. and you can save back to disk at any time.
I use these datatables in a slightly unusual manner, to get as much speed as I can. First, I always set the Filtered property to True, which means that also having Filter=”, the default that means it’s unfiltered. I use the Filter instead of explicit SQL calls. The TMemDatatable filter syntax is very comprehensive (actually translates it into appropriate SQL). Most importantly, it is very fast and an additional consideration is that I can update a filtered table in situ by standard insert/edit/delete followed by post.
For a similar reason, the Types/Members/Signatures tables are not hooked up as master/detail/detail. For optimum performance, I filter the detail tables but not for all master table navigation. When actually needed, setting a filter on detail tables is very fast. Setting appropriate indexes is important though if tables are relatively large. Similar to the way you use ApplyUpdates on the normal Remote Data Adapter (RDA), the same can be done to apply changes on several local tables as a single transaction.
Using these techniques, I can display the structure of a complicated page ultimately derived from three XML files in less than a quarter of a second. There are many other scenarios where this type of code could be useful. Hands up those who have loaded aaa=bbb,ccc,…. type data into a TStringList only to discover that they need to process it in two or more different orders? Setting up a TDataTable and using its filter and sorting capabilites is much nicer to work with.
And Another Thing
When I was developing the wiki code, I soon discovered that being able to display the data in a table would make development easier. It was surprisingly easy to produce an app (which I called BriefView) which would display any .daBriefcase file in a DevExpress grid. I won’t show you everything in my BriefView application as the items missing are very basic Delphi components, such as buttons and dialogs, which should be obvious from the code below.
The Data Abstract components are fewer than you might expect: TDADataSource, TDAMemDataTable and TDABin2DataStreamer. I am also using the TcxGrid component from Developer Express, for reasons you will see below.
FormCreate logic is very simple:
procedure TForm1.FormCreate(Sender: TObject); begin if ParamCount > 0 then begin FileOpen.Dialog.FileName := ParamStr(1); FileOpened(nil); end; end;
The ShowOpened code is also called via a button to replace the display of a file by another:
procedure TForm1.FileOpened(Sender: TObject); var dc: TcxGridDBDataController; fn: string; begin CloseFile;
dtWork := TDAMemDataTable.Create(nil); dsWork.DataTable := dtWork; dtWork.LocalDataStreamer := LocalDataStreamer; dtWork.RemoteFetchEnabled := False; dtWork.LogicalName := ChangeFileExt(ExtractFileName(FileOpen.Dialog.FileName),''); dtWork.LoadFromFile(FileOpen.Dialog.FileName);
dc := tvWork.DataController; dc.GridView.ClearItems; dc.CreateAllItems; fn := ChangeFileExt(FileOpen.Dialog.FileName,'.grid'); if FileExists(fn) then tvWork.RestoreFromIniFile(fn,False,False,[gsoUseFilter, gsoUseSummary],dtWork.LogicalName); FileSaveAs.Dialog.FileName := FileOpen.Dialog.FileName; end;
This is virtually the application. The first half loads the table from disk, including FieldDefs, etc. The second is concerned with the UI only and is all the code needed to set up the grid with correct column widths together with persistance of filtering/grouping/sorting and columns actually displayed and their order.
Note that it starts with a call to CloseFile, which is called when the application is closed too, but first I need to show you SaveFile (which is called via a button):
procedure TForm1.SaveFile(Sender: TObject); var fn: string; begin fn := ChangeFileExt(FileSaveAs.Dialog.FileName,'.grid'); dtWork.SaveToFile(FileSaveAs.Dialog.FileName);
tvWork.StoreToIniFile(fn,True,[gsoUseFilter, gsoUseSummary],dtWork.LogicalName); FileOpen.Dialog.FileName := FileSaveAs.Dialog.FileName; end;
Finally, the simple CloseFile:
procedure TForm1.CloseFile; var fn: string; begin if dtWork=nil then exit;
dsWork.DataTable := nil; fn := ChangeFileExt(FileSaveAs.Dialog.FileName,'.grid'); tvWork.StoreToIniFile(fn,True,[gsoUseFilter, gsoUseSummary],dtWork.LogicalName); FreeAndNil(dtWork); end;
Summary
This post, hopefully, has given you some ideas as to how underlying Data Abstract technology can be used in your own projects.
