the Pascal Institute

Felix J COLIBRI

home
– papers_with_sources
  + design_patterns
  + colibri_utilities
    – tcp_ip_sniffer
    – sniffing_interbase
    – delphi_net_bdsproj
    – dccil_bat_generator
    – coliget_search_engine
    – dfm_parser
    – dfm_binary_to_text
  + db
  + web
  + graphic
  + controls
  + colibri_helpers
  + compilers
+ training
+ sweet_home
– site_map
– download
– search :
  Home  ==>  Papers  ==>  Colibri_utilities  ==>   Sniffing_interbase_traffic    

Sniffing Interbase Traffic - Felix John COLIBRI.

1 - Introduction

Interbase already comes complete with several logging and monitoring tools. However those tools mainly work at the application level. For instance, the Interbase Express monitor simply displays what Jeff Overcash decided to present: at chosen points, before sending orders to the Interbase Client, the Interbase Express unit writes some text to the Ibx monitor. The IbxSqlMonitor is a great tool, but its main purpose is to give the Delphi programmer some feedback of the progress of his requests. If you wish to analyze the traffic in terms of bytes, a lower level monitoring tool like a network sniffer comes handy.

In this paper we are going to use the sniffer that we built in the TCP IP Sniffer paper and will compare, as an example, the traffic generated by using different Interbase components: Ibx, the Bde and direct API calls to the Interbase Client DLL.

Before starting, I want to stress that we simply want to test the performance of the different access components. Our goal is NOT to snoop the meaning of the data transferred (the salary and stock options of your boss). The user can always encrypt his data, and the packet content will be opaque. We are interested by the volume of information, and will look at the content only to see what Interbase overhead is included in the packets (schema information and the like). This Interbase content analysis is precisely the benefit of sniffing over simple timing: we can identify the cause of the problem, and possibly find a remedy.



2 - Creating the Interbase Application

2.1 - The test bed

To have a flexible test bed, we are going to build the database and the table which we want to analyze with a small Interbase Express program. If you are familiar with Ibx programming, you can skip this part. You can also use an existing Database instead of creating a dedicated one like we are doing here.

2.2 - Create the Database

We create the Database using the usual CreateDatabase instruction:
   create a new application and call il "p_create_sniffed_database"
   place a tIbDatabase component on your tForm
   drop a tButton, create its OnClick event, and create the database:

const k_database_disk_path= '\\notebook\c\programs\fr\colibri_utilities\programs\interbase_sniffer\_data\';
      k_database_path= 'notebook:c:/programs/fr/colibri_utilities/programs/interbase_sniffer/_data/';
      k_database_name= 'ib_sniffer.GDB';

procedure TForm1.create_database_Click(Sender: TObject);
  begin
    IbDatabase1.Close;

    f_create_path(g_database_disk_path);

    with IbDatabase1, Params do
    begin
      Connected:= False;

      DatabaseName:= g_database_path+ k_database_name;
      SqlDialect:= 3;
      Clear;

      Add('USER '+ QuotedStr('SYSDBA'));
      Add('PASSWORD '+ QuotedStr('masterkey'));
      Add('PAGE_SIZE 4096');

      CreateDatabase;
    end; // IbDatabase1
  end; // create_database_Click

   compile, run and click Button1
Please note that:
  • we have placed the base on a remote PC. Sniffing TCP IP does NOT work on local bases: the TCP IP stack is not used, and therefore sniffing does not work
  • to make sure that the path exists, we called f_create_path which calls ForceDirectories to build the desired path. The parameter is a Windows parameter
  • to create the Database, we used an Interbase connection string, which is not exactly the same as the Windows path
  • we chose Interbase Dialect 3, which offers DateTime and huge integers, but you may prefer to stick with Dialect 1. The page size is the standard one that we use all the time, but changing this value might obviously have some influence on the benchmarking
  • we placed all the strings in constants, as you can see by looking at the .ZIP, the constants are all in an include file which is called by all our projects, to avoid any mis spelling
  • we also added in the .ZIP a DROP DATABASE button to be able to restart the whole trial again.


2.3 - Create the Table

The table is created with the classic CREATE TABLE request:

 CREATE TABLE my_table
     (field_1 type_1, field_2 type_2, ..., field_n type_n)

In our case:
   drop another tIbDatabase, a tIbTransaction and link those together.
Double click on tIbDatabase to open the connection editor, and fill in the DatabaseName, User, Password, remove the Login checkbox and hit "Test" to check the connection:

   drop an tIbSql component, link it to IbDatabase1
   place a tButton, create its OnClick event, and create a "reasonable" table:

const k_sql_create_table=
            'CREATE TABLE '+ k_table_name
          + '  ('
          + '     s_id INTEGER, '
          + '     s_company CHAR(40), '
          + '     s_address CHAR(50), '
          + '     s_contact CHAR(20), '
          + '     s_amount_due DOUBLE PRECISION'
          + '  ) ';

procedure execute_sql(p_sql_query: String);
    // -- this procedure will sent any "write" sql request to the remote database
  begin
    with Form1, write_ib_sql do
    begin
      Close;

      with Sql do
      begin
        Clear;
        Add(p_sql_query);
      end; // with Sql

      Try
        write_IbDatabase.Open;
        write_IbDatabase.DefaultTransaction.Active:= True;

        if write_IbDatabase.DefaultTransaction.InTransaction
          then write_IbDatabase.DefaultTransaction.Commit;

        write_IbDatabase.DefaultTransaction.StartTransaction;

        ExecQuery;

        write_IbDatabase.DefaultTransaction.Commit;

        display('  ok');
      except
        on e: Exception do
          begin
            write_IbDatabase.DefaultTransaction.RollBack;
            display('  *** exec '+ e.Message);
          end;
      end;
    end; // with IbSql1
  end; // execute_sql

procedure TForm1.create_table_Click(Sender: TObject);
  begin
    write_IbDatabase.Close;
    write_IbDatabase.DatabaseName:= g_database_path+ k_database_name;

    execute_sql(k_sql_create_table);
  end; // create_Click

   compile, run and click Button1


Note that:

  • we have placed the creation request in a litteral constant, as explained above
  • the creation itself has been split in two:
    • a general procedure execute_sql, which sends any "write" request to the Interbase Server (CREATE INDEX, DROP CONSTRAINT, ALTER COLUMN : anything but SELECT  )
    • the procedure create_table_Click to send the CREATE TABLE request
    The generic write request procedure will be used later for populating the table
  • in the .ZIP we added a "drop_table_" button to allow several runs.


2.4 - Populate the Table

The addition of rows in a table is performed with the INSERT INTO request:

 INSERT INTO my_table
     (field_1, field_2, ... , field_n)
     VALUES (value_1, value_2, ... , value_n)

To have a reasonable amount of data available, we automated the generation using random numbers:

  • we extracted the client names and adresses from the CUSTOMER table from DBDEMO. Those were saved in a .TXT file
  • we load this file in a tStringList
  • for each row in our table, we generate a random number from 0 to tStringList.Count- 1, and this will become our new customer.
The INSERT INTO request itself is built by simply inserting the radom generated values, and the whole process driven by a classic FOR loop.



Let's first extract the values from the DBDEMO table (or any table of your choice):

  • drop another tButton on the tForm, call it "extract_bde" and type the extraction text:

    const k_company_list_name= 'company.txt';
          k_address_list_name= 'address.txt';
          k_contact_list_name= 'contact.txt';

    procedure TForm1.extract_bde_Click(Sender: TObject);
      var l_c_company_name_list, l_c_address_list, l_c_contact_list: tStringList;
      begin
        l_c_company_name_list:= tStringList.Create;
        l_c_address_list:= tStringList.Create;
        l_c_contact_list:= tStringList.Create;

        With extract_table do
        begin
          DataSource1.DataSet:= extract_table;
          Open;
          First;
          While Not Eof do
          begin
            l_c_company_name_list.Add(FieldByName('Company').AsString);
            l_c_address_list.Add(FieldByName('Addr1').AsString);
            l_c_contact_list.Add(FieldByName('Contact').AsString);

            Next;
          end; // While Not Eof
        end; // With IbQuery1

        l_c_company_name_list.SaveToFile(g_database_disk_path+ k_company_list_name);
        l_c_company_name_list.Free;

        l_c_address_list.SaveToFile(g_database_disk_path+ k_address_list_name);
        l_c_address_list.Free;

        l_c_contact_list.SaveToFile(g_database_disk_path+ k_contact_list_name);
        l_c_contact_list.Free;
      end; // extract_bde_Click



And here is the insertion part:
   drop another tButton on the tForm, call it "insert", and type the insertion text:

function f_double_to_sql_string(p_double: Double): String;
  var l_save_decimal_separator: Char;
  begin
    l_save_decimal_separator:= DecimalSeparator;
    DecimalSeparator:= '.';
    Result:= FloatToStr(p_double);
    DecimalSeparator:= l_save_decimal_separator;
    Result:= f_replace_character(Result, ',', '.');
  end; // l_save_decimal_separator

procedure TForm1.insert_Click(Sender: TObject);

  procedure insert_generic(p_id: Integer; p_company_name, p_address, p_contact: String;
      p_amount_due: Double);
    var l_values: String;
        l_sql_request: String;
    begin
      l_values:=
            IntToStr(p_id)+ ', '
          + QuotedStr(p_company_name)+ ', '
          + QuotedStr(p_address)+ ', '
          + QuotedStr(p_contact)+ ', '
          + f_double_to_sql_string(p_amount_due);

      l_sql_request:= k_sql_insert_partial
        + ' ('+ l_values+ ')';
      execute_sql(l_sql_request);
    end; // insert_generic

  var l_c_company_name_list, l_c_address_list, l_c_contact_list: tStringList;
      l_line_index: Integer;

      l_sample_count: Integer;
      l_company_name_random, l_address_random, l_contact_random: String;
      l_amount_due_random: Double;

  begin // insert_Click
    write_IbDatabase.Close;
    write_IbDatabase.DatabaseName:= g_database_path+ k_database_name;

    l_c_company_name_list:= tStringList.Create;
    l_c_company_name_list.LoadFromFile(g_database_disk_path+ k_company_list_name);
    l_sample_count:= l_c_company_name_list.Count;

    l_c_address_list:= tStringList.Create;
    l_c_address_list.LoadFromFile(g_database_disk_path+ k_address_list_name);

    l_c_contact_list:= tStringList.Create;
    l_c_contact_list.LoadFromFile(g_database_disk_path+ k_contact_list_name);

    for l_line_index:= 0 to StrToInt(insert_count_edit_.Text)- 1 do
    begin
      l_company_name_random:= l_c_company_name_list[Random(l_sample_count)];
      l_address_random:= l_c_address_list[Random(l_sample_count)];
      l_contact_random:= l_c_contact_list[Random(l_sample_count)];
      l_amount_due_random:= 23000+ Random(150000)/ 10;

      insert_generic(1000+ l_line_index, l_company_name_random, l_address_random,
          l_contact_random, l_amount_due_random);
    end; // for l_line_index
  end; // insert_Click

Notice that we used QuotedStr to avoid any single vs double quote, which are not the same in Interbase Dialect 1 and Dialect 3

   compile, execute and click
   here is our application in full swing:




Our base is ready, and we can easily change the content of our table (add columns, different types, increase the row count...). You can also add some bells and whistles, like:
  • an edit to allow the user to set the line count
  • a button to empty the table


3 - Sniffing Interbase Express

We are now ready for the main action. We will launch a SELECT query, and measure the traffic of this instruction. So we will:

3.1 - The Sniffing part

The sniffing part is done by importing the c_packet_sniffer into our project. A you may remember, this CLASS can start the sniffing, and collect statistics about the packet count and sizes.

The use of the c_packet_sniffer would still require the tedious link of all collected data to visual controls of our application. We already did this display in the p_packet_sniffer application, but we do not wish to build the SELECT in that main form. Regrettably Delphi offers no way to isolate a group of controls from a Form. We can try to do some "Save As" gimmickery, or use copy and paste, but that's about it. Of course we can create a brand new component, but this would be an overkill. So we will use a CLASS which will create the visual controls "by hand", like in olden OWL time, and use this CLASS, and use this c_display_packet CLASS in our project.

Here is a snapshot of our previous capture Form:

What we want is the lower part (tNoteBook, tListView) and some count labels.

We created a CLASS:

  • with the following definition:

     c_display_packets= class(c_packet_sniffer)
                          m_c_page_control: tPageControl;
                          m_c_stats_tab_sheet: tTabSheet;
                          m_c_line_list_view: tListView;
                          m_c_in_label, m_c_out_label, m_c_in_out_label,
                              m_c_packet_count_label: tLabel;
                          m_list_packets: Boolean;
                          m_c_stats_memo: tMemo;

                          Constructor create_display_packets(p_name: String;
                              p_c_owner: tWinControl);
                          procedure start_capture(p_adapter_index: Integer;
                              p_collect_statistics: Boolean;
                              p_file_name: String; p_list_packets: Boolean);
                          procedure add_list_view_line(p_id, p_in_or_out,
                              p_protocol, p_sub_protocol, p_bytes,
                              p_source_ip, p_source_port,
                              p_destination_ip, p_destination_port: String);
                          procedure display_packet(p_c_packet_sniffer: c_packet_sniffer);
                          procedure stats_button_click(Sender: tObject);
                          Destructor Destroy; Override;
                        end; // c_display_packets

  • the constructor builds all the controls:

    Constructor c_display_packets.create_display_packets(p_name: String;
        p_c_owner: tWinControl);
      var l_c_stats_panel: tPanel;
      begin
        Inherited create_packet_sniffer(p_name);

        l_c_stats_panel:= tPanel.Create(p_c_owner);
        with l_c_stats_panel do
        begin
          Parent:= p_c_owner;
          Height:= 21+ 6;
          Align:= alTop;

          // ... the rest of the construction
        end; // with l_c_stats_panel do
      end; // create_display_packets

    this method which is about 150 lines long has been truncated for this display

  • and the project using the capture class simply calls:

    procedure TForm1.start_Click(Sender: TObject);
      begin
        g_c_display_packets:= c_display_packets.create_display_packets('display_packets',
            sniff_panel);

        g_c_display_packets.start_capture(k_default_adapter, k_collect_statistics_true, 
            'c:\log.bin', k_list_packets_true);
      end; // connect_Click



We build a small project with a tPanel (the owner and parent of our packet display) and a "star" and "stop" button, and the .ZIP is available. The c_display_packet CLASS could easily be turned into a component, but our main goal being to compare Interbase component we will not do it here.

3.2 - The Interbase Express SELECT  

Let us now write the Interbase Express applications
   create a new application and call il "p_sniff_ibx_select"
   drop a tIbDatabase, a tIbTransaction and link those together.
Double click on tIbDatabase to open the connection editor, and fill in the DatabaseName, User, Password, remove the Login checkbox and hit "Test" to check the connection
   place a tPanel on the Form to display the capture statistics
   place a tButton, create its OnClick event, call it "connect" and write the capture and connection code:

procedure TForm1.connect_Click(Sender: TObject);
  var l_log_name: String;
  begin
    if sniff_.Checked
      then begin
          g_c_display_packets:= c_display_packets.create_display_packets('display_packets',
              sniff_panel);

          if log_.Checked
            then begin
                f_create_path(k_local_log_path);
                l_log_name:= k_local_log_path+ f_now_to_string+ '_log_ibx.bin'
              end
            else l_log_name:= '';
          g_c_display_packets.start_capture(0, True, l_log_name, k_list_packets_true);
        end;

    IbDatabase1.Open;
  end; // connect_Click

   drop an tIbQuery component, link it to IbDatabase1.
   place a tButton, create its OnClick event, call it "select" let it open the table:

const k_table_name= 'sniffer_table';
      k_sql_select= 'SELECT * FROM '+ k_table_name;

procedure TForm1.select_Click(Sender: TObject);
  begin
    DataSource1.Dataset:= Nil;

    with IbQuery1 do
    begin
      Close;

      with Sql do
      begin
        Clear;
        Add(k_sql_select);
        Open;
      end; // with Sql
    end; // with IbQuery1
  end; // select_Click

   compile, run and click:

Here is an example of opening the table:

And when we check "stats", we get the byte counts (we only show the capture window):

You may have noticed that the packet count is not exactly the same at the count in the previous figure: by the time we had copied the figure to the clipboard and had inserted it in this paper, the network had sent some additional packets. We kept the discrepancy to show you that getting an accurate picture might require some quick foot work.


3.3 - Monitoring Ibx

To see which instruction have been sent, we can add an IbSqlMonitor:
   drop a tIbSqlMonitor on Form1. Toggle its Enabled property to True. Create ist OnSql property and let it display each log message:

var g_event_count: Integer;

procedure TForm1.IBSQLMonitor1SQL(EventText: String; EventTime: TDateTime);
  begin
    display('= ===== '+ IntToStr(g_event_count));
    Inc(g_event_count);
    display('= '+ EventText);
  end; // IBSQLMonitor1SQL

   here is the monitor display:

This figure show that we can display the Ibx monitoring event together with the packet count. For instance:

= 23 ===== 1
IBTransaction1: [Start transaction]


This simultaneaous monitoring and packet sniffing is interesting because:
  • it shows that the monitoring is performed by the Interbase Express layer, at chosen points, and the information is synthetized to be as useful as possible to the programmer. The goal is not to measure traffic.
  • it also proves how easy it is to intergrate the sniffing class into a project, and display different informations. When we use a separate sniffing tool, we have to note on a piece of paper the timing of the user events and the packet count to try to get an accurate picture. In addition, the possibility to save the captured packets on disk allows us to perform different kind of studies on the data flow off-line, whereas on-line packet analysis would force us to restart the trials again and again.


4 - 1- Sniffing the BDE

We can compare the traffic by using a BDE application:
  • create a new application


We also used direct Interbase Api calls, but will not present the detailed application here, since this would drag us into the isc_xxx programming presentation.



5 - The Results

To have a point of comparison, we computed the rock bottom traffic amount one could expect. This simply is the binary size of record. In our case:
  • s_id INTEGER = 4 bytes
  • s_company CHAR(40) = 40 bytes
  • s_address CHAR(50) = 50 bytes
  • s_contact CHAR(20) = 20 bytes
  • s_amount_due DOUBLE PRECISION = 8 bytes
So each record is 122 bytes long.



We filled the table with 100, 1.000 and 10.000 records, and compared the results for Interbase Express and the BDE:

 records  database  raw  ibx  bde
 100  588 K  12 K  111 (57 K)  58 (24 K)
 1.000  692 K  122 K  268 (199 K)  219 (166 K)
 10.000  1.712 K  1.220 K  1.846 (1.618 K)  1.770 (1.584 K)

In this figure:

  • the "database" figure is the size read in a Windows Explorer
  • "raw" is the amount of the "bare minimum" data size
  • "ibx" is the result of the capture
    • the first number is the packet count (in and out)
    • the second is the packet byte count. This figure is the packet content count (without the header). If you wish to compare this amount to the database size, you must add 54 bytes per packet (14 for Ethernet, 20 for IP and 20 for TCP) which adds a 4% overhead (neglecting the handshake packets, the error packets, packets of smaller size etc).
  • "bde" contains the results for the BDE capture


A couple of points a worth mentioning:
  • when I first performed the 10.000 trial, I kept having 900 K capture logs, whereas the bare minimum is 1.220K. I then stopped displaying the data and added Application.ProcessMessage in the WHILE loop: the result is as displayed above.

    The reason is that when the application processing is too important, the capture misses some packets. Those are mainly at the end of the capture (up to 7000 all packets were recorded, and the end was missing. This was discovered by checking the s_id values in the recorded packets).

    This also demonstrates that using packet capture remains a tricky business.

  • a dump of a single record transfer showed that the true Interbase record sent over the wire was 152 bytes long. This was easily measured by computing the distance between two consecutive records: 


    
51 i 00 E0 18 E5 4D C2 00 90   : <========= 00-90-F5-0F-0C-1F 00-E0-18-E5-4D-C2
     F5 0F 0C 1F 08 00 ______  : <
     45 00 03 A4 C3 55 40 00   : <IP        192.168.0.3 192.168.0.2
     80 06 B2 A8 C0 A8 00 03   : <
     C0 A8 00 02 ____________  : <
     0B EA 06 D4 C1 2E 8D 00   : <     TCP  3050 1748 sz=892
     A4 23 B4 8D 50 18 FD CF   : <          psh,ack,
     12 FE 00 00 ____________  : <          ack= 8DB423A4, seq= 008D2EC1
     33 33 33 33 73 60 DC 40   : <          3333s`Ü@<
     00 00 A0 3A 00 00 00 42   : <          .. :...B<
     - 
     00 00 00 00 00 00 00 01   : <          ........<
     4E 04 00 00 00 00 47 6F   : <          N.....Go<
     6C 64 20 43 6F 61 73 74   : <          ld Coast<
     20 53 75 70 70 6C 79 20   : <           Supply <
     20 20 20 20 20 20 20 20   : <                  <
     20 20 20 20 20 20 20 20   : <                  <
     20 20 20 20 20 20 00 00   : <                ..<
     37 2D 37 33 37 36 33 20   : <          7-73763 <
     4E 61 6E 61 6B 61 77 61   : <          Nanakawa<
     20 52 6F 61 64 20 20 20   : <           Road   <
     20 20 20 20 20 20 20 20   : <                  <
     20 20 20 20 20 20 20 20   : <                  <
     20 20 20 20 20 20 20 20   : <                  <
     20 20 00 00 4D 61 72 69   : <            ..Mari<
     61 20 45 76 65 6E 74 6F   : <          a Evento<
     73 68 20 20 20 20 20 20   : <          sh      <
     00 00 00 00 00 00 00 00   : <          ........<
     00 00 00 00 00 16 DB 40   : <          ......Û@<
     00 00 A0 3A 00 00 00 42   : <          .. :...B<
     -
     00 00 00 00 00 00 00 01   : <          ........<
     4F 04 00 00 00 00 55 6E   : <          O.....Un<
     69 73 63 6F 20 20 20 20   : <          isco    <



In any case, the trial demonstrate that Ibx carries a 30 K overhead. We naturally looked at the content of the Ibx packets on a 1 record table. There were lots of nearly-empty packets. Obviously there was some startup computation performed by Ibx.

At the end, we remembered that we had added the IbMonitor to our application. Changing IbMonitor's Enabled property to False, and even deleting the component form the Form did not change anything. However toggling all IbDatabase TraceFlags to False did the trick: Interbase Express is about 1 K leaner (1.583 K) then the BDE.



So, at the end of the day, the conclusion is the following:

  • Ibx and the BDE have about the same traffic in SELECT statements. This is not too surprising, since all calls are converted to nearly the same isc_xxx Api calls.
  • the use of IbSqlMonitor adds a fixed 30 K overhead in traffic


6 - Improvements

6.1 - Benchmarks

  • all benchmarks can be challenged. We could spend days trying out all kinds of benchmarking schemes and strategies, there is always going to be some user claiming, usually rightly so, that this benchmark does not correspond to the way he is using Interbase. We present here an example, and it will be easy to tweak the code here or there to suit your needs.
In our case
  • we would have to make sure that our trials are not biased by previous experiments. In particular, we should be suspicious of the caches present at many levels (the PC, Interbase, Delphi).
  • time measurements should also be performed without any sniffing in the loop, to avoid any spurious interactions

    In this area, Ibx and the Bde had about the same performance, about a couple of seconds for 100.000 records (on a local 100 MB network). The longest was the ending of the connection (about 7 seconds).

6.2 - Level of Interbase Sniffing

At the client level, we have 3 tracing possibilities:

  • at the first level, the IbSqlMonitor displays informations about the request which are going to be sent to the Interbase Client
  • at the second level, that we named the "proxy" level, you can trap the requests as they are sent to the Interbase Client. For instance, when I was working under Linux I wrote in Delphi a DLL (under Linux it is not named a DLL but it is the same concept). This DLL simply received the request from Ibx, displayed the isc_xxx command AND all its parameters, and forwarded the request to the real Interbase Client. The difference with IbSqlMonitor is that I had all control over what I wanted to display
  • the third level is the network level, which we presented in this paper. We can use general sniffing tools, but have to interpret the raw packet content.


6.3 - Other possibilities

Now that we have the tool, we could study other settings:
  • what is the the impact of the different Interbase Client level or Interbase Server level tunings (cache size, packet size etc)
  • compare the different Interbase versions (Super Server vs Classic, Firebird 1.5 vs Interbase 7.5)
  • analyze the effect of using other Delphi components: dbExpress and the much acclaimed more efficient drivers, or the Borland Data Provider on top of Windows Forms
  • add timing measurements
  • what is the impact of using VARCHAR instead of CHAR (but there we would also add timing measurements, and compare first insert and modifications with longer or shorter values)
  • similar question for parametrized queries vs direct SELECT  
  • forcing some schema loading on our client (by opening tables)
  • using nested tables vs separate master and detail
  • what is the cost of closing and reopening the connection ?
  • an easy one now: using stored procedures and triggers or calling everything over on the Client side. Using a stored procedures for sequence fetching or grabbing the value directly in RDB$DATABASE.
  • transactions. Ah, transactions, that's my favorite one. Just throwing a curve at you: what would be the difference between "SnapShot" and "Read Committed" ?. And what about letting Delphi handling all the transactions (in a single default transaction) versus trying to do it all ourselve, possibly with multiple transactions ?
The list goes on and on. This is an ideal field of investigation for a Master Student looking for an easy thesis subject. With nice graphics all over the place, which always look good at presentation time. And everything can be done in Delphi: the sniffer sources have been published in Delphi in our previous paper, and the creation of the data to measure is part of this paper. tTeeChart to the rescue to draw the lovely graphics.

On a more practical level, I started this sniffing because I did not understand the time taken by some requests. This certainly would be my advice: only dwelve in this area if your are forced to do so because of performance considerations.



7 - Download the Source Code

We placed all the sources for the projects in the following .ZIP files:

Those files contain:
  • the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
  • any .TXT for parameters
  • all units (.PAS) for units
The .ZIPs
  • are self-contained: you will not need any other product (unless expressly mentioned).
  • can be used from any folder (the pathes are RELATIVE)
  • will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
To use the .ZIP:
  • create or select any folder of your choice
  • unzip the downloaded file
  • using Delphi, compile and execute
To remove the .ZIP simply delete the folder.

Please send us any feedback at fcolibri@felix-colibri.com should you have any difficulties downloading or using those files.



8 - Conclusion

We presented how to use a TPC IP Sniffer to compare Interbase network traffic.



9 - References

  • Ten Things You Can do to Make Interbase Scream
    Bill KARWIN - Borland Conference 1998
    One of the numerous paper about Interbase optimization
  • The Firebird Book
    Helen BORIE - Apress - ISBN 1 59059 279 4
    The most complete book about Interbase programming, at the Sql, and C level. Also very good explanations about the architecture.
  • TCP IP Sniffer the companion paper where we presented the Sniffer capture and display applications


10 - Other Papers with Source and Links

UML Patterns
the lexi editor delphi source code of the Gof Editor: Composite, Decorator, Iterator, Strategy, Visitor, Command, with UML diagrams
factory and bridge patterns presentation and Delphi sources for the Abstract Factory and Bridge patterns, used in the Lexi Document Editor case study from the GOF book
gof design patterns delphi source code of the 23 Gof (GAMMA and other) patterns: Composite, Decorator, Iterator, Strategy, Visitor, Command
TCP/IP
tcp ip sniffer project to capture and display the packets travelling on the Ethernet network of your PC.
sniffing interbase traffic capture and analysis of Interbase packets. Creation of a database and test table, and comparison of the BDE vs Interbase Express Delphi components
socket programming the simplest Client Server example of TCP / IP communication using Windows Sockets with Delphi
delphi socket architecture the organization of the ScktComp unit, with UML diagrams and a simple Client Server file transfer example using tClientSocket and tServerSocket
Utilities
the coliget search engine a Full Text Search unit allowing to find the files in a directory satisfying a complex string request (UML AND Delphi OR Patters)
Delphi utilities
delphi net bdsproj structure and analysis of the .BDSPROJ file with the help of a small Delphi .XML parser
dccil bat generator generation of the .BAT for the Delphi DCCIL command line compiler using the .BDSPROJ
dfm parser a Delphi Project analyzing the .DFM file and building a memory representation. This can be used for transformations of the form components
dfm binary to text a Delphi Project converting all .DFM file from a path from binary to ascii format
Database
database reverse engineering Extraction of the Database Schema by analyzing the content of the application's .DFMs
sql parser Parsing SQL requests in Delphi, starting from an EBNF grammar for SELECT, INSERT and UPDATE
ado net tutorial a complete Ado Net architectural presentation, and projects for creating the Database, creating Tables, adding, deleting and updating rows, displaying the data in controls and DataGrids, using in memory DataSets, handling Views, updating the Tables with a DataGrid
turbo delphi interbase tutorial develop database applications with Turbo Delphi and Interbase. Complete ADO Net architecture, and full projects to create the database, the Tables, fill the rows, display and update the values with DataGrids. Uses the BDP
Web
sql to html converting SQL ascii request to HTML format
simple web server a simple HTTP web Server and the corresponding HTTP web Browser, using our Client Server Socket library
simple cgi web server a simple CGI Web Server which handles HTML <FORM> requests, mainly for debugging CGI Server extension purposes
cgi database browser a CGI extension in order to display and modify a Table using a Web Browser
whois a Whois Client who requests information about owners of IP adresses. Works in batch mode.
web downloader an HTTP tool enabling to save on a local folder an HTML page with its associated images (.GIF, .JPEG, .PNG or other) for archieving or later off-line reading
web spider a Web Spider allowing to download all pages from a site, with custom or GUI filtering and selection.
asp net log file a logging CLASS allowing to monitor the Asp.Net events, mainly used for undesrtanding, debugging and journaling Asp.Net Web applications
asp net viewstate viewer an ASP.NET utility displaying the content of the viewtate field which carries the request state between Internet Explorer and the IIS / CASSINI Servers
Graphic
delphi 3d designer build a 3d volume list, display it in perspective and move the camera, the screen or the volumes with the mouse.
Controls
find memo a tMemo with "find first", "find next", "sort", "save" capabilities
Helper units
windows environment read and write Windows Environment strings
stdin stdout send and receive strings from a GUI application to a CONSOLE application
 
Other
valid delphi links 430 Delphi Web pages, sorted by category (Database, Xml, Web programming, TCP/IP etc), with special attention to source code availability



11 - The author

Felix John COLIBRI works at the Pascal INSTITUTE. He programs in Pascal since 1979, and in mainly active in the area of software development and training. His web site features tutorials, technical papers about programming with downloadable source code, and the description and calendar of forthcoming Delphi, Interbase and UML training sessions.
Created: nov-04. Last updated: sep-06.  © Felix J. Colibri 2006  http://www.felix-colibri.com
Back:    Home  Papers  Training  Links  Download