Passing single value to Access from SQL multiple times

Question

I have a procedure which checks the recordcount of multiple select statements and returns a value based on the count of the queries...

  -- check aDT
    If ((SELECT DISTINCT theFN, theIN, theLN, theANA FROM aDT
        WHERE theFN = @pF  and theIN = @pI 
        and theLN = @pL  and theANA = @pLY) > 0)
        BEGIN
            SET @Bool = 1
            RETURN @Bool
        END 

    -- check aDT2
    If ((SELECT theFN, theIN, theLN, theANA FROM aDT2
        GROUP BY theFN, theIN, theLN, theANA 
        HAVING theFN = @pF  and theIN = @pI 
        and theLN = @pL  and theANA = @pLY) > 0)
        BEGIN
            SET @Bool = 1
            RETURN @Bool
        END 

IF (EXISTS(SELECT DISTINCT tblFs.fldFN, tblFs.fldRN, tblFs.fldIN, tblUs.fldPK,
        tblUs.fldLN, tblUA.fldANA, 
        tblUA.fldMT, (CASE WHEN (IsNull([flddate1], '') = '') THEN [flddate2] 
        ELSE [flddate1] END) AS fldDate, tblUA.fldPG
        FROM tblUA INNER JOIN tblUs ON tblUA.fldULK = tblUs.
        fldUID INNER JOIN tblFs ON tblUs.fldFK = tblFs.fldFID 
        WHERE (tblFs.fldFN = @pF) AND (tblFs.fldIN = @pI ) AND (tblUs.
        fldLN = @pL ) AND (tblUA.fldANA = @pL)  
        BEGIN
            SET @sDup = 1
            RETURN @sDup
        END

I have multiple queries and if any of them return records the bit value is set to 1, else it is set to 0 and the value is always returned to MS Access. Unfortunately the function runs slower and slower each time; is using a table-valued function a better option?

UPDATE: I configured the function as a table-valued inline function and it is even slower. Phenomenally slow. I am just not sure how I can achieve the results I need to in a more efficient way.

Answer 1

Since you are interested only in whether or not a record exists, you could drop all the expensive operators, replacing them with cheaper alternatives:

If ( EXISTS (
    SELECT * FROM aDT WHERE theFN = @pF and theIN = @pI and theLN = @pL and theANA = @pLY
))
    BEGIN
        SET @Bool = 1
        RETURN @Bool
    END 

If ( EXISTS (
    SELECT * FROM aDT2 WHERE theFN = @pF and theIN = @pI and theLN = @pL and theANA = @pLY
))
    BEGIN
        SET @Bool = 1
        RETURN @Bool
    END 

IF ( EXISTS (
    SELECT *
    FROM tblUA
    INNER JOIN tblUs ON tblUA.fldULK = tblUs.fldUID
    INNER JOIN tblFs ON tblUs.fldFK = tblFs.fldFID 
    WHERE (tblFs.fldFN = @pF) AND (tblFs.fldIN = @pI ) AND (tblUs.fldLN = @pL ) AND (tblUA.fldANA = @pL)
))
    BEGIN
        SET @sDup = 1
        RETURN @sDup
    END

The result should be equivalent without DISTINCTs or GROUP BY/HAVING, because your results are binary: DISTINCT/GROUP BY clauses can change the exact record count, but they cannot change the outcome of the EXISTS operator.