Need help optimizing my query after SQL Server migration (2 Viewers)

[renQ]

Hello I recently migrated my access backend to SQL Server. However I have ran into an issue when it comes to one of my queries. It has multiple joins and is supposed to be editable. This is what it looks like. The way the users interacted with this before was by using the find tool and looking for one of the IDs. However that is extremely slow when run on this table which has around 80,000 rows. How do I best optimize this?

SELECT L.[Job IDFK], L.[Lot Number], L.[Lot ID], L.[Scheduled Production Date], L.[Shop ETA], L.[Date Production Completed], L.[Actual Delivery Date], J.[Job Name], CT.[Construction Type]
FROM ((Lots AS L LEFT JOIN [Lot Order Details] AS LOD ON L.[Lot ID] = LOD.[Lot ID]) LEFT JOIN [Construction Type] AS CT ON LOD.[Construction ID] = CT.[Construction ID]) LEFT JOIN Jobs AS J ON L.[Job IDFK] = J.[Job ID];

 

[theDBguy]

Hi. Welcome to AWF!

Not sure if this will work, but you might try creating a View from your query and use a link to that view for your form.

 

[DHookom]

I was going to suggest something similar to a SQL server view however create a pass-through query in Access and use a double-click or similar to open a form for editing.

 

[arnelgp]

have you added Index on those joining fields?