CODEWITHSUNDEEP
sqlsql-servert-sqloptimization
Ryan Nalls
Ryan Nalls

Reputation: 23

Attempting to optimize sql query

I have the following query that takes several seconds to run:

 SELECT COUNT(*) cnt
   FROM [PTYCHRG] A, [AVAIL_ACTION_TYPE_CODES_TEMP] B
  WHERE TRIM(A.CASE_ID) = TRIM(caseid)
    AND TRIM(B.casecode) = TRIM(casecd)
    AND (
            TRIM(A.ACTN_CD) = TRIM(B.ACTIONCODE)
            OR TRIM(A.INDICTED_ACTN_CD) = TRIM(B.ACTIONCODE)
            OR TRIM(A.AMENDED_ACTN_CD) = TRIM(B.ACTIONCODE)
        )
    AND LOWER(TRIM(B.DOMAIN)) = LOWER(TRIM(domain))
    AND [rownum] = 1;

I'm looking for a way/method to speed it up significantly as it is a part of a larger process.

I am basically looking to see if there is at least one record (I don't care what field I pull back, I just want to know if there is at least one record).

Any ideas on what I can do different to make it run faster?

Upvotes: 1

Views: 79

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This is your query rewritten with proper join syntax, better table aliases, and fewer parentheses::

SELECT COUNT(*) cnt
FROM PTYCHRG p JOIN
     AVAIL_ACTION_TYPE_CODES_TEMP atc
     ON TRIM(p.CASE_ID) = TRIM(atc.caseid) AND
        TRIM(atc.casecode) = TRIM(p.casecd) AND
        TRIM(atc.ACTIONCODE) IN (TRIM(p.ACTN_CD), TRIM(p.INDICTED_ACTN_CD), TRIM(p.AMENDED_ACTN_CD)) AND
        LOWER(TRIM(atc.DOMAIN)) = LOWER(TRIM(p.domain)) AND
 WHERE rownum = 1;

None of these fix any problems. Instead, they make it easier to higlight problems. First, you are running functions on values. This usually prevents SQL Server from using indexes, or even making more informed decisions about joins. If spaces are really a problem, there are two solutions: a functional index or fix the data. I would go with fixing the data:

update PTYCHRG
    set Case_Id = TRIM(Case_id),
        ActionCode = TRIM(ActionCode),
        ACTN_CD = TRIM(ACTN_CD),
        INDICTED_ACTN_CD = TRIM(INDICTED_ACTN_CD),
        AMENDED_ACTN_CD = TRIM(AMENDED_ACTN_CD),
        domain = lower(trim(domain));

update AVAIL_ACTION_TYPE_CODES_TEMP
    set caseid = trim(caseid),
        casecode = trim(casecode)
        ACTIONCODE = trim(ACTIONCODE),
        domain = lower(trim(domain));

Then the query becomes:

SELECT  COUNT(*) cnt
FROM PTYCHRG p JOIN
     AVAIL_ACTION_TYPE_CODES_TEMP atc
     ON p.CASE_ID = atc.caseid AND
        atc.casecode = p.casecd AND
        atc.ACTIONCODE IN (p.ACTN_CD, p.INDICTED_ACTN_CD, p.AMENDED_ACTN_CD) AND
        atc.DOMAIN = p.domain AND
 WHERE rownum = 1;

And then I would recommend indexes. You can try:

create index idx_AVAIL_ACTION_TYPE_CODES_TEMP_4
    on AVAIL_ACTION_TYPE_CODES_TEMP(caseid, casecode, domain, actioncode)

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6071

Ryan

Can you see, whether the SQL gives same result as yours.

SELECT  COUNT(*) cnt
FROM    [PTYCHRG] A
JOIN    [AVAIL_ACTION_TYPE_CODES_TEMP] B
ON      TRIM(A.CASE_ID) = TRIM(caseid)
AND     ISNULL(TRIM(A.ACTN_CD),ISNULL(TRIM(A.INDICTED_ACTN_CD),TRIM(A.AMENDED_ACTN_CD))) = TRIM(B.ACTIONCODE)
WHERE   [rownum] = 1

If avoidable, remove TRIM() from the sql as well

Upvotes: 0

Related Questions