Tag Archives: SQL

Beginner, InterBase, Intermediate

Returning specific rows of data with InterBase

Leave a comment

Ever wanted to select specific rows from a dataset? Well InterBase has some very cool features in the SQL syntax  that allows you to do just that!

Paging Data

Adding the ROWS command to the end of your SQL statement (as per the example below) will return a specific number of records. For examples, to fetch the first 10 records just add rows 10.

Select * from MyTable
rows 10;

When you want to get the next 10 records you can use the extended syntax with the “to” command.

Select * from MyTable
rows 11 to 20;

The second query can obviously be used to fetch records 1 to 10 as well and is very useful when combined with a parameterised query to allow restful paging of data sets.

Fetching Page Headers

Now, say you wanted to get the first record of every page of data, you can also easily do this by adding an additional optional parameter “by” for example

Select * from MyTable
rows 1 to 100 by 10;

The above statement would return records 1, 11, 21, 31 etc… for the total records up to 100th record.

You can also skip records to return by a percentage of the dataset result size by simply adding “PERCENT” to the end of the above statement.

Select * from MyTable
rows 1 to 100 by 7 PERCENT;

Getting the top 10

But what if you want to use ROWS to get the top 10? Well, you can easily select 10 records, but what if the 11th and 12th record have the same value as the 10th? They should be returned as well, but how?

InterBase supports this easily by adding “WITH TIES”. To use “WITH TIES” you also need to include an “ORDER BY” clause to indicate what field to measure the ties on.  For example, to get the top 10 sales people by a field storing the sales_booked, you could run the following statement.

Select * from salespeople
order by sales_booked desc
rows 10 with ties;

This will include the top 10 and also anyone with sales_booked matching the 10th record.

Getting the first 10 percent

Following on, the first 10% of records can be returned by using the following syntax.

Select * from salespeople
rows 10 percent;

While getting 10% of records is a nice feature, combining this with the “ORDER BY” and “WITH TIES” makes a very easy way to select the top 10% of sales people (for example) rather than the top 10.

Summary

There are some very cool features in the InterBase language for defining the specific size and scope of data to return that lend itself nicely to a range of useful implementations around statistics and restful data service development.

For more fun details on the InterBase language, check out the language reference guide on http://docs.embarcadero.com/products/interbase

FireDAC, InterBase, Intermediate, Objective Pascal

Speeding up SQL processing with Array DML

2 Comments

If you want to really make your application scream, then one key area to get your interaction right is with the database layer. The example below becomes 8 (eight) times faster processing 10,000 records!

A number of high end databases (including Oracle and InterBase) include support for array inserts which, in short, is a much faster way to insert data. Rather than having to process record after record, the process accepts a block of changes that are processed in one hit. To be clear, this is not the same as starting and ending a database transaction, (something else that can also really help with speed).

Array DML in FireDAC helps you take advantage of database engines that support this feature easily. To see the difference in code and speed, lets explore a simple example. The example uses the following table called test containing two fields, Integer and string

CREATE TABLE test(Field1 INTEGER, field2 VARCHAR(20))

The typical way to insert 10000 records is to use a loop, where we have a query that just updates the parameters and executes the SQL.

const
  NUM_INSERTS = 10000;

procedure TForm7.Button1Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsInteger := i;
      FDQuery1.Params[1].AsString := 'Str' + IntToStr(i);
      FDQuery1.ExecSQL;
    end;
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

To alter this code to use Array DML, there is very little to do. Firstly, before setting the parameter values you can set the size of the array you want to insert. (You can always set this higher that needed if your not sure)

Once the array size is set, there is a small modification to the way the parameter values are set. FireDAC supports the plural setters for setting each record in the dynamic array. (see code below)

Finally, there execute command is changed to tell it to run for the total records you want to insert.

procedure TForm7.Button2Click(Sender: TObject);
var i: integer; t: integer;
begin
  FDQuery1.ExecSQL('DELETE FROM test');
  FDQuery1.SQL.Text := 'INSERT INTO test(field1, field2) values (:field1, :field2)';
  t := GetTickCount;
  FDQuery1.Connection.StartTransaction;
  try
    FDQuery1.Params.ArraySize := NUM_INSERTS;
    for i := 0 to NUM_INSERTS-1 do
    begin
      FDQuery1.Params[0].AsIntegers[i] := i;
      FDQuery1.Params[1].AsStrings[i] := 'Str' + IntToStr(i);
    end;
    FDQuery1.Execute(NUM_INSERTS,0);
  finally
    FDQuery1.Connection.Commit;
  end;
  (Sender as TButton).Caption :=  FloatToStr((GetTickCount() - t) / 1000);
end;

So what is the difference at run time? On my PC (virtual machine), running the 10,000 inserts takes 1.014 seconds in the first block of code, compared with 0.125 seconds with the second block of code. i.e. only 12% of the original processing time and over 8 times faster!

Thats about it! The code for the above Array DML FireDAC demo is available at code central http://cc.embarcadero.com/item/29832

An video example of this in action, along with a number of other FireDAC capabilities is also available in the FireDAC Session from Developer Direct Season 4 video.