Solved Need help optimizing my query after SQL Server migration

[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?
also adding a Relationship (Constraint) on those table fields will help speed up things.

 

[cheekybuddha]

Following up on Arnel's suggestion, it has been discussed here recently that, depending on what method you used to upsize your Access databasse to SQLServer, that not all indexes are properly recreated, so double-check they are present for all joined fields.

 

[Minty]

A SQL View will almost certainly be quicker than joining the tables in access.
You can then query the view locally and if you are using an up to date SQL Driver (Version 18 is the latest one and would be recommended) it should work at least as quickly as your old access query did.

In addition to the other advice, It's good practice to consider reducing the amount of records being returned.
If you generally only need the last 30 or 90 days worth of data, then only bring those in.

 

[cheekybuddha]

A SQL View will almost certainly be quicker than joining the tables in access.

However, it is still likely to be un-updateable if joins are made on non-indexed fields, no?

 

[Minty]

However, it is still likely to be un-updateable if joins are made on non-indexed fields, no?

Possibly, although SQL server is a lot more tolerant of somewhat ambiguous joins, and still often allows updating when Access wouldn't, in my experience.
To be fair all my tables have a unique key specified, so it's never really an issue.

 

[GPGeorge]

In the SQL provided, it appears that the joins are all on Primary and Foreign Key pairs. It's unlikely that any of those Primary or Foreign Keys are not indexed in Access, and the same should be true when they are Migrated. It's possible, I suppose, that Referential Integrity is not enforced, and that the names of the fields is not an accurate reflection of their relationships. We need to learn that from the OP.

 

[cheekybuddha]

and the same should be true when they are Migrated.

Wasn't there an issue with some indices not being migrated properly?

Pat mentioned something here

From the link:

One thing to watch is SSMA does not copy the hidden indexes Access creates on all FK fields so you might need to add them after the upsize.

 

[renQ]

Thanks guys for responding, just wanted to clear up some things. So thanks for the suggestions for views it works well for getting the initial query. My main issue is in the previous version of the access backend, when we would try to use the Find And Replace tool, it was significantly faster when searching the records, now after the adjustments it has sped up but still remains slow. I'm not sure if this is a query issue or something related to Access in specific. Thanks.

 

[Minty]

To do find and replace on a sizeable recordset pass the process to the Server, either using a stored procedure or a pass through query, depending on the complexity involved.

The difference in speed should be significant.
Assuming it a text field, make sure that it is also indexed.

 

[renQ]

To do find and replace on a sizeable recordset pass the process to the Server, either using a stored procedure or a pass through query, depending on the complexity involved.

The difference in speed should be significant.
Assuming it a text field, make sure that it is also indexed.

Sorry I mentioned find and replace to reference the tool box that pops up when you hit ctrl + f. I wouldn't want to replace any of the records just using that tool to locate specific IDs on the table. To reiterate in the previous version when I would use this tool it would be instant, now it seems to not be able to find the ID on some occasions within the table.

 

[Minty]

Sorry I mentioned find and replace to reference the tool box that pops up when you hit ctrl + f. I wouldn't want to replace any of the records just using that tool to locate specific IDs on the table. To reiterate in the previous version when I would use this tool it would be instant, now it seems to not be able to find the ID on some occasions within the table.

Ah - that's a different kettle of fish altogether...

You may need to build something that can replicate that functionality, It is certainly slower on non-access backends, so I suspect when it is an Access data-source it can employ some ninja smoke and mirrors to speed things up.

What happens if you perform the search using a SP to return the record ID, or using a record-set clone / bookmark to do a search, as I think that might work better.

 

[renQ]

I see that makes sense, I may end up going with the record-set clone/bookmark approach then. Just for additional things to think about. I have a passthrough query that runs the code from pre migration, and that seems to have no issues with the find tool. Would it be because the returned record set is not updateable? Or is there some other reason.

 

[GPGeorge]

Wasn't there an issue with some indices not being migrated properly?

Pat mentioned something here

From the link:

I think there's more to it. In fact, one of the upcoming User Group presentations--I think the new Australia chapter--is planning a discussion of that topic. There was some discussion of it in the after-the-presentation portion of our last meeting of Access Pacific.

The "missing indexes" turned out to be indexes on Foreign Key fields which Access, by default, creates when RI is enforced on a relationship. They are not automatically created by SSMA when migrating those tables. SQL Server does correctly impose the Foreign Key constraint when SSMA migrates the relate tables, though. The remaining question is whether there needs to be an additional index on that Foreign Key field in the related table in SQL Server.

I have a copy of Microsoft Jet Database Engine Programmer's Guide, copyrighted in 1995, which includes these paragraphs on page 81. (My random typos are gratis.)

On the topic of adding Foreign Key Constraints in SQL DDL:

"Another use of the CONSTRAINT clause is to create a relationship (or reference) from a foreign key of one table to the primary key of another table. With this constraint in place, it's impossible to enter a value of the foreign key of a table that doesn't already exist as the primary key of the master table.

In the following example, two indexes are created. The first is the primary key Index on TaskID, and the second is a foreign key index on the EmployeeID."

...[example omitted]

"In addition to the index, a new Relation object is created in the database.... , a database contains a Relations collection, which stores information about all the inter-table relationships defined for your database."

I understand this to mean that the so-called missing Foreign Key index, which is created in Access, is not replicated by SSMA, although it can be added if desired in SQL Server. The question remaining for me is whether that matters in the SQL Server database and whether that omission is proper or improper. I think this is by design in SSMA, and not an oversight. Why that would be the case, I don't know.

I think the index, required or not, does matter, of course, but the fact that SSMA omits them suggests there is, at the very least, an open question about it.

Pat initiated the conversation about these indexes and probably has more information as well.

 

[Minty]

I see that makes sense, I may end up going with the record-set clone/bookmark approach then. Just for additional things to think about. I have a passthrough query that runs the code from pre migration, and that seems to have no issues with the find tool. Would it be because the returned record set is not updateable? Or is there some other reason.

If you return a snapshot recordset of any multi table joined query (of any sizeable recordset) it is always significantly quicker in my experience.

It's for this reason (we use Azure hosted SQL Server) we frequently use a "display only" list version of the data then a single record edit form as previously suggested by @DHookom earlier in the thread. 90% of the time this is much easier to handle and removes any potential speed issues.

 

[renQ]

If you return a snapshot recordset of any multi table joined query (of any sizeable recordset) it is always significantly quicker in my experience.

It's for this reason (we use Azure hosted SQL Server) we frequently use a "display only" list version of the data then a single record edit form as previously suggested by @DHookom earlier in the thread. 90% of the time this is much easier to handle and removes any potential speed issues.

I was thinking this approach may be the fastest what would the implementation of this look like? Would I need to develop a vba script that opens another form with the data from the table. Is it possible to make this form appear over the table or would it have to be a separate tab?

 

[Minty]

The normal route would be a pop up single form that is opened modally/dialog on top the of the main list, and then returns you to where you where after is opened.
I have lots of them but they are are proprietary to the clients app, and difficult to take pictures of without munging a ton of the data.

I normally use a double click event on the Job_Number/Contract/Client_ID and pass it to the pop up with the OpenArgs property, then set the pop form to the appropriate single record.

Something like this:

 

[GPGeorge]

Spoiler:

I think there's more to it. In fact, one of the upcoming User Group presentations--I think the new Australia chapter--is planning a discussion of that topic. There was some discussion of it in the after-the-presentation portion of our last meeting of Access Pacific.

The "missing indexes" turned out to be indexes on Foreign Key fields which Access, by default, creates when RI is enforced on a relationship. They are not automatically created by SSMA when migrating those tables. SQL Server does correctly impose the Foreign Key constraint when SSMA migrates the relate tables, though. The remaining question is whether there needs to be an additional index on that Foreign Key field in the related table in SQL Server.

Spoiler:

I have a copy of Microsoft Jet Database Engine Programmer's Guide, copyrighted in 1995, which includes these paragraphs on page 81. (My random typos are gratis.)

On the topic of adding Foreign Key Constraints in SQL DDL:

"Another use of the CONSTRAINT clause is to create a relationship (or reference) from a foreign key of one table to the primary key of another table. With this constraint in place, it's impossible to enter a value of the foreign key of a table that doesn't already exist as the primary key of the master table.

In the following example, two indexes are created. The first is the primary key Index on TaskID, and the second is a foreign key index on the EmployeeID."

...[example omitted]

"In addition to the index, a new Relation object is created in the database.... , a database contains a Relations collection, which stores information about all the inter-table relationships defined for your database."

I understand this to mean that the so-called missing Foreign Key index, which is created in Access, is not replicated by SSMA, although it can be added if desired in SQL Server. The question remaining for me is whether that matters in the SQL Server database and whether that omission is proper or improper. I think this is by design in SSMA, and not an oversight. Why that would be the case, I don't know.

I think the index, required or not, does matter, of course, but the fact that SSMA omits them suggests there is, at the very least, an open question about it.

Pat initiated the conversation about these indexes and probably has more information as well.

Reference for the assertion that indexes are not automatically created, nor required, for Foreign Key fields in SQL Server.

Primary and foreign key constraints - SQL Server

Learn about primary and foreign key constraints, important objects used to enforce data integrity in database tables.

learn.microsoft.com

Note that there may be performance penalties for not creating an index on the field which is the Foreign Key even though it's not required for the Constraint.