-
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,
A.Pat,
A.NAME,
A.data_entry_date,
A.data_entry_time,
A.data_entry_by,
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))
=====================================================
-
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.
-
Mayhaps with some function-based indexes.
-
Originally Posted by Flyby
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|