Microsoft Access Front-end with SQL Server 2017 Back-end
Question
Working on improving performance in an Access application. Some forms have multiple sub-forms and they load very slowly - often times bringing the entire server to a screeching halt. Before I start rewriting this thing I thought I'd ask for some advice.
One sub-form record-source is a linked SQL UNION QUERY - Does the Recordset Type (Dynaset, Snapshot, etc) in Access matter --- as far as performance goes? None of the data is editable.
This query returns around 6300 records in the list and IS SLOW AS MOLASSES to load.
Having it be a UNION query is by design because the purpose of the form is to allow supervisors to view work loads across a sales floor for multiple types of Lead Records -- which are presently in two different tables (and in an ideal world would not be in separate tables -- but to accomplish that would require a complete rewrite and I don't have time to do it right now).
I could allow them to toggle between the two tables but they wouldn't get an overall picture easily --- hence the way it's designed now...
I'm going to add a couple indexes tonight to see if that helps -- just thought I'd reach out for some Expert Advise.
Anyway -- any suggestions would be appreciated. Thanks y'all!
Working on improving performance in an Access application. Some forms have multiple sub-forms and they load very slowly - often times bringing the entire server to a screeching halt. Before I start rewriting this thing I thought I'd ask for some advice.
One sub-form record-source is a linked SQL UNION QUERY - Does the Recordset Type (Dynaset, Snapshot, etc) in Access matter --- as far as performance goes? None of the data is editable.
This query returns around 6300 records in the list and IS SLOW AS MOLASSES to load.
Having it be a UNION query is by design because the purpose of the form is to allow supervisors to view work loads across a sales floor for multiple types of Lead Records -- which are presently in two different tables (and in an ideal world would not be in separate tables -- but to accomplish that would require a complete rewrite and I don't have time to do it right now).
I could allow them to toggle between the two tables but they wouldn't get an overall picture easily --- hence the way it's designed now...
I'm going to add a couple indexes tonight to see if that helps -- just thought I'd reach out for some Expert Advise.
Anyway -- any suggestions would be appreciated. Thanks y'all!
ASKER
So the source is a SQL View now. It is set as a dynaset recordset type.
I solved this problem not long ago
I posted on my blog the code that execute before the form load and use a special recordset (why special? because it have 5 properties to setup)
The code is VBA and a little dirty (lots of lines not used)
http://wildboy85.blogspot.com/2019/07/ms-access-mdb-to-ms-sql-express.html
In your case you will have to load a recordset for each form
Insert the code in the VBA section for each form
the sub have to be called:
form_open (as seen in blog)
It is a little pain to change each form, but once it is done, the speed is instant.
I posted on my blog the code that execute before the form load and use a special recordset (why special? because it have 5 properties to setup)
The code is VBA and a little dirty (lots of lines not used)
http://wildboy85.blogspot.com/2019/07/ms-access-mdb-to-ms-sql-express.html
In your case you will have to load a recordset for each form
Insert the code in the VBA section for each form
the sub have to be called:
form_open (as seen in blog)
It is a little pain to change each form, but once it is done, the speed is instant.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<Working on improving performance in an Access application. Some forms have multiple sub-forms and they load very slowly - often times bringing the entire server to a screeching halt.>>
The other thing here is to not load the subforms if they are not visible. Often subforms are put on a tab control and one way to improve performance is to not set the sourceobject on the subform control until they click on the tab. That's shown here:
https://www.fmsinc.com/microsoftaccess/Performance/Forms/LateBinding.asp
As an alternative, you can control the subforms rowsource. Default it to "Select * FROM myTable Where 1=0" and it will load with no records (or set the .DataEntry Property to True). Then when they click on the tab, set the rowsource and set it to what it should be.
There's a few other ways to go at this as well, but basically it boils down to not using the subform until they click on a tab.
Jim.
The other thing here is to not load the subforms if they are not visible. Often subforms are put on a tab control and one way to improve performance is to not set the sourceobject on the subform control until they click on the tab. That's shown here:
https://www.fmsinc.com/microsoftaccess/Performance/Forms/LateBinding.asp
As an alternative, you can control the subforms rowsource. Default it to "Select * FROM myTable Where 1=0" and it will load with no records (or set the .DataEntry Property to True). Then when they click on the tab, set the rowsource and set it to what it should be.
There's a few other ways to go at this as well, but basically it boils down to not using the subform until they click on a tab.
Jim.
ASKER
Thanks Experts!
Yes. Depending on the size of the table, snapshot is probably not a great choice. By saying "snapshot", your telling the database engine to make a copy of every record. That's what a snapshot is; give me the data as of a point in time.
<<Anyway -- any suggestions would be appreciated. Thanks y'all!>>
You need to start harnessing the power of SQL Server, either by using a pass-through query, using a view, or a stored procedure that returns records.
With all of these, the operation is performed server side. By working with linked tables, most of the time you will be pulling the raw data over and then JET will manipulate it locally.
Jim.