NexusDB

Q: What is the best way to perform Auditing of changes/updates via an Extender? Create and destroy the nx... objects within the event each time or implement a thread-safe cache of nx objects?

a) don't use the simple monitor for this. instead:

- implement your own monitor/extender classes

- don't implement anything in the monitor, instead keep your complete implementation (and all cursors you need to perform your updates/logging) in the extender. There will be one instance of the extender per cursor you are monitoring. as the cursor will never be used in a multi threaded way your extender doesn't need to be threadsafe.

b) never use any of the nxdb components in a monitor/extender. Instead: directly use the core server API defined in nxsdServerEngine.

c) make sure the cursor you are using to log your changes belongs to the same database/session as the cursor you are extending, otherwise transaction processing will not work as you expect.

for b) and c) if your extender is extending a TnxServerCursor, you can us the Database property of that to get hold of the  TnxServerDatabase object owning that cursor. The database object has a CursorOpen function that allows you to open other cursors owned by the same database and living in the same transaction context as the cursor you are extending. You can create that cursor when your extender is created and store it in the extender object. You won't need to do anything about transactions because there will always be an implicit or explicit transaction already active when your extender is notified about any of the eaRecord* events.For a complete example of how to implement monitors/extenders please take a look at the RefIntegrity classes in the bonus directory.

Q: I know that "many" indexes per table caused a performance drawback  (slow append and so on) in the "old" days, but how is it in Nexus? Is there any "max-count" of indexes per table that I should try not  to get above?

First some concepts:

B*Tree Index(which is what the default index implementation of NexusDB does):

There are 2 kinds of pages, internal and leaf nodes. actual keys are only stored in the leaf nodes, the internal nodes store values derived from that actual keys to lead the search for a specific key starting from the root node. The depth of the tree (number of pages from the root node to the leaf node containing the key) is always constant for all keys and increases as the tree grows. each page contains a sorted list of keys (or key derived values for internal nodes). Searching for specific key has to start at the root page, doing a standard binary search on that page (requiring the square root of the number of keys in the page of key comparisons), follow the reference to the next page on level deeper into the tree, searching again.. and so on till the key is found (or not found) in a leaf node.

The maximum number of keys per page depends on the size of the key (plus some constant overhead) and the size of a page (minus some constant overhead). The overheads are different for internal and leaf nodes. But for larger (>16 bytes) key sizes this isn't very relevant.

Each page is filled between 50-100%, the avg. fill factor in a regularly modified b*tree is 66.6%. This means the  AvgNumberOfKeysPerPage is

MaxKeysPerPage * 0.666.

The avg. number of leaf nodes is Ceiling(TotalNumberOfKeys /AvgNumberOfKeysPerPage )

The avg. depth of the tree is Ceiling(Xth root of LeafNodeCount) where x = AvgNumberOfKeysPerPage.

The total number of key comparisons is about AvgTreeDepth * Sqrt(AvgNumberOfKeysPerPage ).

Given all this information you can put a diagram together that shows how the number of required key comparisons needed to find a key in the index changes with the number of keys.

Inserting a record requires inserting one key per index (which requires a find operation to find the insertion point).

Deleting a record required deleting one key per index (which requires a find operation to find the key and generate a key path on the way from the root to the leaf).

Updating a record requires one delete and one insert per index which had it's key changed (2 find operations)

In addition to the pure time it takes to compare the keys when walking the tree it's especially important to consider the IO costs:

Inserting/Deleting a record (without any indices):

 read/write - 2 pages (table header page + data page)

The header contains the total count of records in the table and needs to be updated.

If this was the last used slot in the data page (for a delete) or if there were no data pages with empty slots available (for an insert) between 1 and 2 additional pages need to be read/written to either add the page back into the main list of reusable pages or get a new page from there or add an additional page to the table

Updating a record (without indices)   read/write - 1 page

Inserting/Deleting a key into an index:

 read - 1 page per level of the tree + indices header page (containing the root node page number)

 write - 1 leaf node page + indices header page (containing the total number of keys for the index)

If there are multiple indices the "indices header page" will be read/written only once as all indices use the same page, all modifications take place in an implicit transaction and the buffer manager will cache the page.

In addition, if the leaf node that the find operation determines as the insertion point/deletion point is either full (insert) or would be less then 50% after the operation (deletion) it's required to either balance the page with its siblings (adding 1 or 2 more page reads and 1 page write) or, if the siblings are also full/half-empty execute a page split (adding a new page, splitting the keys into 2 pages) or page merge (removing one page and merging the keys with the sibling). which costs a few more page reads/writes. In case of a split or merge the parent page needs to be updated as well and the operation will cascade upward through the tree as long as the parent page is full/half-empty and can not be balanced with siblings. If it reaches the root node of the tree a new root node is created (and the depth of the tree increases by one level, the new root node will have 2 keys) or the current root node is removed (and the depth of the tree

decreases by 1 level, this happens when the key count of the root node reaches 1).

Last thing we have to look at is page size. For each page read/write there is a (more or less static) time (drive seek time) + a variable time depending on the page size. Larger pages result in a higher fan-out factor (avg. keys per internal node)  of the tree, reducing the depth of the tree, reducing the number of pages read/written. At the same time, larger pages take longer to read/write and contain more information that is possible not relevant. The "negative" impact of larger pages is the highest if you do single record operations (insert/update/delete) and gets smaller if you use larger transactions (because multiple operations that write to the same page will only result in a single read / write).

In summary... the answer to your questions is "that depends".

Q: How do I access server side objects?

You can use the server engine's IterateDependents method for that.

 with bsServerEngine.IterateDependents do try

   for i := 0 to Pred(Count) do

     if TObject(Items[i]) is TnxStateComponent then

       if TnxStateComponent(Items[i]).Enabled then

         if not TnxStateComponent(Items[i]).Active then

           // do your stuff

 finally

   Free;

 end;

Q: What is the difference between the customcom transport and the registeredcom transport, and when should you use which?

Registered com transport uses the ROT (running objects table) which is a  system local list of running objects managed by windows to establish the  initial communication between client and server.

Custom com transport just calls an event and you have to implement that  event. passing the InxTransport that's passed into the event to the  server  side transport, call AttachRemoteTransport there, passing in the InxTransport and then pass the InxTransport you get back from that function back to the client.

How you do this is completely up to you. If both client and server transport are in the same process it's very easy. But you can also use e.g. DCOM or any other mechanism to pass the InxTransports between client and server.

Q: How does the Default property of Session, TransContext and Database work?

The Default property works like this:

For example do the following in code:

The table will be linked to that Database automatically, the database to that Session and Transcontext and the Transcontext to the Session.

Per thread there is one default Session, per Session there is one default Database and one default Database at any one time. It makes your code a lot easier if you are creating a lot of tables in code and want them all hooked up to the same database... or if you automatically want all your databases to share the same TransContext but

it doesn't just leave any properties to automatic instead. At runtime they are always resolved dynamically; at design time, if you use these default properties the links will be resolved once, the properties are set and after that changing the default has no further function.

The default Session is only applied when a TransContext / Database or Dataset is created in the same thread.

When a Session is assigned to a Database and the Transcontext property is currently nil it checks if there is a default TransContext and sets it.

When a Session is assigned to a Dataset (Table/Query) and the Database property is nil it checks if there is a default Database and sets it.

When you create a new Table it will first check if there is a default Session and assign it, then, because you are assigning a Session, it checks if there is a default Database.

This system will never change an existing assignment, only if a property is nil will it check if there is a default that applies to it.

Q: How do i change the data type of Field and preserve the data (for compatible fields)?

What I do is create a new dictionary with the new structure. Call NewDict.IsEqual(OldDict) and if not equal I perform a restructure on the table using the new dictionary and a field mapper (to make certain that the data is preserved). In short it comes down to this code:

 procedure RestructureTable(aDatabase : TnxDatabase;

   const aTableName : String; aNewDict : TnxDataDictionary);

 var

   OldDict : TnxDataDictionary;

   Mapper : TnxTableMapperDescriptor;

   TaskInfo : TnxAbstractTaskInfo;

   Completed : Boolean;

   TaskStatus : TnxTaskStatus;

 begin

   OldDict := TnxDataDictionary.Create;

   try

     if aDatabase.GetDataDictionaryEx(aTableName, '', OldDict) <> DBIERR_NONE then

       Exit;

     if OldDict.IsEqual(aNewDict) then

       Exit;

     Mapper := TnxTableMapperDescriptor.Create;

     try

       Mapper.MapAllTablesAndFieldsByName(OldDict, aNewDict);

       Check(aDatabase.RestructureTableEx(aTableName, '', aNewDict, Mapper, TaskInfo));

       if Assigned(TaskInfo) then

         try

           while True do begin

             TaskInfo.GetStatus(Completed, TaskStatus);

             Application.ProcessMessages;

             if Completed then

               break;

           end;

         finally

           TaskInfo.Free;

         end;

     finally

       Mapper.Free;

     end;

   finally

     OldDict.Free;

   end;

 end;

Q: How does "Automatic Physical Connection sharing" work and what are its benefits?

Without going into too much detail, the transports internally use a pool of physical connections (sockets, named pipes, shared memory sections) there is no 1:1 relationship between physical connections and opened sessions. Your client application can have 500 threads, each has opened 5 sessions, all using the same transport. If only 10 threads of these are actually inside a call to the server at the same time only 10 physical connections will be required for 2500 sessions. Opening a new session just requires a message over an already existing physical connection, instead of establishing a new connection.

Q: How does the TnxSessionPool component exactly work and what are it's benefits?

There are 2 main uses for it:

Q: How are triggers handled in terms of transactions?

SQL triggers are always executed with an active transaction. For any insert/delete/update the core engine will start an implicit transaction before any extenders are notified about the event. SQL triggers are executed inside extender notifications and as such execute in the context of this implicit transaction. The core engine will commit this implicit transaction only if no error was raises in any of the extenders or during the execution of the insert/delete/update, otherwise it will be rolled back.

Q: Why can't I create more than 4096 instances of a TnxMemoryStream at the same time?

Everything I'm saying now holds true for Windows. Most of it holds true for all operating systems running on the x86 platform in protected mode as most of it is dictated by hardware constraints. There are some things that I'll leave out of this description as they only complicate matters and have no major impact for user mode applications.

"Physical memory" refers to memory modules that are physically connected to the memory bus and which the CPU can access using physical addresses. Depending on the hardware such a "physical memory address" can have more then 32 bit. As user mode application developer you'll never get to see a physical address. Physical memory is separated into blocks of 4kb. The OS keeps track of what each of these 4kb blocks is used for. As user mode application developer you have no direct control over this at all.

Each process has it's own so called "virtual address space". As you are using 32bit pointers, there are 4294967296 possible addresses in a "virtual address space". 4GB. (Except in special cases) The upper 2GB of these addresses are reserved for the operating system, leaving the lower 2GB for the application. The address space is separated into blocks of 64kb. For each block the operating system keeps track if the block is currently free or if it's "reserved". Reserving virtual address space doesn't use any "physical memory". It only means that the OS now knows that a region of addresses is now in use. There are only 2 APIs that can reserve address space: VirtualAlloc and MapViewOfFile (other API functions may implicitly call these 2. e.g. LoadLibrary). In the end it all comes back to a central, per process, management structure in the kernel which efficiently prevents the same address region from being used for multiple purposes at the same time. It is *not possible* to get "address space" from the OS in sizes other then multiples of 64kb.

In addition, the address space of each process is divided into blocks of 4kb. For each of these 4kb blocks the OS keeps track of the following states:

There are only 2 APIs that can commit address space: VirtualAlloc and MapViewOfFile (again, may be indirectly called). "Committing" address space *doesn't* use any "physical memory"!

Every time that the CPU has to access memory using a "virtual address" (which means basically any form of memory access in user mode applications) it has to look up a mapping from the virtual address to the actual physical address. Depending on the state of the 4kb page the address is in the following will happen:

So, what does all this have to do with the original question?

const

 nxcl_1KB                        = 1024;

 nxcl_SystemAllocationSize       = 64 * nxcl_1KB;

constructor TnxMemoryStream.Create(aInitial : Integer);

begin

 if aInitial < nxcl_SystemAllocationSize then

   aInitial := 8 * nxcl_SystemAllocationSize;

...

and:

   Stream := TnxMemoryStream.Create(1024);