what is the table 'slots' good for

[Klaus Dahlke]

Hi John,
in meanwhile, I deleted the entries in the slots table for the Bayesian filters. Here are now some results on the database activity by checking the entries into the log of the postgresql database:

After starting gnucash:
# grep -i select *.csv | grep -c gnucash
91
# grep -i delete *.csv | grep -c gnucash
0
# grep -i insert *.csv | grep -c gnucash
2

After having fetched data via HBCI from bank, 20 new records, 1 old record:
# grep -i select *.csv | grep -c gnucash
96
# grep -i insert *.csv | grep -c gnucash
15
# grep -i delete *.csv | grep -c gnucash
4

After manually assign accounts to the 20 new records:
# grep -i select *.csv | grep -c gnucash
1932
# grep -i insert *.csv | grep -c gnucash
4470
# grep -i delete *.csv | grep -c gnucash
1820

After pressing 'okay' to insert into database:
# grep -i select *.csv | grep -c gnucash
2128
# grep -i insert *.csv | grep -c gnucash
4935
# grep -i delete *.csv | grep -c gnucash
1998

The table slots is where the action is going on:
# grep -i select *.csv | grep -c -i slots
2059
# grep -i insert *.csv | grep -c -i slots
4873
# grep -i delete *.csv | grep -c -i slots
1995

whereby exactly the 20 new transactions are inserted:
# grep -i insert *.csv | grep -c -i transaction
20
# grep -i insert *.csv | grep -c -i splits
40

After reconciling the account before submitting the data to the database
# grep -i select *.csv | grep -c gnucash
2128
# grep -i insert *.csv | grep -c gnucash
4935
# grep -i delete *.csv | grep -c gnucash
1998
# grep -i update *.csv | grep -c gnucash
23

After submitting the reconciliation to the database:
# grep -i select *.csv | grep -c gnucash
2532
# grep -i insert *.csv | grep -c gnucash
5745
# grep -i delete *.csv | grep -c gnucash
2400
# grep -i update *.csv | grep -c gnucash
65

Again, heavy traffic on the slots table:
# grep -i select *.csv | grep -c -i slots
2461
# grep -i insert *.csv | grep -c -i slots
5683
# grep -i delete *.csv | grep -c -i slots
2397
# grep -i update *.csv | grep -c -i slots
0

So, reconciling 20 records for a single account causes ~400 select, ~800 insert and ~400 delete statements on the slot table.

Is there a change for on overhaul of the concept of using the slot table for one of the next releases?

Best regards,
Klaus


On Tue, 27 Nov 2012 22:06:27 +0100
Klaus Dahlke <klaus.dahlke at gmx.de> wrote:

> On Sat, 24 Nov 2012 08:36:12 +0900
> John Ralls <jralls at ceridwen.us> wrote:
> 
> > 
> > On Nov 24, 2012, at 6:43 AM, Klaus Dahlke <klaus.dahlke at gmx.de> wrote:
> > 
> > > Dear all,
> > > I use gnucash since many years with the postgresql backend. No inconsistencies or similar so far. The only problem I have that I experience some performance issues, especially one retrieveing data via HBCI or reconciling accounts.
> > > 
> > > To investigate a bit further I increased the log level of postgresql and found that during retrieval and reconciling that the 'slots' table is involved, e.g. some delete and insert statements with fairly old data ...
> > > 
> > > Actually, I don't reconcile all my accounts, typically only reconcile the bank and credit/debit cards accounts.
> > > 
> > > The slots table has now 18250 lines:
> > > gnucash=# select count(slot_type) from slots;
> > > count 
> > > -------
> > > 18250
> > > (1 row)
> > > 
> > > 
> > > gnucash=# select slot_type, count(slot_type) from slots group by slot_type;
> > > slot_type | count 
> > > -----------+-------
> > >         6 |     8
> > >         4 |  3292
> > >         5 |  3144
> > >         9 |  4827
> > >         1 |  5876
> > >        10 |  1103
> > > (6 rows)
> > > 
> > > Could give some information what's the purpose of the slots table and whether the 'old' entries can be deleted?
> > 
> > The Slots table holds key-value pair data (KVP)  from data structures throughout Gnucash. It's been the (unfortunate) practice through most of Gnucash 2's development to use KVP to add or extend new variables to Gnucash because doing so allows older versions of Gnucash to open the data file.
> > 
> > Since you mentioned HCBI, it stores its Bayesian matching data in KVP, and that data can get quite large over time. It's hard to see what entries in the Slots table correspond to the Bayesian data, but quite easy from XML. If you want to delete it -- and that would mean that you'd have to re-train all of your import account assignments -- you can save your data as XML using File>Save As and edit the result. You'll want to turn off compression in preferences first. The HBCI elements are clearly named and are under the <Book> element; they should be near the top of the file after all of the <currency> elements. 
> > 
> > Do note that editing the Gnucash data file voids the warranty, so be very careful, and check the results carefully in Gnucash before re-saving it to Postgres and using it for new transactions.
> > 
> > Regards,
> > John Ralls
> 
> 
> Hi John,
> thanks for the info. It seems that also within postgresql the entires in questions are easy to identify. The entries in the field 'name' of the slots table start with 'import-map/' (typically HCBI) or with 'import-map-bayes' (Bayesian filter). I will give it a try on a test database later.
> 
> Cheers,
> Klaus