Query speeds using Access front with SQL backend

Question

I have been working on converting an Access database over to MS SQL. For my initial testing I have imported the back end data onto my system with SQL Server Express 2014. So far I have been able to get everything to work with my Access front end except for one query.

Setting the primary keys on the tables in question has helped some but not fully. When I run the query in Access it will take about 10 seconds to run, but when I run it from a second computer it will take upwards to 30 seconds to run. However if I run the query directly Server Management Studio it runs within a second.

I'm not sure if the slow down is due to the fact I'm running SQL off of my laptop, because it's SQL Server Express, or a combination of the two. I'm hoping someone can provide some more information for me.

Here is a copy of the query:

SELECT tbl_defects.*,
       tbl_parts.part_type,
       tbl_parts.number,
       tbl_parts.mold,
       tbl_parts.date_created,
       tbl_parts.blade,
       tbl_parts.product,
       tbl_defects.defects_id
FROM   tbl_parts
       RIGHT JOIN (tbl_dispositions
                   RIGHT JOIN tbl_defects
                           ON tbl_dispositions.dispositions_id =
                              tbl_defects.disposition)
               ON tbl_parts.parts_id = tbl_defects.part
ORDER  BY tbl_defects.defects_id DESC; 

On the tbl_Defects the primary key is Defects_ID and it is set to index. On tbl_disposition the primary key is Disposition_ID and it is set to index. The third table tbl_parts the primary key is Parts_ID and it is set to index as well.

If I switch any of the Right Joins to be Inner Joins the query will run properly but it will be missing about 2000 records.

Answer 1

It has been my experience too that some queries with multiple LEFT (or RIGHT) JOINs perform badly when run in Access on linked tables.

I suggest creating a Pass-Through query for this, if possible (if you don't need to edit the result set). This will run directly on the server, as it would in SSMS.

Or you could create a view on SQL Server and link that.