More than 1 inner join slows query too much (1 Viewer)

smbrr

Registered User.
Local time
Today, 08:33
Joined
Jun 12, 2014
Messages
61
Hello,

I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.

To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.

Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.

If I run the query:
Code:
SELECT VI.*
FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;

It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.

Now if I run:
Code:
SELECT VI.*
FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku) 
INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.

I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.

Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.

Please advise, how am I supposed to do this? If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?

Thank you
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:33
Joined
Feb 19, 2013
Messages
16,555
if your queries use domain functions or subqueries it will slow it up - will be the same for any db. Try changing your queries to maketables and run off the results there.

If you are using domain functions, change to subqueries and if you are using subqueries see if you can redesign to avoid using them - i.e. bring through the relevant table and set criteria.

If you are using union queries - again, find another way

I know you said fields are indexed, just double check all are indexed - including fields used in subqueries. Note also that criteria Like '*abc' (i.e. an initial '*') does not use indexing.

If your 4 queries have common criteria, consider combining them

Finally is the final query a 'full report' to be exported or whatever or is it something that is referenced - if the latter, build in the referencing criteria and see how that performs
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:33
Joined
Jan 23, 2006
Messages
15,364
smbrr,
Suggest you post a copy of the database for review.
As CJ said extensive use of Domain Functions, poor indexing, poor design... are typical culprits for speed/performance issues.
 
Last edited:

Strapuk

New member
Local time
Today, 15:33
Joined
Jul 29, 2015
Messages
4
I have a similar problem, although possibly unrelated.

I have 2 subforms on a main form, that show linked data based on PlayerID. It runs fine - well at least it did until I compacted the db. Since then, the form takes ages to load (and this can be up to a minute), and every time I select a different PlayerID, the form record source query takes the same time.

Luckily I am paranoid about data loss, so I always make backups of the db befoer any compaction. When I go back to use the backup version, full speed returns!

The query behind the form is too complex to list here, but it does have 3 left joins bewteen a table and another query, (which itself is based on a query of 3 tables and a query - which itself is based on 4 other quers having 8 left joins - if you see what I mean!).

I'm guessing the complexity of the joins is causing the porblem, but I cannot fathom out why speed is not an issue before compaction. Somethign to do with Indexing maybe?

Any thoughts greatly appreciated!
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:33
Joined
Jan 23, 2006
Messages
15,364
You can zip your database and attach to post (even with <10 posts).
 

Strapuk

New member
Local time
Today, 15:33
Joined
Jul 29, 2015
Messages
4
Thanks jdraw, but it's so complicated it would be difficult to describe ! I will try to just export those elements that go to make up the question and send that in.

THanks for the advice.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:33
Joined
May 7, 2009
Messages
19,175
How about if you change your approached and not use Inner join, ie.

Select VI.* From VI Where (VI.Sku In (Select Sku From SKUS)) And
(VI.SOff In (Select Soff From SOFF)) And ... etc.
 

smbrr

Registered User.
Local time
Today, 08:33
Joined
Jun 12, 2014
Messages
61
CJ_London, I'm not using anything special, nor UNION queries (which would be very useful to me but I had to drop early because the results are well over 2gb).
The maketable solution works, the 1st query runs ok, makes a table, and the next queries are instant since the table is only a few thousand records.

I'll mark this as resolve, but it's not a viable solution, you can't just have to make and drop tables at each and every step of all your queries. I have reports that join and join and join again between tables and each others, and I'll end up with an insane ammount of maintenance to do.

You said:
"If your 4 queries have common criteria, consider combining them"

I don't understand what you mean, but no they don't have common criteria. There's no criteria at all, it's just a matter of "i want only the records that are in all these tables".

"Finally is the final query a 'full report' to be exported or whatever or is it something that is referenced - if the latter, build in the referencing criteria and see how that performs"

The query I was talking about is the full result in itself, I paste the result in an Excel file, and then for each row I have a bunch of other reports to run. I'd have liked to just make a big query that does all that with joins, but Access just can't deal with all this data.

About posting the database, I can't, because of 1/company secret stuff, 2/it's really complex and with a boatload of vba code and I doubt anyone has the patience to understand it and 3/ everything is in french :D

arnelgp, I will try this on my other queries, but that's joins were invented for, is it not? Could it be better?

Thank you
 

Users who are viewing this thread

Top Bottom