none
Access Query Takes Too Long To Produce Results RRS feed

  • Question

  • Hi,

    Below is my query, please help me to run it faster. Thanks.

    Let me know if you need any other information

    QUERY:

    SELECT DB2P_TPH0040.RSRCH_STUS_CDE, DB2P_TPH0350.DS_PHMCY_ID, DB2P_TPH0350.DS_PHMCY_RPTD_NAM, DB2P_TPH0350.RPTD_ADDR, DB2P_TPH0350.RPTD_CITY, DB2P_TPH0350.RPTD_ST, DB2P_TPH0350.RPTD_ZIP_CDE, DB2P_TPH0350.DS_TLPHN_NBR INTO [T_New_In-Progress_Week_Start_List]

    FROM DB2P_TPH0350 INNER JOIN (DB2P_TPH0050 INNER JOIN DB2P_TPH0040 ON DB2P_TPH0050.DS_PHMCY_GID = DB2P_TPH0040.DS_PHMCY_GID) ON DB2P_TPH0350.DS_PHMCY_GID = DB2P_TPH0050.DS_PHMCY_GID

    WHERE (((DB2P_TPH0040.RSRCH_STUS_CDE)="I" Or (DB2P_TPH0040.RSRCH_STUS_CDE)="M") AND ((DB2P_TPH0350.CURR_VER_IND)="Y") AND ((DB2P_TPH0050.RSRCH_CMNT_TEXT) Like "*F:  MM[-,.,/]DD[-,.,/]*16*") AND ((DB2P_TPH0040.RSRCH_CTGRY_CDE)="N")) OR (((DB2P_TPH0040.RSRCH_STUS_CDE)="I" Or (DB2P_TPH0040.RSRCH_STUS_CDE)="M") AND ((DB2P_TPH0350.CURR_VER_IND)="Y") AND ((DB2P_TPH0050.RSRCH_CMNT_TEXT) Like "*F:  M[-,.,/]D[-,.,/]*16*") AND ((DB2P_TPH0040.RSRCH_CTGRY_CDE)="N")) OR (((DB2P_TPH0040.RSRCH_STUS_CDE)="I" Or (DB2P_TPH0040.RSRCH_STUS_CDE)="M") AND ((DB2P_TPH0350.CURR_VER_IND)="Y") AND ((DB2P_TPH0050.RSRCH_CMNT_TEXT) Like "*F:  MM[-,.,/]D[-,.,/]*16*") AND ((DB2P_TPH0040.RSRCH_CTGRY_CDE)="N")) OR (((DB2P_TPH0040.RSRCH_STUS_CDE)="I" Or (DB2P_TPH0040.RSRCH_STUS_CDE)="M") AND ((DB2P_TPH0350.CURR_VER_IND)="Y") AND ((DB2P_TPH0050.RSRCH_CMNT_TEXT) Like "*F:  M[-,.,/]DD[-,.,/]*16*") AND ((DB2P_TPH0040.RSRCH_CTGRY_CDE)="N"));

    Thursday, August 25, 2016 9:10 PM

Answers

  • Yours WHERE:

    WHERE  ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
             AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
             AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                   "*f:  mm[-,.,/]dd[-,.,/]*16*" ) 
             AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  m[-,.,/]d[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  mm[-,.,/]d[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  m[-,.,/]dd[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ); 
    All "or" group

    1. search "i" and "m" in db2p_tph0040.rsrch_stus_cde
    2. search "n" in db2p_tph0040.rsrch_ctgry_cde
    3. seaarch "y" in db2p_tph0350.curr_ver_ind

    4. pattern in like: char list in [] don't need comma separator

    So, change where:

    WHERE  
    	db2p_tph0040.rsrch_stus_cde in ("i","m")
    	AND db2p_tph0040.rsrch_ctgry_cde  = "n"
    	AND db2p_tph0350.curr_ver_ind  = "y"
    	AND
    		(
    		db2p_tph0050.rsrch_cmnt_text     LIKE "*f:  mm[-./]dd[-./]*16*"
    		OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  m[-./]d[-./]*16*" 
                    OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  mm[-./]d[-./]*16*"  
                    OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  m[-./]dd[-./]*16*"  
    		); 


    Michał

    Thursday, August 25, 2016 10:19 PM

All replies

  • Did you design this database?

    Can the design be modified?

    Is this an Access back-end?

    Do you have to have non-sargable predicates such as "Like "*F...."?


    -Tom. Microsoft Access MVP

    Thursday, August 25, 2016 9:22 PM
  • Hi Tom,

    Did you design this database? YES

    Can the design be modified? YES

    Is this an Access back-end? YES

    Do you have to have non-sargable predicates such as "Like "*F...."? Yes, I have to use this.

    Regards,

    Sunil

    Thursday, August 25, 2016 9:25 PM
  • Ensure that all columns mentioned in the ON clauses (in the joins) are indexed.

    Also make as many of the columns in the WHERE clause as possible indexed.

    The LIKE comparisons will kill the efficiency, consider using a separate indexed column to filter this data rather than embedding it in the comments.

    How big are the tables?

    Thursday, August 25, 2016 10:10 PM
  • Yours WHERE:

    WHERE  ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
             AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
             AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                   "*f:  mm[-,.,/]dd[-,.,/]*16*" ) 
             AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  m[-,.,/]d[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  mm[-,.,/]d[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ) 
            OR ( ( ( db2p_tph0040.rsrch_stus_cde ) = "i" 
                    OR ( db2p_tph0040.rsrch_stus_cde ) = "m" ) 
                 AND ( ( db2p_tph0350.curr_ver_ind ) = "y" ) 
                 AND ( ( db2p_tph0050.rsrch_cmnt_text ) LIKE 
                       "*f:  m[-,.,/]dd[-,.,/]*16*" ) 
                 AND ( ( db2p_tph0040.rsrch_ctgry_cde ) = "n" ) ); 
    All "or" group

    1. search "i" and "m" in db2p_tph0040.rsrch_stus_cde
    2. search "n" in db2p_tph0040.rsrch_ctgry_cde
    3. seaarch "y" in db2p_tph0350.curr_ver_ind

    4. pattern in like: char list in [] don't need comma separator

    So, change where:

    WHERE  
    	db2p_tph0040.rsrch_stus_cde in ("i","m")
    	AND db2p_tph0040.rsrch_ctgry_cde  = "n"
    	AND db2p_tph0350.curr_ver_ind  = "y"
    	AND
    		(
    		db2p_tph0050.rsrch_cmnt_text     LIKE "*f:  mm[-./]dd[-./]*16*"
    		OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  m[-./]d[-./]*16*" 
                    OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  mm[-./]d[-./]*16*"  
                    OR db2p_tph0050.rsrch_cmnt_text  LIKE "*f:  m[-./]dd[-./]*16*"  
    		); 


    Michał

    Thursday, August 25, 2016 10:19 PM
  • That is astonishing. Object names (including table names, field names) should be descriptive so everyone (especially the maintenance programmer coming after you sadly got hit by a truck) can readily understand the database design. I was assuming you were working with a database design of some ERP system from decades ago.

    I for one would refuse to work on your application until given the chance to FindAndReplace.

    Until such time I have nothing to offer that hasn't already been said.


    -Tom. Microsoft Access MVP

    Friday, August 26, 2016 4:01 AM