Requesting for help in improving performance

[GrunleStan]

All suggestions readily accepted.

So background. I have a HR database that has to be hosted on a whole bunch of Sharepoint List.
Each branch staff & private staff information has to be in a separate table/List. We can't have branch looking into other branch. So to join all of them into 1 table I use a UNION.

SELECT * FROM tbl_Staff_Branch1

UNION ALL

SELECT * FROM tbl_Staff_Branch2

UNION ALL ......

SELECT * FROM tbl_Staff_Branch7

UNION ALL SELECT * FROM tbl_Staff_HQAdmin;

The staff grades/ designation/ additional appointments & etc is stored separately..
Doing this so we can keep track of historical data;
eg who was the Fire Warden in 2015 and such. So & so was promoted to Lab Assistant Lvl 2 on 6-Jul-2016.
So for the Subgrade ( pay grade ); designation, PAA & SAA fields, only the Latest of these are only picked out....
For these I use a MaxD aggregate which will filter only the latest record for each Staff.

SQL is as such...

SELECT tbl_Dsgn.*
FROM tbl_Dsgn
WHERE (((tbl_Dsgn.DA_DateAssign)=DMax("DA_DateAssign","tbl_Dsgn","DA_Staff_ID=" & "'" & [DA_Staff_ID] & "'")));

my current get-all-the-data-together query uses the following

SELECT qryAllStaff.*, qryAllSP.*, qryDsgnMaxD.*, qryPAAMaxD.*, qrySAAMaxD.*, qrySubGMaxD.*, [S_Salute] & ". " & [S_NAME] AS CombinedName
FROM ((((qryAllStaff LEFT JOIN qryDsgnMaxD ON qryAllStaff.S_NRIC = qryDsgnMaxD.DA_Staff_ID) LEFT JOIN qryPAAMaxD ON qryAllStaff.S_NRIC = qryPAAMaxD.PAA_Staff_ID) LEFT JOIN qrySAAMaxD ON qryAllStaff.S_NRIC = qrySAAMaxD.SAA_Staff_ID) LEFT JOIN qryAllSP ON qryAllStaff.S_NRIC = qryAllSP.SP_NRIC) LEFT JOIN qrySubGMaxD ON qryAllStaff.S_NRIC = qrySubGMaxD.subG_NRIC;

The Query works but is painfully slow. even when the data is directly on the same machines.
Is there anything I can do to speed up queries?

If this query was ran for a form, then user switched to another form, will this same query be run again ?

The Zip file is a jpg of the design view of the Main SQL query.

TIA everyone.

 

[plog]

1. Whats the purpose of this master combined view? You didn't really provide context for the big picture.

2. How close to real time data do you need? Could you turn this query into a Make Table, then just use that for a day/week/month? Or does this query need access to any data that was changed in the last minute?

3. Why one huge query? You're feeding a form from that? Certainly the form can't edit the data, so what's the form's purpose?

 

[GinaWhipp]

UNION queries are notoriously slow when pulling pulling anything but a subset of data. That said, having separate tables for the Staff Branches speaks to a denormalized database. I understand you can't have branches looking at each other database but couldn't you have used queries and put them all in one tables as that is what you are doing with a UNION query.

DMax() in a WHERE statement? Yep, that's another speed killer. Perhaps you create some sort of Function to use?

And that *get all* query speaks to something has gone wrong. You are separating data only to bring it back together? If you must might be quicker to run some Make Table queries to stage the data and then run your *get all* query.

 

[The_Doc_Man]

Here is my thought.

You could do this with a slightly different design, I think. One big table with everyone in it, but the table's records are qualified by the group (department) for which that record applies. Your users should not see the raw database.

Instead, they would have to see only forms and reports opened via what we sometimes call a "switchboard" or "dispatcher" form. Where you open the app and a master control panel opens up. If you want to see things, you click a button that launches a form that filters on this department code.

The Form_Open routine for the master control can look up who they are in your domain environment using Environ("Username") though other methods exist as well. But this is the easiest one. Then in a table of users, you can have the code that shows which department this user can see and drive all forms and reports from it.

You NEVER let the switchboard form close unless you are in the process of closing the app. Otherwise, the switchboard keeps the details "behind the scenes" from ever being visible. You can look up the "switchboard" form (or "dispatcher" form) in this forum using our Search facility. It's a fairly common topic.

Here's the payoff. IF you do this to unify the tables, you can make a single index that will make data retrieval more efficient, since Access honors indexes whenever possible. Of course, you would put an index on the field that identifies the department, plus whatever other indexes you need for efficient lookups. That should help performance.

 

[NauticalGent]

Have you considered combining all the individual lists into one (as others have suggested) and then using Views?

I could explain it but it would be better to simply read the same article I did:
https://accessexperts.com/blog/2011/07/07/sharepoint-lists-and-microsoftaccess/

With this method, the server does the filtering and the speed is incredible! The coding can be a little tricky at first but once you “get” it, it goes pretty smooth.

Are the users able to get to the lists directly or do channel them with your application?

Also, I would make every effort to move away from SP. Once my organization “upgraded” from 2010 to 2013 and Win10, Access and SP would not work together any more. The issue was that the lists would not stay linked even though you could see them in the Navigation Pane, hover over the table and see the connection string and everything. But when you tried to open the table/list by double clicking, VBA or whatever, I got the “Access cannot find the (blah,blah,blah).

My IT opened up a premier ticket with MS and all we got for our efforts was “it is a known problem”. Well no kidding!!?

Long story short, we now use SQL Server (which, if you have SP you probably have too) and life has been a slice ever since.

However you go, best of luck to you. You have some the best AWF has to offer engaged with your issue so success is all but assured...

 

[GrunleStan]

1. Whats the purpose of this master combined view? You didn't really provide context for the big picture.

Basically for reports. If I needed a report with everything, this would be where I look for the information. All sorts of HR reports that span the Organisation.
And also as a form a really easy and not really "quick" access to the organisation wide data.

2. How close to real time data do you need? Could you turn this query into a Make Table, then just use that for a day/week/month? Or does this query need access to any data that was changed in the last minute?

Now a Make Table would be a nice idea !

 

[GrunleStan]

DMax() in a WHERE statement? Yep, that's another speed killer. Perhaps you create some sort of Function to use?

a function ? Sorry.. I've been hitting my head against the wall too much with this problem.. could you suggest some sort of psuedo code for this function?
or a different way to get teh latest date from table ?

And that *get all* query speaks to something has gone wrong. You are separating data only to bring it back together? If you must might be quicker to run some Make Table queries to stage the data and then run your *get all* query.

AS i use this for reports I could perhaps have Branch based searches or grab all data depending only on branches...

Thank you for the food for thought

 

[GinaWhipp]

Hmm, missed your reply!

I could suggest one if I knew exactly what you needed to pull. You have only provided bits and pieces.