QuerySpeed - x-ian/dha-mis-migration GitHub Wiki

Tools for speed evaluation

Process for Speed analysis

run CsvQueryDump for both backends import & save into XLS import both into access query link based on field 3 (name), ideally incl. autonumber copy&paste result back to XLS change number decimal from . to , (for bloody german XLS)

Data Entry: Outstanding, untested, ...

Form_htc_provider_obs htc_prov_id_AfterUpdate

Form_obs_dim_set.form_section_select_AfterUpdate

Comment requery line to avoid the weird runtime error 2485. Unclear if not invoking this requery has any other negative side effect.

art_person_deduplicate (select query)

works with recent setup and versions

used in:

  • art_sched_person
  • art_staff
  • art_staff_obs

Issues: Syntax Errors with "identifier not converted (4); View definition contains 2 Format and 2 Iif statements"

Solution: Create a view with this definition:

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[art_person_deduplicate]
AS 
SELECT TOP 9223372036854775807 WITH TIES   
  art_person.ID, 
  CONCAT(art_person_1.NameLast, ' ', art_person_1.NameFirst, ', ', art_person_1.Position, ' (' + art_person_1.phone + ')', IIf(art_person.ID<>art_person.DeDuplicate_ID, ' (VOIDED)', '')) AS person, 
  art_person.DeDuplicate_ID
FROM art_person 
  INNER JOIN art_person AS art_person_1 ON art_person.DeDuplicate_ID = art_person_1.ID
ORDER BY Person;
GO

TODO: There is no immediate replacement for the FORMAT function used in Access; formatting is not done for now. One solution is to manually split the phone string into its subcomponents and add/concat spaces in between.

hdepartment_name (select query)

works with recent setup and versions

used in:

* psm_relocate (fields hdepartment_id_send and hdepartment_id_recv)

Issue: Error at "identifier not converted (2); Query definition contains a Trim and an Iif statement"

Solution: Create a view with this definition

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[hdepartment_name]
AS 
SELECT TOP 9223372036854775807 WITH TIES   
  code_hdepartment.ID,
  CONCAT(
    LTRIM(RTRIM(
      CONCAT(
	    [hfacility_name], 
	    ' ', 
	    IIf([hservice]=294,[name_healthdept],'')
	  )
    )),
    ' (', 
    concept_hservice.concept_name, 
    '), ',
    concept_district.concept_name
  ) AS SiteName
FROM (concept AS concept_district 
  INNER JOIN code_hfacility ON concept_district.ID = code_hfacility.district) 
  INNER JOIN (code_hdepartment 
  INNER JOIN concept AS concept_hservice ON code_hdepartment.hservice = concept_hservice.ID) 
  ON code_hfacility.ID = code_hdepartment.hfacility_id
ORDER BY SiteName;

supply_item_label (select query)

works with recent versions

used in:

  • art_drug_stocks
  • map_regimen_supply
  • map_supply_item_cms_code
  • supply_item_ID
  • psm_dist_batch
  • psm_DL_item
  • psm_relocate
  • psm_relocate_old
  • psm_ro_item
  • psm_stock_report
  • supply_item_set

Issues:

  • Warning: "View columns with same name (concept_3.concept_name and concept_4.concept_name)." Every column in MSSQL views needs to have an unique name.
  • With automatic migration the resulting view on MSSQL can not be used leading to a "Unable to open view in MSSQL: Msg 245, Level 16, State 1, Line 2; Conversion failed when converting the varchar value ')' to data type smallint."

Solution: Create view with following definition

USE [HIVData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[supply_item_label]
AS 
SELECT TOP 9223372036854775807 WITH TIES
  supply_item.ID, 
  CONCAT(concept.concept_name, ' ', [strength], ' ', concept_3.concept_name, ' (', [pack_size], ' ', concept_2.concept_name, ')') AS supply_item_label, 
  concept_3.concept_name as concept_3_name, 
  concept_4.concept_name as concept_4_name
FROM concept 
INNER JOIN ((((supply_item INNER JOIN concept AS concept_1 ON supply_item.presentation = concept_1.ID) 
INNER JOIN concept AS concept_2 ON supply_item.pack_unit = concept_2.ID) 
INNER JOIN concept AS concept_3 ON supply_item.pack_type = concept_3.ID) INNER JOIN concept AS concept_4 ON supply_item.inventory_unit = concept_4.ID) ON concept.ID = supply_item.item
ORDER BY supply_item_label;

Query optimizations

concept_app

Imports with .* from concept_live. But local table structure doesn't (and shouldn't!) have SSMA Timestamp. Replace the .* in Query definition with every single column except SSMA.

concept_set_app

Similar to concept_app

obs_set_tmptbl_de_update

  • Is used to update (Frontend) local temporary Access tables that collect data points for an already entered cohort report of one facility.
  • Only contains around 1500 rows, but is updated from 15 million obs and 400000 obs_dimensions.
  • Inner joins 2 local and 2 linked tables (obs, obs_dimensions) together
  • Takes much longer with MSSQL (not measured, but maybe 4x slower); now around 10 seconds
  • SQL Profiler shows, that for one invocation a few 1000 ODBC calls are executed.
  • Assumption (without full confirmation):
    • Nested inner joins prevent query parser from collecting 1500 joined records from MSSQL first and then invoking the update operation. Instead for every update a new request (maybe more precisely a reposition of the cursor) is invoked on MSSQL.
    • Or Access doesn't understand that the parameter (art_clinic_obs_v8.ID) to filter out the majority of records () can also be applied to the obs and obs_dimensions join and need to pull the complete result of the join (15 million records) before it can filter the records.

Optimization attempts:

1 Passthrough queries not possible due to local tables. Also passthrough queries can not be parameterized (as stupid as this sounds) 1 Server-side view between obs and obs_dimensions * Very fast, but when used within the update query with the local tables a message 'Operation must use an updatable query' appears. Even though the records from the view are only used as a source to update a local table (also stupid, but true) 1 'Outsourcing' of the join between obs and obs_dimensions in a dedicated select query in Access. When used standalone (not within the update query), it is also very fast. However when used in the update query, performance goes back to MSSQL slow speed.

  1. Separating the select from update query between obs and obs_dimensions with the same parameter as the update query ((obs_dimensions.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]). This appears to give Access the hint to first properly filter the obs and obs_dimensions join and then use the result for the update operation, hence back to Access-only fast execution time.

obs_set_tmptbl_de_update_BASE:

SELECT obs.ID, obs.obs_dimensions_ID, obs.data_element, obs.data_value, obs_dimensions.art_clinic_obs_id, obs_dimensions.period_report, obs_dimensions.sub_group, obs_dimensions.User, obs_dimensions.TimeStamp
FROM obs INNER JOIN obs_dimensions ON obs.obs_dimensions_ID = obs_dimensions.ID
WHERE (((obs_dimensions.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]));

obs_set_tmptbl_de_update:

UPDATE obs_dim_set_tmptbl INNER JOIN (obs_set_tmptbl INNER JOIN obs_set_tmptbl_de_update_BASE ON obs_set_tmptbl.data_element = obs_set_tmptbl_de_update_BASE.data_element) ON (obs_dim_set_tmptbl.ID = obs_set_tmptbl.obs_dim_set_tmptbl_ID) AND (obs_dim_set_tmptbl.sub_group = obs_set_tmptbl_de_update_BASE.sub_group) AND (obs_dim_set_tmptbl.period_report = obs_set_tmptbl_de_update_BASE.period_report) SET obs_set_tmptbl.data_value_tmp = obs.data_value
WHERE (((obs_set_tmptbl_de_update_BASE.art_clinic_obs_id)=[Forms]![art_clinic_obs_v8]![ID]));

data_entry_stats_page3

To be revalidated: Doesn't appear to be 'significantly too slow' anymore.

Using complex/aggregate functions and complex-ish join with GROUP BY and HAVING clause while using parameters (year and quarter). Access pulls a bigger resultset and applies the functions and aggregation locally. This leads to a lot of data. 12 million rows by the simple join, without any filter and aggregation, 2600 rows for the aggregated resultset being then finally filtered down to the 200 records for one year and quarter.

Solution: Temporarily hard-coding parameters into select statement and changing the Access functions to MSSQL replacements shows that the query runs (fast enough) on the server. TODO: Validate call in Access query with FIRST('3') changed to fixed '3'.

TODO: CAST shouldnt be needed anymore, test with removing it.

SELECT        dbo.obs_dimensions.[User], CAST(dbo.obs_dimensions.TimeStamp AS date) AS EntryDate, COUNT(dbo.obs.ID) AS DataElements, 
                         MIN(CAST(dbo.obs_dimensions.TimeStamp AS time)) AS EntryStart, MAX(CAST(dbo.obs_dimensions.TimeStamp AS time)) AS EntryStop, '3' AS Page, 
                         dbo.code_year_quarter.year, dbo.code_year_quarter.quarter
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id INNER JOIN
                         dbo.obs ON dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID ON dbo.code_year_quarter.ID = dbo.art_clinic_obs.year_quarter_id
GROUP BY dbo.obs_dimensions.[User], CAST(dbo.obs_dimensions.TimeStamp AS date), dbo.code_year_quarter.year, dbo.code_year_quarter.quarter

Moving this query into a dedicated view (e.g. view_data_entry_stats_page3) and (re-) linking it in Access makes the whole dataset accessible (note that the columns User, EntryDate, year, quarter needs to be specified as unique index columns during linking). Now an Access select query can use this view (Access sees it as an 'ordinary table') and parameters for year ad quarter can be added.

Note: There seems to be a bug in SSMS which leads to an error when using the query designer with above definition (https://connect.microsoft.com/SQLServer/feedback/details/587490/cannot-call-methods-on-date)

data_entry_stats_page3_OPTIMIZED

SELECT view_data_entry_stats_page3.User, view_data_entry_stats_page3.EntryDate, view_data_entry_stats_page3.DataElements, view_data_entry_stats_page3.EntryStart, view_data_entry_stats_page3.EntryStop, view_data_entry_stats_page3.Page, view_data_entry_stats_page3.Year, view_data_entry_stats_page3.Quarter
FROM view_data_entry_stats_page3
WHERE (((view_data_entry_stats_page3.Year)=[Enter year]) AND ((view_data_entry_stats_page3.Quarter)=[Enter quarter]))
ORDER BY view_data_entry_stats_page3.User DESC;

Note that times are displayed/formatted differently. Might cause troubles in additional UNIONS or reports.

Other attempts (just for documentary purpose): 1 However MSSQL views are static in the sense that they can not be parameterized. Typically a client queries the view with an additional WHERE clause to filter the results. But in this case the data of the query is not static (or can be filtered by a simple WHERE clause), but changes depending on the aggregate/HAVING function. Hence moving the query to the server doesn't work.

  1. A SQL Passthrough query also doesn't work, as they can not be defined with parameters. Access simply passes them as they are to MSSQL without any additional processing.
  2. One way out could be to create custom stored procedures or inline table value / user defined ([[UDF][https://msdn.microsoft.com/en-us/library/ms186755.aspx]])) functions within MSSQL; similar to http://stackoverflow.com/questions/1687279/can-we-pass-parameter-to-a-view-in-sql. This might allow to move the data processing to the server, but also increases the complexity (and potentially reduces usability as now the table/view might not be accessible as 'ordinary' tables/views). Maybe best examples found [[here][http://stackoverflow.com/a/11504933]] or [[here][http://stackoverflow.com/a/31143937]]. But most likely still doesn't seamlessly work as now the function call needs to be passed to MSSQL from Access (as in SELECT FROM my_function_name(my_parameter)) and this doesn't work with SQL Passthrough queries.
  3. Dynamically generate the SQL for the SQL Passthrough query based on the current parameters needed from VBA. Unsure how this would be used in reports or the like 'above'.
  4. Rewrite of logic possible? Is always the previous quarter used for data entry stats? If yes, then maybe the parameters could simply be calculated based on current day.

RecordSource of Form art_drug_stocks_supply_item

  • 'Inline' SQL, no dedicated query
  • Appears to be 2 or 3 times slower with MSSQL
  • Contains multiple INNER JOINS and one LEFT join

Supervision analysis: Outstanding, unknown, untested, unclear

To be addressed

Supply Mgmt - Dist round - Check supplies needed

Slightly ugly solution

See Query dependency hierarchy

Maybe psm_relocate_adjust_tmptbl as base table for psm_regim_consum_growth_stocks is the problem?

Plenty of references to calculated columns, need to be rewritten for SQL

psm_regim_consum_growth_stocks_mssql

SELECT view_psm_regim_consum_growth.art_clinic_obs_id, view_psm_regim_consum_growth.year_quarter_id, view_psm_regim_consum_growth.hdepartment_id, view_psm_regim_consum_growth.supply_item_ID, view_psm_regim_consum_growth.consum_now, view_psm_regim_consum_growth.consum_grow, view_psm_regim_consum_growth.consum_min, IIf([consum_need]>Nz([consum_need_min],0),[consum_need],[consum_need_min]) AS consum_need_adjust_min, art_drug_stocks.units_instock, art_drug_stocks.units_exp6m, Val(Nz([adjust_units],0)) AS units_adjust, DateDiff("d",[quarter_stopdate],[visit_date])/30.44 AS visit_interval, [visit_interval]*([consum_now]+[consum_grow]*[visit_interval]/2) AS visit_interval_consum, Round(Nz([units_instock],0)+[visit_interval_consum],0) AS units_endq, [units_endq]+Nz([units_adjust],0)-Nz([units_doomed_b4_consum_date],0) AS units_endq_adjust, IIf([units_endq_adjust]>=0,[units_endq_adjust],0) AS units_endq_adjust_pos, [consum_need_adjust_min]-[units_endq_adjust_pos] AS supply_need, IIf(([hservice_paed]=False And [item_paed]=True) Or ([hservice_fp]=False And [item_fp]=True),0,1) AS supply_item_allow, IIf([supply_need]>=0,[supply_need],0)*[supply_item_allow] AS supply_need_pos, art_drug_stocks.expiry_date_min, DateAdd("m",-1,[expiry_date_min]) AS expiry_date_min_back, IIf([units_exp6m]>=Abs(Nz([relo_out],0)+Nz([dispos],0)),[units_exp6m]-Abs(Nz([relo_out],0)+Nz([dispos],0)),0) AS units_exp6m_adj, Round(IIf([consum_now]>[consum_min],[consum_now],[consum_min])*IIf([expiry_date_min_back]>[visit_date],([expiry_date_min_back]-[visit_date])/30.44,0),0) AS units_consum_b4_expiry, IIf([units_consum_b4_expiry]<[units_exp6m_adj] And [expiry_date_min_back]<[consum_date],[units_exp6m_adj]-[units_consum_b4_expiry],0) AS units_doomed_b4_consum_date
FROM (((view_psm_regim_consum_growth LEFT JOIN art_drug_stocks ON (view_psm_regim_consum_growth.supply_item_ID = art_drug_stocks.supply_item_ID) AND (view_psm_regim_consum_growth.art_clinic_obs_id = art_drug_stocks.art_clinic_obs_id)) INNER JOIN view_supply_item_paed_fp ON view_psm_regim_consum_growth.supply_item_ID = view_supply_item_paed_fp.supply_item_id) INNER JOIN view_code_hdepartment_ARTpaed_FP ON view_psm_regim_consum_growth.hdepartment_id = view_code_hdepartment_ARTpaed_FP.hdepartment_id) LEFT JOIN psm_relocate_adjust_tmptbl ON (view_psm_regim_consum_growth.supply_item_ID = psm_relocate_adjust_tmptbl.supply_item_id) AND (view_psm_regim_consum_growth.art_clinic_obs_id = psm_relocate_adjust_tmptbl.art_clinic_obs_id);

psm_regim_consum_growth_stocks_sum_mssql

SELECT psm_regim_consum_growth_stocks_mssql.supply_item_ID, Sum(psm_regim_consum_growth_stocks_mssql.supply_need_pos) AS supply_need_total
FROM psm_regim_consum_growth_stocks_mssql
GROUP BY psm_regim_consum_growth_stocks_mssql.supply_item_ID;
CREATE VIEW [dbo].[view_code_hdepartment_ART]
AS
SELECT        dbo.code_hdepartment.ID AS hdepartment_ID, dbo.code_hdepartment.hfacility_id, dbo.code_year_quarter.year, dbo.code_year_quarter.quarter, 
                         dbo.code_year_quarter.ID
FROM            dbo.code_year_quarter INNER JOIN
                         dbo.code_hfacility INNER JOIN
                         dbo.code_hdepartment ON dbo.code_hfacility.ID = dbo.code_hdepartment.hfacility_id ON 
                         dbo.code_year_quarter.quarter_startdate >= dbo.code_hdepartment.service_start
WHERE        (dbo.code_hdepartment.hservmode = 309) AND (dbo.code_hfacility.Voided = 0) AND (dbo.code_hdepartment.Voided = 0) AND 
                         (dbo.code_hdepartment.service_end IS NULL) AND (dbo.code_hdepartment.service_end IS NULL OR
                         dbo.code_hdepartment.service_end <= dbo.code_year_quarter.quarter_stopdate) AND (dbo.code_hdepartment.hservice = 294)


CREATE VIEW [dbo].[view_code_hdepartment_ARTpaed_FP]
AS
SELECT code_hdepartment_ART.ID AS hdepartment_id, 
code_hdepartment_ART.hservice_paed,
IIf((view_code_hdepartment_FP.hfacility_id is not null),0,1) AS hservice_fp
FROM code_hdepartment AS code_hdepartment_ART 
LEFT JOIN view_code_hdepartment_FP ON code_hdepartment_ART.hfacility_id = view_code_hdepartment_FP.hfacility_id
WHERE (((code_hdepartment_ART.hservice)=294)
AND ((code_hdepartment_ART.Voided)=0) 
AND ((code_hdepartment_ART.hservmode)=309) 
AND ((code_hdepartment_ART.service_end) Is Null));

CREATE VIEW [dbo].[view_code_hdepartment_FP]
AS
SELECT        hfacility_id
FROM            dbo.code_hdepartment AS code_hdepartment_ART
WHERE        (hservice = 1334) AND (Voided = 0) AND (hservmode = 309) AND (service_end IS NULL)
GROUP BY hfacility_id


CREATE VIEW [dbo].[view_psm_regim_consum_growth]
AS
SELECT
dbo.view_psm_regim_consum_now.art_clinic_obs_id, 
dbo.view_psm_regim_consum_now.year_quarter_id, 
dbo.view_psm_regim_consum_now.hdepartment_id, 
dbo.view_psm_regim_consum_now.visit_date, 
dbo.view_psm_regim_consum_now.quarter_stopdate, 
dbo.view_psm_regim_consum_now.supply_item_ID, 
dbo.view_psm_regim_consum_now.consum_min, 
dbo.view_psm_regim_consum_now.Consume_month AS consum_now, 
IIf(dbo.view_psm_regim_consum_now.growth_allow=1,(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2),0) AS consum_grow, 
dbo.view_psm_regim_consum_now.consum_min*DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44 AS consum_need_min, 
(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2) AS consum_growth_calc, 
(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)) AS consum_prev1, 
(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0)) AS consum_prev2, 
DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44*(dbo.view_psm_regim_consum_now.Consume_month+IIf(dbo.view_psm_regim_consum_now.growth_allow=1,(((dbo.view_psm_regim_consum_now.Consume_month-(Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0)))+((Coalesce(dbo.view_psm_regim_consum_prev1.Consume_month,0))-(Coalesce(dbo.view_psm_regim_consum_prev2.Consume_month,0))))/2),0)*DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44/2) AS consum_need,
DateDiff("d",[quarter_stopdate],'01/01/2016')/30.44 AS supply_months, 
'01/01/2016' AS consum_date
FROM            dbo.view_psm_regim_consum_now LEFT OUTER JOIN
                         dbo.view_psm_regim_consum_prev2 ON dbo.view_psm_regim_consum_now.hdepartment_id = dbo.view_psm_regim_consum_prev2.hdepartment_id AND 
                         dbo.view_psm_regim_consum_now.map_regimen_supply_ID = dbo.view_psm_regim_consum_prev2.map_regimen_supply_ID LEFT OUTER JOIN
                         dbo.view_psm_regim_consum_prev1 ON dbo.view_psm_regim_consum_now.hdepartment_id = dbo.view_psm_regim_consum_prev1.hdepartment_id AND 
                         dbo.view_psm_regim_consum_now.map_regimen_supply_ID = dbo.view_psm_regim_consum_prev1.map_regimen_supply_ID



CREATE VIEW [dbo].[view_psm_regim_consum_now]
AS
SELECT        dbo.art_clinic_obs.ID AS art_clinic_obs_id, dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, MIN(dbo.art_clinic_obs.visit_date) AS visit_date, 
                         MIN(dbo.code_year_quarter.quarter_stopdate) AS quarter_stopdate, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         dbo.map_regimen_supply.supply_item_ID, COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month, 
                         dbo.map_regimen_supply.consum_min, dbo.map_regimen_supply.growth_allow
FROM            dbo.map_regimen_supply INNER JOIN
                         dbo.code_hdepartment INNER JOIN
                         dbo.code_year_quarter INNER JOIN
                         dbo.art_clinic_obs ON dbo.code_year_quarter.ID = dbo.art_clinic_obs.year_quarter_id INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.map_regimen_supply_rule ON dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group INNER JOIN
                         dbo.obs ON dbo.map_regimen_supply_rule.patient_regimen = dbo.obs.data_element AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID ON 
                         dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id ON dbo.code_hdepartment.ID = dbo.art_clinic_obs.hdepartment_id ON 
                         dbo.map_regimen_supply.ID = dbo.map_regimen_supply_rule.map_regimen_supply_ID
WHERE        (dbo.map_regimen_supply_rule.active = 1) AND (dbo.code_year_quarter.ID = 66)
GROUP BY dbo.art_clinic_obs.ID, dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         dbo.map_regimen_supply.supply_item_ID, dbo.map_regimen_supply.consum_min, dbo.map_regimen_supply.growth_allow


CREATE VIEW [dbo].[view_psm_regim_consum_prev1]
AS
SELECT        dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month
FROM            dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.map_regimen_supply_rule INNER JOIN
                         dbo.obs ON dbo.map_regimen_supply_rule.patient_regimen = dbo.obs.data_element ON 
                         dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id
WHERE        (dbo.art_clinic_obs.year_quarter_id = 66 - 1) AND (dbo.map_regimen_supply_rule.active = 0)
GROUP BY dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID


CREATE VIEW [dbo].[view_psm_regim_consum_prev2]
AS
SELECT        dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID, 
                         COALESCE (SUM(dbo.obs.data_value * dbo.map_regimen_supply_rule.unit_month), 0) AS Consume_month
FROM            dbo.art_clinic_obs INNER JOIN
                         dbo.obs_dimensions INNER JOIN
                         dbo.obs INNER JOIN
                         dbo.map_regimen_supply_rule ON dbo.obs.data_element = dbo.map_regimen_supply_rule.patient_regimen ON 
                         dbo.obs_dimensions.period_report = dbo.map_regimen_supply_rule.period_report AND dbo.obs_dimensions.ID = dbo.obs.obs_dimensions_ID AND 
                         dbo.obs_dimensions.sub_group = dbo.map_regimen_supply_rule.sub_group ON dbo.art_clinic_obs.ID = dbo.obs_dimensions.art_clinic_obs_id
WHERE        (dbo.art_clinic_obs.year_quarter_id = 66 - 2) AND (dbo.map_regimen_supply_rule.active = 0)
GROUP BY dbo.art_clinic_obs.year_quarter_id, dbo.art_clinic_obs.hdepartment_id, dbo.map_regimen_supply_rule.map_regimen_supply_ID



CREATE VIEW [dbo].[view_supply_item_paed_fp]
AS
SELECT supply_item.ID AS supply_item_id,
 supply_item.paed_form AS item_paed, 
IIf([concept_ID_parent]=362,1,0) AS item_fp
FROM concept INNER JOIN supply_item ON concept.ID = supply_item.item
WHERE (((supply_item.void)=0));
psm_relo_site_base
code_hdepartment_spokehub

Query no longer used; did not optimize

missing, ODBC fail: Msg 1011, Level 16, State 1, Line 1 The correlation name 'concept_district' is specified multiple times in a FROM clause.

Self-join of the same query including multiple sub queries. Looks like the generated SQL doesn't properly re-alias them so that these subqueries do not receive unique names.

Moving the underlying query code_hdepartment_hub_spoke to a MSSQL view. The previously used Access function StrConv is not available in MSSQL. Therefore a user defined function dbo.IniCap is created and then used to simulate something at least close to similar (InitCap is the name of the Oracle equivalent). Sadly the SSMS Query Designer is replacing the SQL function (?) CONCAT with multiple arguments to the canonical form { fn CONCAT('', '')} which only takes 2 arguments and changes the handling of NULL values. Therefore this view is created with direct SQL.

http://stackoverflow.com/questions/16567121/more-than-2-columns-in-a-concat-function http://stackoverflow.com/questions/19339538/sql-server-return-string-procedure-initcap

CREATE FUNCTION dbo.InitCap(@string varchar(30))
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
    RETURN UPPER(LEFT(@string,1)) + LOWER(SUBSTRING(@string, 2, 8000));
END;
GO

CREATE VIEW [dbo].[view_code_hdepartment_hub_spoke]
AS
SELECT        dbo.code_hdepartment.ID AS hdepartment_ID, dbo.InitCap(LTRIM(RTRIM(CONCAT(dbo.code_hfacility.hfacility_name, ' ', 
                         dbo.code_hdepartment.name_healthdept, ', ', concept_district.concept_name)))) AS site_name, 
                         dbo.code_hdepartment.hdepartment_hub_id
FROM            dbo.concept AS concept_district INNER JOIN
                         dbo.code_hfacility INNER JOIN
                         dbo.code_hdepartment ON dbo.code_hfacility.ID = dbo.code_hdepartment.hfacility_id ON concept_district.ID = dbo.code_hfacility.district
WHERE        (dbo.code_hfacility.Voided = 0) AND (dbo.code_hdepartment.Voided = 0) AND (dbo.code_hdepartment.service_end IS NULL) AND 
                         (dbo.code_hdepartment.hservmode = 309) AND (dbo.code_hdepartment.hservice = 294)

GO

Once this view is linked into the Access frontend, the Access query code_hdepartment_spokehub can be changed to the new MSSQL view:

SELECT code_hdepartment_hub.hdepartment_hub_id, code_hdepartment_spoke.site_name AS site_name_spoke, code_hdepartment_hub.site_name AS site_name_hub
FROM dbo_view_code_hdepartment_hub_spoke AS code_hdepartment_spoke LEFT JOIN dbo_view_code_hdepartment_hub_spoke AS code_hdepartment_hub ON code_hdepartment_spoke.hdepartment_hub_id = code_hdepartment_hub.hdepartment_ID;
psm_rule_supply_batch

TODO: Somehow this doesn't seem to be the full story. Failing to work now again. Unclear what and when it works and when not. In this particular case a workaround is to change the query supply_item_label_base from below to a SQL passthrough query. Code also given below.

Button Review quantification rules of Stock management invokes map_regimen_supply_openfrm_Click. This calls the query psm_rule_dim_de_list_tbl (which is created by psm_rule_dim_de_list_mktbl) and then loads the various elements of the form psm_rule_supply_batch. E.g. query psm_rule_supply_batch and supply_item_label (for combobox supply item id).

Access implicitly combines these various configured datasources/queries to send one SQL query to MSSQL. It appears as if Access combines implicit and explicit joins (or maybe doesn't restructure the queries, but simply 'throws' them into the query execution). But unlike Access MSSQL adheres to the SQL standard and doesn't fully support this syntax of Access. In addition here the same table is joined multiple times. Hence the query fails with some errors (like 'The multi-part identifier "dbo.concept.ID" could not be bound'). (Mixing implicit and explicit joins (http://stackoverflow.com/a/7314936))

There doesn't seem to be a way in Access to change this query construction method. But in this case 'outsourcing' the query supply_item_label with its 4 joins to concept into its own subquery supply_item_label_base and expose/reuse its columns in the query supply_item_label avoids the big SQL query generated by the form psm_rule_supply_batch.

supply_item_label_base (Access version):

SELECT supply_item.ID, concept.concept_name & " " & [strength] & " " & concept_3.concept_name & " (" & [pack_size] & " " & concept_2.concept_name & ")" AS supply_item_label, concept_3.concept_name AS pack_type_label, concept_4.concept_name AS inventory_unit_label, supply_item.paed_form
FROM concept INNER JOIN ((((supply_item INNER JOIN concept AS concept_1 ON supply_item.presentation = concept_1.ID) INNER JOIN concept AS concept_2 ON supply_item.pack_unit = concept_2.ID) INNER JOIN concept AS concept_3 ON supply_item.pack_type = concept_3.ID) INNER JOIN concept AS concept_4 ON supply_item.inventory_unit = concept_4.ID) ON concept.ID = supply_item.item
ORDER BY concept.concept_name & " " & [strength] & " " & concept_3.concept_name & " (" & [pack_size] & " " & concept_2.concept_name & ")";

supply_item_label_base (MSSQL version):

SELECT supply_item.ID, concat(concept.concept_name, ' ', [strength], ' ', concept_3.concept_name, ' (', [pack_size], ' ', concept_2.concept_name, ')') AS supply_item_label, concept_3.concept_name AS pack_type_label, concept_4.concept_name AS inventory_unit_label, supply_item.paed_form
FROM concept INNER JOIN ((((supply_item INNER JOIN concept AS concept_1 ON supply_item.presentation = concept_1.ID) INNER JOIN concept AS concept_2 ON supply_item.pack_unit = concept_2.ID) INNER JOIN concept AS concept_3 ON supply_item.pack_type = concept_3.ID) INNER JOIN concept AS concept_4 ON supply_item.inventory_unit = concept_4.ID) ON concept.ID = supply_item.item
ORDER BY concat(concept.concept_name, ' ', [strength], ' ', concept_3.concept_name, ' (', [pack_size], ' ', concept_2.concept_name, ')');

supply_item_label:

SELECT supply_item_label_base.ID, supply_item_label_base.supply_item_label, supply_item_label_base.pack_type_label, supply_item_label_base.inventory_unit_label, supply_item_label_base.paed_form
FROM supply_item_label_base
ORDER BY supply_item_label_base.supply_item_label;

For reference purpose this is the query send the the server:

SELECT "dbo"."psm_dist_round"."ID","dbo"."psm_dist_batch"."ID","dbo"."map_regimen_supply"."ID","concept_3"."concept_name" ,
"dbo"."concept"."concept_name" ,"dbo"."supply_item"."strength" ,"dbo"."supply_item"."pack_size" ,
"concept_2"."concept_name" ,"dbo"."concept"."ID","dbo"."supply_item"."ID","concept_1"."ID","concept_2"."ID",
"concept_3"."ID","concept_4"."ID" 
FROM "dbo"."psm_dist_round","dbo"."psm_dist_batch","dbo"."concept",
"dbo"."concept" "concept_1","dbo"."concept" "concept_2","dbo"."concept" "concept_3","dbo"."concept" "concept_4",
{oj "dbo"."map_regimen_supply" 
LEFT OUTER JOIN "dbo"."supply_item" ON (("dbo"."map_regimen_supply"."supply_item_ID" = "dbo"."supply_item"."ID" ) 
AND ((((("dbo"."concept"."ID" = "dbo"."supply_item"."item" ) AND ("dbo"."supply_item"."presentation" = "concept_1"."ID" ) ) 
AND ("dbo"."supply_item"."pack_unit" = "concept_2"."ID" ) ) AND ("dbo"."supply_item"."pack_type" = "concept_3"."ID" ) ) 
AND ("dbo"."supply_item"."inventory_unit" = "concept_4"."ID" ) ) ) }
WHERE ((("dbo"."psm_dist_round"."dist_round" =  ? ) 
AND ("dbo"."psm_dist_round"."ID" = "dbo"."psm_dist_batch"."psm_dist_round_id" ) ) 
AND ("dbo"."psm_dist_batch"."supply_item_ID" = "dbo"."map_regimen_supply"."supply_item_ID" ) )

And these are the errors messages from MSSQL:

DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "dbo.concept.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_1.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_2.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_3.ID" could not be bound. (4104) 
DIAG [37000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier "concept_4.ID" could not be bound. (4104)
chk_surv_de_reg
  • Heterogeneous join between local table map_survival_cohost and remote tables art_clinic_obs, obs_dimensions, code_year_quarter
  • Local map table has approx 7500 records. Appears as if for every record a query to art_clinic_obs (or obs_dimensions) is invoked. So no 'bulk query' happens, whereas with the Access the filter on art_clinic_obs is immediately available. (https://support.microsoft.com/en-us/help/286222/how-to-optimize-microsoft-access-when-using-odbc-data-sources)
  • Looks as if the join between art_clinic_obs and obs_dimensions together with map_survival_cohort slows it down. Removing the join between art_clinic_obs speeds up the query. There introduce a base join without obs_dimensions and later then link it in.

chk_surv_de_reg_base

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT art_clinic_obs.ID AS Form_ID_prev, map_survival_cohort.year_quarter_id_reg, art_clinic_obs.hdepartment_id, map_survival_cohort.sub_group, map_survival_cohort.data_element_reg, map_survival_cohort.data_element_surv
FROM code_year_quarter INNER JOIN (art_clinic_obs INNER JOIN map_survival_cohort ON art_clinic_obs.year_quarter_id = map_survival_cohort.year_quarter_id_reg) ON code_year_quarter.ID = map_survival_cohort.year_quarter_id
WHERE (((code_year_quarter.year)=[Enter year]) AND ((code_year_quarter.quarter)=[Enter quarter])) ORDER BY art_clinic_obs.ID;

chk_surv_de_reg

PARAMETERS [Enter year] Short, [Enter quarter] Short;
SELECT chk_surv_de_reg_base.Form_ID_prev, map_survival_cohort.year_quarter_id_reg, chk_surv_de_reg_base.hdepartment_id, obs_dimensions.ID AS obs_dimensions_id, chk_surv_de_reg_base.sub_group,chk_surv_de_reg_base.data_element_reg, chk_surv_de_reg_base.data_element_surv
FROM obs_dimensions INNER JOIN chk_surv_de_reg_base ON obs_dimensions.art_clinic_obs_id = chk_surv_de_reg_base.Form_ID_prev
WHERE (((obs_dimensions.period_report)=393) AND ((obs_dimensions.sub_group)=398));

art_clinic_obs_odo

no longer required

change from ORDER BY art_sched_set.team, [visit_date] + [start_time]; to ORDER BY art_sched_set.team, [visit_date], [start_time];

Obsolete queries

Queries that either didn't work at all or run much slower, but aren't needed anyways.

  • calc_test
  • calc_test_result
  • pmtct_data

Unclear queries / Slow or too slow queries

chk_outc chk_outc_new chk_outc_new_outlier chk_art_cohort_outcome_prev_now chk_surv_reg chk_surv_reggroup chk_surv_reg_diff chk_surv_de_reg_agg htc_set_site_district_greater_allq report_oi_quart_cum_expos_result_zone art_drug_stock_consum_year_quarter report_pmtct_trend_union report_pmtct_trend report_pmtct_trend_chart report_pmtct_trend_xtab supply_item_ART_levels_agg art_clinic_obs_certif_sched complete_join_report_drug_stocks art_cum_to_death

Reports

report_tb_ks_oc_cm

fails with ODBC error

recreate distribution list

happened once, unclear if a problem

you tried to assign the null value to a variable that is not a variant datat type

Review draft allocation list

Lock finalized list

⚠️ **GitHub.com Fallback** ⚠️