Speed tremendously affected by JOIN

[HansDS]

I have a query that uses several LEFT JOINs to gather all its data from various lookup tables. However, oneof the criteria fed into that query is performing extremely poorly.

Setup:
[Products] with ProductID as PK, has 50000 records
[ProdSuppls] with ProductID+SupplierID as PK, has 80000 records
[Suppliers] with SupplierID as PK, has 1000 records

The query:
SELECT [Products].ProductID FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN [Suppliers] ON [ProdSuppls].SupplierID = [Suppliers].SupplierID) ON [Products].ProductID = [ProdSuppls].ProductID WHERE [Suppliers].Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"

When I run this query it takes about 3 secs. to return 7 records with "Amadéus" as the Supplier name.
Obviously I thought the query criteria was the culprit. However, when I run the query SELECT * FROM [Suppliers] WHERE Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"
directly against the [Suppliers] table, a single record ("Amadéus") is returned instantaneously.

It looks to me like Access is not properly optimizing the execution plan for the query, but I have no way of telling.
Can anyone shed some light on this and maybe recommend a better way?

 

[MarkK]

Are the two fields SupplierID and ProductID in the ProdSuppls table indexed?

 

[HansDS]

Yes, both of those fields are indexed. I also just compacted the database in order to eliminate slowness from that side.
Interestingly enough, when changing both JOIN types to INNER JOIN it was lightning fast. However, the fact that I got the same 7 records as a result is just a coincidence. It does point in the direction of the JOINS and how they're executed, and not in the direction of the criteria.

 

[jdraw]

Since your ProductID is located in ProdSuppls, can you drop the use of Products
and rewrite the query as below? Not sure what the effect on speed will be..

SELECT [ProdSuppls].ProductID 
FROM ProdSuppls, Suppliers
WHERE ((([ProdSuppls].[SupplierID])=[Suppliers].[SupplierID]) 
AND (([Suppliers].[Name]) Like "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"));

 

[HansDS]

In fact I simplified the query a little too much to make my point, because I also need data from the ProdSuppls table.
SELECT [Products].ProductID, [ProdSuppls].SupplierID, [Suppliers].Name FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN [Suppliers] ON [ProdSuppls].SupplierID = [Suppliers].SupplierID) ON [Products].ProductID = [ProdSuppls].ProductID WHERE [Suppliers].Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"

This query returns products and their various distributors.

Any thought on speeding this one up?

 

[Simon_MT]

I'm not sure you use this specific criteria but it seems the same each time. Here are some alternatives but if performance is the key issue here some options:

Categorise your Suppliers according to the above, if there is only one catagory you are looking for it could be a simple Yes/No scenario, if it is more than one then a identifier will be required. I will take the first scenario for sake of simplicity.

Add a Field to Suppliers Category Indexed for speed

Create a update query with your criteria:

[Suppliers].[Name]) Like "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"

Set Supplier Category = "True"

Your criteria is then replaced with Supplier Category = True

Because this is non normalised you would then have to maintain the Supplier Category when the Supplier Name is maintained or a Supplier is added. The whole idea is if your criteria is creating a bottle-neck code around it.

Another alternative is to create a separate query that only includes Suppliers with your criteria and use this query whenever these suppliers are required rather than the table. The only drawback is that if a different criteria is used this has not solved the problem!

Simon

 

[jdraw]

In fact I simplified the query a little too much to make my point, because I also need data from the ProdSuppls table.
SELECT [Products].ProductID, [ProdSuppls].SupplierID, [Suppliers].Name FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN [Suppliers] ON [ProdSuppls].SupplierID = [Suppliers].SupplierID) ON [Products].ProductID = [ProdSuppls].ProductID WHERE [Suppliers].Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"

This query returns products and their various distributors.

Any thought on speeding this one up?

Hans,
You do not need the Products table in this query. You are only using ProductID which is available in ProdSuppls table. Using the query I suggested will remove one of the JOINS.

However, if you have to do several queries like this I suggest a change in structure to Suppliers table.

The concept is to have a search field, such as SrchSupplierName. This field has no accented characters, and could be all Capitals. The field should be indexed.

To populate the field, create and run a query that replaces all of the
[aàáâãäåAÀÁÂÃÄÅ] with A; [eèéêëÈÉÊË with E and [uùúûüUÙÚÛÜ] with U . Then your search query becomes
.... where [Suppliers].SrchSupplierName LIKE "AMADEUS*"

I have used this sort of set up for large company databases. Takes longer to set up, but improves retrieval greatly.

 

[LPurvis]

Couple of questions:

You refer to a simpification, is this your actual query?

SELECT [Products].ProductID, [ProdSuppls].SupplierID, [Suppliers].Name 
FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN [Suppliers] 
    ON [ProdSuppls].SupplierID = [Suppliers].SupplierID) ON [Products].ProductID = [ProdSuppls].ProductID 
WHERE [Suppliers].Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*"

If so then you might as well switch to an INNER JOIN - you've negated all the OUTER JOINS by specifying criteria upon the outermost table.
Instead you'd want:

SELECT [Products].ProductID, [ProdSuppls].SupplierID, [S].Name 
FROM [Products] LEFT JOIN ([ProdSuppls] LEFT JOIN 
    (SELECT * 
     FROM [Suppliers] 
     WHERE [Suppliers].Name LIKE "[aàáâãäåAÀÁÂÃÄÅ]m[aàáâãäåAÀÁÂÃÄÅ]d[eèéêëÈÉÊË][uùúûüUÙÚÛÜ]s*") S 
    ON [ProdSuppls].SupplierID = [S].SupplierID) ON [Products].ProductID = [ProdSuppls].ProductID

Is there an index upon the SupplierID field in ProdSuppls?
(I don't just mean an explicit index - but if you've established an RI relationship between ProdSuppls and Suppliers?)
If not then consider adding one to just that column (I appreciate it's already part of the PK index).

 

[HansDS]

Replying to LPurvis...

Actually the criteria is very dynamic and can be composed of up to 10 criteria referencing fields in either one of the 3 tables (all of those fields are indexed). The query I presented is one of the simplest ones, using a single criteria.
Also, the SELECT list in reality returns about 50 fields from these 3 tables, all of which are either displayed in the UI or used in decision-making.
Business logic demands that the criteria be accent-insensitive and case-insensitive.
Continuing the above example, in the search user interface, the user entered "Amadeus" in the Supplier criteria field. the query should return records whose supplier name starts with "amadeus". "Amadeus", "Amadeus Music", "Amadéusco", etc.

If necessary, I can also post another query that demonstrates one of the more complex criteria a user can enter.

 

[LPurvis]

That's fair enough - though ultimately it doesn't change the advice I offered.
The criteria needs to be applied either in subqueries or accept that the outer joins will be rendered ineffective by any criteria applied?
And the index is still the same issue regardless of what's returned etc.

 

[HansDS]

I've been tinkering a lot with the JOIN options, but INNER JOIN won't return the correct results in all cases.
Given the query we started out with, indeed INNER JOIN would work just as well because the crtiteria is on the outermost table. However, suppose the user entered only a criteria for Products.Description LIKE "*concert*", then the INNER JOINs would cause all records with ProdSuppls.SupplierID=NULL to be excluded, which is not what we want. There could be numerous products without a supplier, and yet we still want to return those if we only search with a product description criteria.
This opens a whole new can of worms related to NULLS and their consequences on joins.

 

[LPurvis]

I'm not clear on what you're saying as a conclusion.
That's essentially what I'd pointed out as a caveat in my first post.
You just need to construct the query appropriately to allow for that.