Tips on increasing query speed (SQL) ?

[TerraEarth]

I'm having an issue where my queries are horrendously slow (1 minute + compilation time) when my database only has ~ 10k records with 15 or so columns.



My style of usage is that I take an atomic approach: I take a complicated query that I need, find all the component, atomic parts and great them up into their own mini queries in Access. I then run the complicated query using the established mini queries.

I've noticed that NOT IN especially is slow. I am frequently using key words such as GROUP BY, HAVING, MIN(), SUM(), NOT IN, IN, WHERE, etc. for more complex queries.

Usually I wouldn't care if a query took 2-3 minutes to compile but these queries will be used for important features in the database so I feel that the run-time needs to be worked on. Any tips?

[orange]

First step would be to show us :
-plain English description of what your query is expected to do,
-the SQL of the query involved, and
-the design of tables involved showing keys/indexes.

Good luck with your project.

[TerraEarth]

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

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:

    document_num | min(fy) | sum(amt1) | sum(amt2) | sum(amt3)

Here are the queries I am using:


qry_cat_x_account:

SELECT doc_num, year, FROM tbl_document WHERE (type = "cat_x");

qry_closed_document:

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:

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:

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:

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)));

[ssanfu]

Be aware that "Year" and "Type" are reserved words in Access and shouldn't be used as object names.

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

"Year" is a built in function.
Plus, "Year" and "Type" are not very descriptive names..
"Year" of what???
"Type" of what??

Maybe use "fYear" or "finYear".
Maybe use "CategoryType" or "CatType"...



The Access Gnomes can sometimes get very obstinate/persnickety when reserved words are used for object names.


Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

[isladogs]

I have an extended article on optimising queries that you may find useful http://www.mendipdatasystems.co.uk/s...s-8/4594556613
This includes an example database that you can easily adapt for your own tests.
Hope that helps.