Wednesday, August 20, 2008

Delphi Insert vs Append Performance

For well over 10 years I have thought that the only difference between the Delphi TDataSet.Insert method and TDataSet.Append was where the record appeared in data aware controls before it was actually posted.

For most cases that is true. In fact if you look at the Delphi documentation you will find the method descriptions are almost identical and they both include the following:

"After the new record is applied back to the database server, its physical location is database-specific. For indexed tables, the index is updated with the new record information."

In some user interface situations I can certainly see a use for the Insert method, but I almost always use the Append method, especially when writing batch operations that insert a number of records.

There is a subtle implementation difference in the Delphi TDataSet code (the Delphi code, not the Advantage descendant code) that can have a rather dramatic affect on performance. A call to Append sets an EOF flag in the internal record buffer. A call to Insert does not. The internal EOF flag results in a call to TDataSet.Last before EVERY Post call. A call to Last results in an extra trip to the server. Not a big deal for a single insert, but in a batch insert operation from the client this can have a huge impact on performance, especially if the data is located on a remote machine.

In a very quick test I appended 10,000 records to a table using an Advantage remote server connection to a different PC (not the Advantage server on my test PC, I wanted the network traffic). I ran the test once using Append and once using Insert. I'm sure you can see where I'm going with this... Append took almost twice as long as Insert!

Append: 8359 ms
Insert: 4409 ms

Batch inserts are best performed via SQL, but I know lots of applications process them a row at a time using Append. If you have code that does this it might be worth your time to try testing with Insert instead.

Tuesday, August 5, 2008

Updating Static Cursors

Ever been happy because you normalized some tables, wrote a query that was way faster than it was before, only to find out later you now have to re-architect a portion of your application? Yep, your fancy join is way faster, but that simple grid interface that used to be a live cursor is now static, and users can't just update it directly anymore.

In version 7.1 (2004) support for triggers on views was added to Advantage. I'd like to take a quick look at how this feature allows you to update static cursors.

Consider the following grid interface to a bug tracking table:



Lets say you modified your schema and added a users table with additional information about each user; location and department. You also modified tables that reference users to store the userID now instead of the username. As a result, instead of a simple:


SELECT bugid, description, owner FROM bugs;

Your query now looks something like this:


SELECT b.bugid, b.description, u.name as "owner" 
FROM bugs b, users u
WHERE b.ownerID = u.id;

All is well, except users can no longer directly update cursor rows, as the result set consists of a join of two tables.

An elegant solution is to first create a view using the query above:


CREATE VIEW BugPreview AS
SELECT b.bugid, b.description, u.name
FROM bugs b, users u
WHERE b.ownerID = u.id


Next, create an INSTEAD OF UPDATE trigger on the view, handling the update yourself using your knowledge of the relationship between the two tables:


CREATE TRIGGER UpdateBugPreview
  ON BugPreview
  INSTEAD OF 
  UPDATE 
  BEGIN 
  declare @new cursor as select * from __new;

  open @new;
  fetch @new;

  try
    -- update fields in the bugs table
    update bugs set description = @new.description,
      ownerID = (select id from users where name = @new.name)
    where bugid = @new.bugid;
  finally
    close @new;
  end try;
  END;


Cursors using the BugPreview view can now be updated directly. Restoring live cursor functionality to a dataset that is really composed of two physical tables.

This technique allows you to normalize data and increase query performance while still maintaining simple "single table" front-end interfaces to your datasets. Adding INSTEAD OF DELETE and INSTEAD OF INSERT triggers in a similar fashion can provide DELETE and INSERT functionality on this static cursor as well.

Note there is some additional maintenance if you use this technique. You now need to be aware of these triggers if you modify the list of columns displayed by the view. Any changes to the column list will require some slight modifications to the triggers. This example also requires a unique user name, and doesn't handle the name not existing yet (you could catch this and insert a record, however).

In my next post I will include a screencast showing an implementation of this solution from start to finish.

Monday, August 4, 2008

API Documentation Searches with Firefox

I stumbled on something today that might be of use if you use Firefox and were not already aware of it.

I accidentally typed a Windows API name into the firefox destination edit box, instead of into my google toolbar edit box.

Normally I would expect the browser to either try to load "http://www.CreateFile.com" or to perform a web search for "CreateFile". I was surprised when I was immediately greeted with the MSDN documentation for the CreateFile API. No intermediate search and an extra click on the search results, which is my usual routine.

That was pretty handy. So I tried a Delphi function (GetMem), and sure enough I was greeted with GetMem documentation from www.delphibasics.co.uk.

This seems to work kind of like the Google "I feel lucky" search that pulls up the page with the highest rank. It's rather handy and much faster than waiting for the Microsoft document explorer to load and display help pages.