Pass through query to table? (Suggestions)

[wrightyrx7]

Hi all,

I have a pass through query with multiple joins (SQL database). However the time the query takes to run is around 10 seconds.

There is a combobox and subform on my main form that uses the data from this query, so it takes a while to load the combobox data. Then when a selection is made it filters a subform which is using the same data so that too takes a while.

What i am thinking of doing is running the pass through query and putting the data in a table. Which should speed up the above process. And have a refresh button which deletes the table runs the query and creates a new table.

Do you think i am going the right way about this?

Thanks in advance
Chris

 

[Minty]

I wouldn't delete the temp table just empty it, as constantly deleting and recreating will cause database bloat if the table is in your front end.

What you suggest is a good way around having to constantly requery complex query results.

Have you considered making the pass through query a view on the sql server and linking that in to your front end and querying that?

 

[Galaxiom]

Slow performance is more often due to problems with query design or inadequate indexing on the table columns.

Post the SQL of the query and check that the columns used to select are indexed.

 

[wrightyrx7]

Thank you for both your replies its much appreciated.

Have you considered making the pass through query a view on the sql server and linking that in to your front end and querying that?

We do not have the privileges to create views on the SQL server. All that IT will give us is SELECT privileges

Slow performance is more often due to problems with query design or inadequate indexing on the table columns.

Post the SQL of the query and check that the columns used to select are indexed.

After a couple of test I noticed the code runs fine when I open it to a datasheet (double click the saved query). It is when i use it as a data source things slow down.

 

[Minty]

We do not have the privileges to create views on the SQL server. All that IT will give us is SELECT privileges

It might be worth pointing out to IT that this would possibly reduce network traffic and server load on the SQL Server. And keep users happy that they haven't got dog slow PC's and laptops that all need upgrading

 

[Galaxiom]

After a couple of test I noticed the code runs fine when I open it to a datasheet (double click the saved query). It is when i use it as a data source things slow down.

Access will normally begin displaying the data as soon as the first page is available. However if the results are ordered then it must wait until all records are returned before it can display the top results.

The form or report may have a sort applied and this will have a similar effect as Order By added to the query.

Please post the SQL.

 

[wrightyrx7]

It might be worth pointing out to IT that this would possibly reduce network traffic and server load on the SQL Server. And keep users happy that they haven't got dog slow PC's and laptops that all need upgrading

I wish our IT department thought like you Minty haha. We are constantly battling with them to access to our own data.

Access will normally begin displaying the data as soon as the first page is available. However if the results are ordered then it must wait until all records are returned before it can display the top results.

The form or report may have a sort applied and this will have a similar effect as Order By added to the query.

Please post the SQL.

Oh thats useful to know about the ORDER BY. Learn something new everyday.

There is a reason i didnt want to post the code, is because there is alot of it! haha

I know there is alot of code, but like i said earlier, when i double click the query and open it in a datasheet, it loads in a second or two.

Its when it is used as a data source it slows down. I have just removed the ORDER BY on the combobox and subform but its still slow loading.

SELECT 
EMP_REF.REFERENCE_NO+POSITION_NAME.PARTY_REF_NO AS ID, 
EMP_REF.REFERENCE_NO AS Personal_Ref, 
POSITION_NAME.PARTY_REF_NO AS Position_Ref, 
POSITION_NAME.PARTY_NM AS Position_Title, 
EMP_PERSON.FORENAME1 AS Forename1, 
EMP_PERSON.SURNAME AS Surname, 
rtrim(EMP_PERSON.FORENAME1)+ ' ' + rtrim(EMP_PERSON.SURNAME) AS Full_Name, 
PER_ADDR.ADDR_1 AS Add_Line_1, 
PER_ADDR.ADDR_2 AS Add_Line_2, 
PER_ADDR.ADDR_3 AS Add_Line_3, 
PER_ADDR.ADDR_4 AS Add_Line_4, 
PER_ADDR.ADDR_5 AS Add_Line_5, 
PER_ADDR.ADDR_6 AS Add_Line_6, 
EMP_PERSON.CONT_REL_SRV_D AS Cont_Relevant_Service_Date, 
tleaver.starting_d AS Joining_Date, 
tparty_lnk.PARTY_LNK_D AS Start_Date_Occupancy, 
tparty_lnk.exp_occ_ed AS Expected_End_Date_Occupancy, 
TUER_CONT_HRS_V.CONTRACTUAL_HOURS AS Contractual_Hours, 
TUER_FTE_HRS_V.FTE_HOURS AS FTE_Hours, 
TUER_PARTY_WKS_V.weeks_worked AS Annual_Weeks_Worked, 
TUER_STR_GRADE_V.GRADE_ACT_VL AS Rate_of_Pay, 
ACT_SCALE_V.SCALE_PT_VL AS Actual_Scalepoint_Value, 
TUER_STR_GRADE_V.ACT_SCALE_PT_REF_CD AS Actual_Scalepoint, 
TUER_STR_GRADE_V.GRADE_REF_CD AS Grade, 
REP_DEPT_NM.PARTY_NM AS Reporting_Unit 
from 
trentadm.TUER_CONT_HRS_V RIGHT OUTER JOIN trentadm.tparty  POSITION_NAME ON (POSITION_NAME.party_id=TUER_CONT_HRS_V.party_id) 
LEFT OUTER JOIN trentadm.TUER_STR_GRADE_V ON (POSITION_NAME.party_id=TUER_STR_GRADE_V.party_id) 
LEFT OUTER JOIN trentadm.TUER_SCALE_V  ACT_SCALE_V ON (TUER_STR_GRADE_V.scale_pt_act=ACT_SCALE_V.scale_pt_id) 
LEFT OUTER JOIN trentadm.TUER_FTE_HRS_V ON (POSITION_NAME.party_id=TUER_FTE_HRS_V.party_id) 
LEFT OUTER JOIN trentadm.TUER_PARTY_WKS_V ON (POSITION_NAME.party_id=TUER_PARTY_WKS_V.party_id) 
INNER JOIN trentadm.tparty_lnk ON (POSITION_NAME.party_id=tparty_lnk.party_id2) 
INNER JOIN trentadm.tparty  CONTRACT_NM ON (CONTRACT_NM.party_id=tparty_lnk.party_id) 
INNER JOIN trentadm.tper_party ON (tper_party.party_id=CONTRACT_NM.party_id) 
INNER JOIN trentadm.tperson  EMP_PERSON ON (tper_party.person_id=EMP_PERSON.person_id) 
LEFT OUTER JOIN trentadm.tper_reference  EMP_REF ON (EMP_REF.person_id=EMP_PERSON.person_id) 
INNER JOIN trentadm.tleaver ON (tleaver.person_id=EMP_PERSON.person_id) 
LEFT OUTER JOIN ( 
select 
TADDR_USED.ADDR_USED_ID, 
TADDR_USED.KEY_ITEM, 
ADDR_TYPE.THE_DESC as ADDR_TYPE, 
TADDRESS.ADDR_1, 
TADDRESS.ADDR_2, 
TADDRESS.ADDR_3, 
TADDRESS.ADDR_4, 
TADDRESS.ADDR_5, 
TADDRESS.ADDR_6, 
ADDR_CNTRY.THE_DESC as ADDR_CNTRY, 
case 
when TADDR_USED.MAILING_ADDR_I = 'T' 
then 'Yes' 
Else 'No' 
end as MAILING_ADDR, 
TADDR_USED.ADDR_D, 
TADDR_USED.ADDR_ED, 
TADDRESS.LAST_MOD_D, 
TADDRESS.LAST_MOD_T, 
TADDRESS.LAST_MOD_USER_ID 
from 
trentadm.TADDR_USED 
inner join trentadm.TADDRESS on TADDR_USED.ADDR_ID = TADDRESS.ADDR_ID 
left outer join trentadm.TDESC ADDR_TYPE on TADDR_USED.ADDR_TYPE_ID = ADDR_TYPE.KEY_ITEM 
and ADDR_TYPE.ENTITY_NM = 'TCODE' 
and ADDR_TYPE.COLUMN_NM = 'CODE_DESC' 
and ADDR_TYPE.LANGUAGE_ID = 'USA' 
left outer join trentadm.TDESC ADDR_CNTRY on TADDRESS.COUNTRY_ID = ADDR_CNTRY.KEY_ITEM 
and ADDR_CNTRY.ENTITY_NM = 'TCOUNTRY' 
and ADDR_CNTRY.COLUMN_NM = 'CNTRY_NM' 
and ADDR_CNTRY.LANGUAGE_ID = 'USA' 
WHERE 
TADDR_USED.ENTITY_NM = 'TPERSON' 
)  PER_ADDR ON (EMP_PERSON.person_id=PER_ADDR.KEY_ITEM) 
RIGHT OUTER JOIN trentadm.tparty_lnk  REP_DEPT_LNK ON (POSITION_NAME.party_id=REP_DEPT_LNK.party_id) 
RIGHT OUTER JOIN trentadm.tparty  REP_DEPT_NM ON (REP_DEPT_LNK.party_id2=REP_DEPT_NM.party_id) 
WHERE 
( (REP_DEPT_LNK.link_type_id='PN_RP_UNIT' or REP_DEPT_LNK.link_type_id IS NULL)  ) 
AND  ( REP_DEPT_NM.object_type='UNIT'  ) 
AND  ( CONTRACT_NM.object_type='CONT'  ) 
AND  ( tparty_lnk.link_type_id = 'CONT'  ) 
AND  ( tparty_lnk.team_party_id IS NULL  ) 
AND  ( EMP_PERSON.non_emp_s='0'  ) 
AND  ( POSITION_NAME.object_type='POSITION'  ) 
AND 
( 
( ( tleaver.starting_d ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) AND (( tleaver.leaving_d ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tleaver.leaving_d ) is null)  ) 
AND 
( (( PER_ADDR.ADDR_D ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( PER_ADDR.ADDR_D ) is null) AND (( PER_ADDR.ADDR_ED ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( PER_ADDR.ADDR_ED ) is null)  ) 
AND 
( (( tparty_lnk.PARTY_LNK_D ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tparty_lnk.PARTY_LNK_D ) is null) AND (( tparty_lnk.PARTY_LNK_ED ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tparty_lnk.PARTY_LNK_ED ) is null)  ) 
AND 
( (TUER_CONT_HRS_V.INHERITED_FROM_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_CONT_HRS_V.INHERITED_FROM_DATE IS NULL) 
AND (TUER_CONT_HRS_V.INHERITED_TO_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_CONT_HRS_V.INHERITED_TO_DATE IS NULL) 
AND (TUER_CONT_HRS_V.HOURS_START_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) )  OR 
TUER_CONT_HRS_V.HOURS_START_DATE IS NULL ) 
AND (TUER_CONT_HRS_V.HOURS_END_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_CONT_HRS_V.HOURS_END_DATE IS NULL) 
) 
AND 
( (TUER_FTE_HRS_V.INHERITED_FROM_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_FTE_HRS_V.INHERITED_FROM_DATE IS NULL) 
AND (TUER_FTE_HRS_V.INHERITED_TO_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_FTE_HRS_V.INHERITED_TO_DATE IS NULL) 
AND (TUER_FTE_HRS_V.HOURS_START_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_FTE_HRS_V.HOURS_START_DATE IS NULL) 
AND (TUER_FTE_HRS_V.HOURS_END_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_FTE_HRS_V.HOURS_END_DATE IS NULL) 
) 
AND 
( (TUER_PARTY_WKS_V.inherited_from_date<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_PARTY_WKS_V.inherited_from_date IS NULL) 
AND( TUER_PARTY_WKS_V.inherited_to_date>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_PARTY_WKS_V.inherited_to_date IS NULL) 
AND (TUER_PARTY_WKS_V.weeks_start_date<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) )  OR 
TUER_PARTY_WKS_V.weeks_start_date IS NULL) 
AND (TUER_PARTY_WKS_V.weeks_end_date>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_PARTY_WKS_V.weeks_end_date IS NULL) 
) 
AND 
( (TUER_STR_GRADE_V.INHERITED_FROM_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_STR_GRADE_V.INHERITED_FROM_DATE IS NULL) 
AND (TUER_STR_GRADE_V.INHERITED_TO_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_STR_GRADE_V.INHERITED_TO_DATE IS NULL) 
AND (TUER_STR_GRADE_V.GRADE_START_DATE<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_STR_GRADE_V.GRADE_START_DATE IS NULL) 
AND (TUER_STR_GRADE_V.GRADE_END_DATE>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
TUER_STR_GRADE_V.GRADE_END_DATE IS NULL)  ) 
AND 
( (( ACT_SCALE_V.SCALE_DET_D )<=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( ACT_SCALE_V.SCALE_DET_D ) IS NULL) AND (( ACT_SCALE_V.SCALE_DET_ED )>=( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( ACT_SCALE_V.SCALE_DET_ED ) IS NULL)  ) 
AND 
( (REP_DEPT_LNK.PARTY_LNK_D <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR REP_DEPT_LNK.PARTY_LNK_D IS NULL) AND 
(REP_DEPT_LNK.PARTY_LNK_ED >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR 
REP_DEPT_LNK.PARTY_LNK_ED IS NULL)  ) 
)

 

[Minty]

I can't believe you need that much filtering and joining to get you a list for use in a combobox? There must be a simpler route to your initial list?

I would also then use that as a base for filtering your main query results.

 

[wrightyrx7]

I can't believe you need that much filtering and joining to get you a list for use in a combobox? There must be a simpler route to your initial list?

I would also then use that as a base for filtering your main query results.

Ok so i just wrote two new queries.

One for the combobox

SELECT
  EMP_REF.REFERENCE_NO AS Personal_Ref,
  rtrim(EMP_PERSON.FORENAME1)+ ' ' + rtrim(EMP_PERSON.SURNAME) AS Full_Name
FROM
  trentadm.tper_reference  EMP_REF RIGHT OUTER JOIN trentadm.tperson  EMP_PERSON ON (EMP_REF.person_id=EMP_PERSON.person_id)
   INNER JOIN trentadm.tleaver ON (tleaver.person_id=EMP_PERSON.person_id)
  
WHERE
  ( EMP_PERSON.non_emp_s='0'  )
  AND  
  ( ( tleaver.starting_d ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) AND (( tleaver.leaving_d ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tleaver.leaving_d ) is null)  )

and one for the subform

SELECT
  EMP_REF.REFERENCE_NO AS Personal_Ref,
  POSITION_NAME.PARTY_REF_NO AS Position_Ref,
  POSITION_NAME.PARTY_NM AS Position_Title,
  rtrim(EMP_PERSON.FORENAME1)+ ' ' + rtrim(EMP_PERSON.SURNAME) AS Full_Name,
  EMP_REF.REFERENCE_NO+POSITION_NAME.PARTY_REF_NO AS ID
FROM
  trentadm.tper_reference  EMP_REF RIGHT OUTER JOIN trentadm.tperson  EMP_PERSON ON (EMP_REF.person_id=EMP_PERSON.person_id)
   INNER JOIN trentadm.tper_party ON (tper_party.person_id=EMP_PERSON.person_id)
   INNER JOIN trentadm.tparty  CONTRACT_NM ON (tper_party.party_id=CONTRACT_NM.party_id)
   INNER JOIN trentadm.tparty_lnk ON (CONTRACT_NM.party_id=tparty_lnk.party_id)
   INNER JOIN trentadm.tparty  POSITION_NAME ON (POSITION_NAME.party_id=tparty_lnk.party_id2)
   INNER JOIN trentadm.tleaver ON (tleaver.person_id=EMP_PERSON.person_id)
  
WHERE
  ( CONTRACT_NM.object_type='CONT'  )
  AND  ( tparty_lnk.link_type_id = 'CONT'  )
  AND  ( tparty_lnk.team_party_id IS NULL  )
  AND  ( EMP_PERSON.non_emp_s='0'  )
  AND  ( POSITION_NAME.object_type='POSITION'  )
  AND  
  (
   ( ( tleaver.starting_d ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) AND (( tleaver.leaving_d ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tleaver.leaving_d ) is null)  )
   AND
   ( (( tparty_lnk.PARTY_LNK_D ) <= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tparty_lnk.PARTY_LNK_D ) is null) AND (( tparty_lnk.PARTY_LNK_ED ) >= ( convert(datetime,substring(convert(varchar(25), getdate(), 0 ),1,11),0) ) OR ( tparty_lnk.PARTY_LNK_ED ) is null)  )
  )

Unexpectedly the load times have not really improved

 

[wrightyrx7]

EDIT: My VBA code that filters the subform was changing the subform datasource back to the HUGE query again. Not the smaller one created above.

Once i change this to look at the smaller pass through query it is a lot faster at loading the data.

Thank you both for your help with this