Optimizing form's RecordSource

[ArviLaanemets]

Hi!



I'm currently working on app(s) with Access FE linked to SQL Server BE. SQL Server has scheduled Job's which will read read data from various ERP systems of various sites, converts read data to unified structure, and stores converted data to tables in SQL database. In Access FE, users will register various requests and articles/products requested. And FE must display various information about those articles/products from SQL database.

I had performance problems with one of forms, which displays a list of components for all articles/products registered for request. When I did give out a version of app for testing, they registered some 25000 articles for single request, and as result this form updated about 10 minutes, and then for half of cases the app crashed. So I have now spent some time to redesigning the database to avoid something like this happening again. I splitted previous SQL database to databases per site (as FE will work with single site data anyway), and stored as much as possible of data previously calculated in FE form RecordSource queries into BE tables (as those will be updated on daily shedule out of worktime, and the time spent for calculating this info there is minuscle compared with rest of Job time). But maybe someone has additional ideas to make this form more efficient. Until now I have made apps having massive amount of data only for reporting.

The form has RecordSource like

SELECT DISTINCT
req.SiteCode, req.ReqID, cmp.CompArtNo, cmp.CompArtTyp, cmp.CompArtName, cmp.CompArtCode, cmp.CompArtCategory, cmp.CompArtAccount, 
cmp.CompArtAccAlias, cmp.CompArtUnit, cmp.CompArtOrign, cmp.CompArtEU, cmp.CompCurrSupplCode, cmp.CompCurrSupplAlias, 
cmp.CompCurrSupplAccGroup, cmp.CompCurrSupplArtNo, cmp.CompCurrProducer, cmp.CompArtStatus
FROM (tRequests AS req LEFT JOIN tReqProducts AS prod ON req.ReqID = prod.ReqID) LEFT JOIN tProdComponents AS cmp ON prod.ProdArtNo = cmp.ProdArtNo
WHERE (((cmp.CompArtNo) Is Not Null));

Data entry, additions, deletions, etc. for form are disabled. The form is continuous subform of requests form. The same component may belong to articles/products sold to several customers registered in request, but the unique list of components per request is needed - so SELECT DISTINCT is used.

And now my questions:
1. Not all of fields returned have controls in form. Do those fields affect perfomance?
2. Some of returned fields have comboboxes as controls in form, and read info from other tables (e.g. combobox cbbCompArtStatus is linked to field CompArtStatus, but dispalys the according value from tArtStatuses.ArtStatusText). Is this the efficient, or is there a reason to add field CompArtStatusText to table tProdComponents, and instead of combo use a text box linked to this field?
3. What is better way to get unique list of components - using SELECT DISTINCT, or using an aggregate query (e.g. counting customers for component) instead?
4. I mentioned before, only some fields in form's RecordSource are linked to controls. Unitl now I have an additional single form beside this one, where additional fields (which were left out) for active component are displayed, but this takes a lot of room (and affects apps performance). I have considered some different solutions, but I have no experience with them. So is there any reason to prefer:
4a) A popup form displaying additional fields (or whole list of fields, as probably it will block at least part of original form), activated from button on form;
4b) A Message box displaying additional/all fields, activated from button on form;
4c) Maybe there is some way to display additional info when the cursor is hovering over some control in form, I'm not aware of (but probably it doesn't exist)?

[CJ_London]

not sure I can answer all the questions

1. Not all of fields returned have controls in form. Do those fields affect perfomance?

if they are not used then they are excess baggage to be brought across the network. Only bring what you need

2. Some of returned fields have comboboxes as controls in form, and read info from other tables (e.g. combobox cbbCompArtStatus is linked to field CompArtStatus, but dispalys the according value from tArtStatuses.ArtStatusText). Is this the efficient, or is there a reason to add field CompArtStatusText to table tProodComponents, and instead of combo use a text box linked to this field?

Depends where the rowsources are - local or on sql server. But if combo functionality is not required, bring the value through as part of the form recordsource

3. What is better way to get unique list of components - using SELECT DISTINCT, or using an aggregate query (e.g. counting customers for component) instead?

shouldn't make any difference - in theory DISTINCT should be quicker but tests indicate it appears not to be the case - suggest try it and see

3 - why do you want to display the unused fields? The 'effort' is bringing the values across. If you just want field names rather than values, use a separate query to compare metadata.

In all cases, indexing is important and for sql server, consider using a passthrough query or stored procedure so processing is done on the server.

If this is returning a large recordset for the user to then filter to find the record or records they want, modify the app to find out what they want to see first - then go get the data. For example, include a criteria for site

[ArviLaanemets]

Depends where the rowsources are - local or on sql server.

Tables are on SQL Server. And all are indexed. But yes, I'll add fields for such linked values to SQL table.

3 - why do you want to display the unused fields?

Those fields are left out not because they aren't needed. I didn't want to make the contionous form horizontally scrollable, so I put only controls probably looked at in first order on form, but there is a possibility the user needs to see some additional info sometimes. But probably a message box where left-out fields for active row are read from Articles table directly, and displayed field-wise using Hard Return, would be a best way to go (this allows remove all not used fields from RecordSource, and the process is invoked only on need).

If this is returning a large recordset for the user to then filter to find the record or records they want, modify the app to find out what they want to see first - then go get the data. For example, include a criteria for site

This doesn't work here! User selects another request, or adds new product(s) to active request, and this form (and several other) must be updated immediately, and there were no limits determined earlier for data to update. And data connection to SQL Server is using File Connection - I searched for a way for use data filtering with it some time ago, and it looks like it is not possible. In connection file you determine the database, and when you create a link you select a table or view, and it's all.

[CJ_London]

So a user opens a 25k recordset - how do they scan all that information to find the record or records they want to update?

[ArviLaanemets]

So a user opens a 25k recordset - how do they scan all that information to find the record or records they want to update?

There is another form, where are displayed all products, components, customers and suppliers linked with request, and links to all documents linked to those products, components, customers and suppliers. Users can open those documents from there. And also find products, components, customers or suppliers without linked documentation, and decide how to continue with them (and register needed actions for request and update them when something is done). About updating the request itself, it is done based on incoming mail or something alike (honestly, I don't have any clue!).

[Minty]

Have you considered creating a view specifically for this form's data?
Doing all the joins etc. and bringing in the combobox descriptive values in one place on the server would generally be significantly more efficient.
Is the form editable?

[ArviLaanemets]

I feel like I'm getting like zomby with this project lately I had a lunch after my previous post and there I remembered, than when I started to redesign this app, one thing I planned to do was to replace any queries in form RecordSources with views from SQL Server. And then I started redesign forms, and I started with redesigning those queries there.

And when I returned from lunch, there was Minty's post

In estonian there is a saying "silmaklapid peas", as much as I know from time when horses powering mills got something put on their heads so they did see only into front (dictionaries give back 'blinkers', but I think this is some later meaning). I feel now like such horse

[Minty]

Sometime my insightfulness is amazing