Phil,
From the svn change log:
------------------------------------------------------------------------
r16924 | plongstaff | 2008-02-03 14:16:06 -0700 (Sun, 03 Feb 2008) | 8 lines
When opening a register, there are now 5 queries: 1) get tx guids for
all splits in the register 2) get all tx'es for those guids 3) get all
slots for those tx'es 4) get all splits for those tx'es 5) get all
slots for those splits. This makes register manipulation much faster.
However, more work may need to be done to ensure that we don't make the
sql line too long by splitting one or more of the queries.
Logging the queries sent to PostgreSQL shows:
Query 1:
SELECT DISTINCT t1.tx_guid FROM splits AS t1 WHERE
t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261'
(returns greater than 600 rows in my test case)
Query 2:
SELECT * FROM transactions WHERE guid IN
('00587c1825d286a198fbb90685cfb08b','0200d809a230434900026c62e6b30cdc',
... );
Query 3:
SELECT * FROM slots WHERE obj_guid IN
('a05751c70b43be0c0cd03ef4a789981a','b032cddd5bfd3095963b2b1c001fceca',
...);
Query 4:
SELECT * FROM splits WHERE tx_guid IN
('a05751c70b43be0c0cd03ef4a789981a','b032cddd5bfd3095963b2b1c001fceca',
...);
Query 5:
SELECT * FROM slots WHERE obj_guid IN
('0d4942388ef552026d66c861782bab1c','44f52793993b48d68d2435735090caa5',
...);
Phil, what do you think of the following queries?
Query A (combines Queries #1 & #2 above):
(I've tried this query successfully)
SELECT DISTINCT t2.*
FROM splits AS t1, transactions AS t2
WHERE t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261' AND t1.tx_guid
= t2.guid;
Query B (same as Query #3 above):
( I can't confirm this one due to my slots table being empty. I've
noted the cause of that in other postings.)
SELECT * FROM slots
WHERE obj_guid IN (SELECT DISTINCT t1.tx_guid FROM splits AS t1 WHERE
t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261');
Query C (same as Query #4 above)
SELECT * FROM splits
WHERE tx_guid IN (SELECT DISTINCT t1.tx_guid FROM splits AS t1 WHERE
t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261');
or rewritten as:
SELECT t2.*
FROM splits AS t1, splits AS t2
WHERE t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261' AND t1.tx_guid
= t2.tx_guid;
Query D (same as Query #5 above)
(Again with an empty slots table, I can't really try this one.)
SELECT t3.*
FROM splits AS t1, splits AS t2, slots AS t3
WHERE t1.account_guid='b0853faaca8fad4e60d2a3f6b0b70261' AND
t1.tx_guid = t2.tx_guid AND
t2.guid = t3.obj_guid;
I believe that this would address your concerns regarding the length of
the SQL query strings and save you the trouble of writing code to split
them into several queries. Plus, 5 queries are reduced to 4.
I notice that some joins and/or sub-queries are repeated in my
suggestions. This may provide the possibility of further reducing the
number of queries.
Mark
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
