Best way to implement SQL "Where" construction in Delphi?

Question

I have Firebird DB table with with fields (FK - ForeignKey Fields):

ID integer,
FACILITY_ID integer, --FK
SERVICE_ID integer, --FK
PROVIDER_ID integer, --FK
SELLER_ID integer, --FK
TARIFF_GROUP_ID integer, --FK
DATE_START date,
N_NORMATIVE_VALUE numeric(15,2),
N_METER_VALUE numeric(15,2),
T_NORMATIVE_VALUE numeric(15,2),
T_METER_VALUE numeric(15,2),
IS_ACTIVE smallint

I want to load this table into memory and work with it in my Delphi program (readonly). And I have to search records by all of FKs (.... where FACILITY_ID = :FACILITY_ID and SERVICE_ID = :SERVICE_ID etc..)

What is best container for that task: dynamic array or lists or something else? And how to implement this search? Hash maybe?

It's not very large table (1000 records), but will be used inside FOR construction with more than 100000 iterations.

And i'm using Delphi XE2 with IBX & UIB & FB25

You could and probably should use a TClientDataSet and use Filter property (to filter records) or Locate method (to locate records). — kobik
sure, but 100000 of SELECTs from disk is too slow. I'm want to operate with data in memory — Andrey Durow
The TClientDataset is in memory data set. (or any other TDataSet with client side cursor for that matter) — kobik
u may use embedded in-memory SQL like NexusDB. Or once i used FB25 Embed with GTTs for in-memory data (via UIB). Though last choice was poor - real input data failes specifications and i had to undo almost all SQL CONSTRAINTS :-) — Arioch 'The

Larry Lustig Larry Lustig · Accepted Answer · 2012-10-16T15:25:36

Are you suggesting that you want to load the entire table into memory and then duplicate the filtering functionality already offered by the database directly in Delphi? That seems like needless effort — why not just run SELECT statements against the database to get the records that you need?

In any event, the best structure to hold data retrieved from a database is some TDataSet descendant class. There are actually several sets of components to do just this for Firebird depending, among other things, on which SKU of Delphi you have available to you. Here's a list of available Firebird components: http://www.firebirdfaq.org/faq8/.

Best way to implement SQL "Where" construction in Delphi?

2 Answers