SikRikDaRula SikRikDaRula - 2 months ago 13
SQL Question

Why does my Query take so long to produce result

I have the following query that takes about 20-30 seconds to produce a result. I would like to get the result time down, I am using access on the front end to create queries and SQL Server on the backend. Not sure if there is an easier way and quicker way to run this query.

Here is my query:

SELECT DISTINCT
[UB-04s].client_id,
DATEDIFF("yyyy", [UB-04s]![patient_dob], [UB-04s]![admit_date]) AS [AGE at Admission],
invalid_diag_codes_by_age.start_age_yrs,
invalid_diag_codes_by_age.end_age_yrs,
invalid_diag_codes_by_age.diag_code
INTO
tmp10407
FROM
[UB-04s], invalid_diag_codes_by_age
WHERE
((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_Q)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_P)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_O)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_N)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_M)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_L)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_K)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_J)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_I)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_H)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_G)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_F)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_E)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_D)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_C)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_B)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].dx_A)=[invalid_diag_codes_by_age].[diag_code])) OR ((([UB-04s].client_id)=[Forms]![frmUB04s]![client_id])
AND (([UB-04s].principal_dx)=[invalid_diag_codes_by_age].[diag_code]));


THESE ARE JUST SNIPPETS FROM THE TABLES AS THESE ARE LARGE TABLES...

FIELDS FROM TABLE UB04s:

client_id principal_dx dx_A dx_B dx_C dx_D dx_E dx_F dx_G dx_H dx_I dx_J dx_K dx_L dx_M dx_N dx_O dx_P dx_Q
527
530 42843 4280 4148 41519 42741 4271 5849 5770 5739 99702 431 2760 44422 28981 28984 2384 78551 42611
531 5715 44489 34839 0785 99682 4589 5723 2762 42518 99779 99811 5180 2689 2769 57142 5680 4471 2894
533 5559 V145
116 2761 7802 4019 2724 V1582 2768
117 55321


FIELDS FROM INVALID DIAG CODES TABLE:

diag_code start_age_yrs end_age_yrs age_range_desc
04041 0 0.5 0-6 months of age
27701 0 1
3070 18 99
99779 0 1
5559 15 99


Basically what I am doing is searching through all diag codes for a client to see if it is a invalid diag code based on the clients age.

Answer

With no real details to work with and I am totally guessing as to what you want. But using aliases and removing all the extra parenthesis will help considerably. I also used IN instead of the repeated predicates. This is not going to work exactly like this but is pretty close.

SELECT DISTINCT u.client_id
    , DateDiff(year, [UB-04s]![patient_dob], [UB-04s]![admit_date]) AS [AGE at Admission]
    , i.start_age_yrs
    , i.end_age_yrs
    , i.diag_code 
INTO tmp10407
FROM [UB-04s] u
cross join invalid_diag_codes_by_age i
WHERE u.client_id = [Forms]![frmUB04s]![client_id] 
AND 
(
    u.dx_Q = i.diag_code
) 
OR 
(
    u.principal_dx = i.diag_code
    AND
    i.diag_code in 
    (
        u.dx_P
        , u.dx_O
        , u.dx_N
        , u.dx_M
        , u.dx_L
        , u.dx_K
        , u.dx_J
        , u.dx_I
        , u.dx_H
        , u.dx_G
        , u.dx_F
        , u.dx_E
        , u.dx_D
        , u.dx_C
        , u.dx_B
        , u.dx_A
    )
)