The query is built using a table documenting financial records.
There's an entry in the table for each year that the record is open, with amounts (amt1, amt2, amt3, ..., amt6) for each relevant financial field
the type column indicates what category the record falls under
So a sample might look like the following:
doc_num | year | amt1 | amt2 | ... | amt6 | type
doc1 | 2016 | $1 | $1 | ... | $2 | category_x
doc1 | 2017 | $1 | $1 | ... | $1 | category_x
doc2 | 2017 | $2 | $2 | ... | $4 | category_y
The table is as follows:
tbl_document
Code:
doc_num | year | amt1 | amt2 | ... | amtn6 | type
So my query is doing the following:
Filter out the following:
financial records with min(fy) <= 2016
records of a specific type (let's call it category_x); type <> category_x
closed records; amt1 = amt2 and amt2 = amt3 and ... amtn-1 = amtn
I need to generate the following query for records that aren't filtered:
Code:
document_num | min(fy) | sum(amt1) | sum(amt2) | sum(amt3)
Here are the queries I am using:
qry_cat_x_account:
Code:
SELECT doc_num, year, FROM tbl_document WHERE (type = "cat_x");
qry_closed_document:
Code:
SELECT doc_num, min(year) As min_year
FROM tbl_document AS A
GROUP BY doc_num
HAVING ((A.amt1 = A.amt2) AND (A.amt2 = A.amt3) AND (A.amt3 = A.amt4) AND (A.amt4 = A.amt5) AND (A.amt5=A.amt6));
qry_ineligible_document:
Code:
SELECT doc_num, min(year) AS min_year FROM (select doc_num, fy FROm qry_cat_x_account UNION select grant_doc_nr, fy FROM qry_closed_document) GROUP BY doc_num;
qry_compiled_eligible_document:
Code:
SELECT doc_num, sum(amt1) As sum_amt1, sum(amt2) As sum_amt2, sum(amt3) As sum_amt3, min(year) As min_year FROM tbl_document
WHERE doc_num NOT IN (select doc_num FROM quni_ineligible_document)
GROUP BY doc_num
HAVING min(year)>=16;
EDIT: after doing some research on indexes, I decided to implemented a composite unique index on doc_num, year as well as a singular index on both year and on type. The speed is still very slow. The individual queries are all instantaneous, but qry_compiled_eligible_document that utilizes them all is horrendously slow. I'm starting to think that utilizing these pre-built queries do not benefit from indexing after they are initially built.
EDIT2: After fiddling around for a while, I rewrote the entire query as follows and it's lightning fast now, all I did was rewrite it such that it no longer depended on pre-built queries:
Code:
SELECT t.doc_num, sum(t.amt1) As sum_amt1, sum(t.amt2) As sum_amt2, sum(t.amt3) As sum_amt3, min(t.year) As min_year FROM tbl_document As t
GROUP BY t.doc_num
HAVING min(t.year)>=16 AND NOT
((sum(t.amt1) = sum(t.amt2)) AND (sum(t.amt2) = sum(t.amt3)) AND (sum(t.amt3) = sum(t.amt4)) AND (sum(t.amt4) = sum(t.amt5)) AND (sum(t.amt5)=sum(t.amt6)));