0

Firebird Db stores chart accounts records in table:

CREATE TABLE CHARTACC 
 (
  ACCNTNUM       Char(8) NOT NULL, -- Account ID (Primary Key)
  ACCPARNT       Char(8),          -- Parent ID
  ACCCOUNT       Integer,          -- account count
  ACCORDER       Integer,          -- order of children in nodes  
  ACCTITLE       varchar(150),     
  ACDESCRP       varchar(4000),
  DTCREATE       timestamp         -- date and time of creation
  )

I must write query which selects from table only last nodes e.g.nodes which haven't child nodes(child2, child3, subchild1, subchild2, subchild3 and subchild4).

enter image description here

Arioch 'The
  • 15,799
  • 35
  • 62
basti
  • 399
  • 2
  • 10
  • We know nothing about your database table's structure. The answer is most likely in how the query is written, but you haven't provided us any query or information about this table. Specifically, how the parent/child relationship works. All we see is a screenshot of a tree and a screenshot of an Excel spreadsheet. But how are you actually acquiring that information in the first place? What is your actual goal to accomplish here? Are you looking to pull a list from the DB? Or are you looking to iterate through tree view items? Entirely different answers. – Jerry Dodge Dec 12 '18 at 01:34
  • Jerry Dodge, I edited post. My goal is write sql-query which selects from table only last nodes e.g.nodes which haven't child nodes – basti Dec 12 '18 at 01:50
  • 1
    `select * from CHARTACC where ACCNTNUM not in (select ACCPARNT from CHARTACC)` seems the most straight-forward way. But I'm extremely concerned at your choice of field types. `Char` shouldn't really be used for identifiers. – Jerry Dodge Dec 12 '18 at 01:53
  • Jerry Dodge, thank you. It works. ACCNTNUM is primary key, unique filed. I used numbers, alphabetical symbols, such as: 1000, 1010, Sale001, Profit01 etc. – basti Dec 12 '18 at 02:11
  • 1
    That's still a terribly inefficient way to design it. Those sorts of references should be at least by `int`. Idk about Firebird, but in SQL Server, there's identity which auto-increments, and other field types which can be used explicitly for identification. `Char` should only be used for text. And I typically use `Varchar` most of the time. – Jerry Dodge Dec 12 '18 at 02:18
  • 1
    PS - Next time, it's not necessary to use the Delphi tag if all you need is something SQL related. Tag it `sql` instead. That will get the right people to respond. You should also tag the generic tag along with any specific version tag. Because people follow certain tags. You can't expect people to follow all possible version tags here. They follow the main generic tag, in this case just `firebird`. Tagging a specific version will rarely be seen by us. – Jerry Dodge Dec 12 '18 at 02:22
  • BTW, there is at least three different database structures that represent trees :-) – Arioch 'The Dec 12 '18 at 08:07
  • @JerryDodge interbase/firebird had semi-automatic autoinc fields by SQL `sequence` + `before update or insert triggers`. In FB 3 syntactic sugar was added to wrap it all into a "data type" declaration. However fixed-size `CHAR(8)` might not be that bad really, especially if one would choose some fixed-size 1-byte-per-char codepage, rather than varied-size UTF-8. Consider Int64 - the same 8 bytes. Or consider yet longer GUID. If you put text-specific issues aside it is not that bad here as it might seem. Though *long* text fields would be definitely bad for indexing. – Arioch 'The Dec 12 '18 at 08:11
  • @Arioch'The, I use Georgian symbols or/and numbers, such as: ხელფ10, ფული1100, მოგ1200, 3110. – basti Dec 12 '18 at 12:15
  • 1
    @basti then it might make some sense to make ID like int32 or int64, especially if the names you give have some real world meaning (traditional argument about natural vs synthetic keys). – Arioch 'The Dec 12 '18 at 13:46

2 Answers2

3

The not in approach suggested by Jerry typically works quite slow in Interbase/Firebird/Yaffil/RedDatabase family, no indices used, etc.

Same goes for another possible representation Select X from T1 where NOT EXISTS ( select * from t2 where t2.a = t1.b) - it can turn out really slow too.

I agree that those queries better represent what human wanted and hence are more readable, but still they're not recommended on Firebird. I was badly bitten in 1990-s when doing Herbalife-like app, I chosen this type of request wrapped in a loop to do monthly bottom-up tallying - update ... where not exists ... - and every iteration scaled as o(n^2) in Interbase 5.5. Granted, Firebird 3 made a long way since then, but this "direct" approach is still not recommended.

More SQL-traditional and FB-friendly way to express it, albeit less direct and harder to read, would be Select t1.x from t1 LEFT JOIN t2 on t1.a=t2.b WHERE t2.y IS NULL

Arioch 'The
  • 15,799
  • 35
  • 62
  • Arioch 'The, on the first attempt you sql fetches in 0.42sec, Jerry Dodge's query contrary, in 23sec. After that, on the second, third attempt both fetches in equal time (0.22-0.23-0.24sec). – basti Dec 12 '18 at 10:41
  • 1
    basti - perhaps because the data was cached into memory. Tools like IBExpert show statistics like Reads from disk and Reads from cache. Make tests on the table containing 10K rows, 100K rows, 1M rows, 10M rows and see if there is behavior dependency. Also - there is a difference between "fetches first row" and "fetches last row = all rows" and I don't know what exactly u tested. – Arioch 'The Dec 12 '18 at 13:40
  • Arioch 'The, I useFirebird Maestro. There are only 180 records in DB table :). I write sql-query text in sql-editor and it writes: "180 rows fetched (0.24 sec)". Excuse me, may you tell me how can I clean cache in IBExpert or Firebird maestro in order to read from disc, not from cache? – basti Dec 12 '18 at 14:06
  • disconnect from the database and restart Firebird Server windows service :-) – Arioch 'The Dec 12 '18 at 14:07
  • Arioch 'The, after restarting service fetch time are equal (0.19-0.20sec). I will try in case of many records. Thank you. – basti Dec 12 '18 at 14:25
  • you sure you restarted exactly the service that handles this specific database ? the database is not on some remote server, is not governed by another firebird instance on your machine, etc? – Arioch 'The Dec 12 '18 at 15:37
  • 1
    Also read query plans: https://www.firebirdsql.org/en/news/understanding-the-firebird-optimizer-and-it-s-plans/ – Arioch 'The Dec 12 '18 at 18:23
1

Your query needs to work something like:

select * from CHARTACC where ACCNTNUM not in (select ACCPARNT from CHARTACC)

To put it into terms, select items from this table where its identifier is not found in the same table anywhere in its parent field.

Jerry Dodge
  • 26,858
  • 31
  • 155
  • 327
  • `not in` typically works quite slow in IB/FB, no indices used, etc. More SQL-traditional and FB-friendly approach would be `Select t1.x from t1 LEFT JOIN t2 on t1.a=t2.b WHERE t2.y IS NULL` – Arioch 'The Dec 12 '18 at 08:05
  • 1
    You should include `where ACCPARNT is not null` in the sub-select –  Dec 12 '18 at 08:43