DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010

    Can we optimize this further?

    Dear All, I am looking for help to optimize below query. This query is to look into single table and see if data was changed. There are 9-10 unions of similar query to get change in other data. I am posting query below. Please let me know if it can be optimize further.
    =============================SQL QUERY=============
    SELECT A.Facility,
    B.data_entry_by PREVIOUS_DATA_ENTRY_BY,
    'Change to Last Name' Type_of_Change,
    A. freetxt_40_2 Change_To,
    B. freetxt_40_2 Change_from,
    SUBSTRING(A.MPI_uniqueid, 4, 50) MPI_uniqueid
    FROM pat_his A,
    pat_his B
    WHERE A.facility = B.facility
    AND A.pat = B.pat
    AND (UPPER(TRIM(A.freetxt_40_2)) <> UPPER(TRIM(B.freetxt_40_2)) or (B.freetxt_40_2 is null and A.freetxt_40_2 is not null))
    AND SUBSTRING(A.MPI_uniqueid, 4, 50) > SUBSTRING(B.MPI_uniqueid, 4, 50)
    AND A.data_entry_date >= {?From Date}
    AND A.data_entry_date <= {?Thru Date}

    AND SUBSTRING(B.MPI_uniqueid, 4, 50) = (SELECT MAX(SUBSTRING(C.MPI_uniqueid, 4, 50))
    FROM pat_his C
    WHERE A.facility = C.facility
    AND A.pat = C.pat
    AND SUBSTRING(A.MPI_uniqueid, 4, 50) > SUBSTRING(C.MPI_uniqueid, 4, 50))


  2. #2
    Join Date
    May 2002
    Any function applied to an indexed column practically guarantees the index will not be used. Test for not equals practically guarantees a full table scan (have to look at everything to see what is not present). Optimize further is more like not optimized at all to begin with.

  3. #3
    Join Date
    Nov 2010
    Mayhaps with some function-based indexes.

  4. #4
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    Quote Originally Posted by Flyby View Post
    Mayhaps with some function-based indexes.
    How is that going to help when there is a condition calling for a full table scan?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width