August 3, 2011 at 8:33 pm
I have a query which is always making pageIO_Latch_Sh block in the server which is making server very slow.. this is a view in one database calling data from other database. This is used by an application to run reports etc. the database sql repository is shared with some other applications as well.. so it is affecting other applications as weell.
is there any better way to tune this query? please help with some ideas.. i know there are lots of inner, out and left joins. since we dont know the logic behind this query, we cant take it out ..is there any other way to get this same result.
SELECT DISTINCT
A.AccountNumber, B.PatientId AS UR_NO, B.PatientFamilyName AS SURNAME, B.PatientGivenName AS FIRST_NAME, A.AdmDate AS ADMIT_DATE,
A.DischargeDate AS DISCHARGE_DATE, C.AGHLOS AS LOS, E.SexId AS SEX_CODE, E.SexDesc AS SEX_DESC, C.AgeAtAdm AS AGE,
G.DRGCode AS DRG, G.DRGDesc AS DRG_DESC, C.WIESCSC AS WIES, AW.MDCId AS MDC_CODE, AW.MDCDesc AS MDC_DESC,
BA.InlierId AS INLIER_CODE, BA.InlierDesc AS INLIER, CASE WHEN datediff(dd, A.admdate, A.dischargedate)
= 0 THEN 'Yes' ELSE 'No' END AS SAMEDAY, C.Coder, C.CodedDate AS DATE_CODED, R.UnitId AS DISCHARGE_UNIT_CODE,
R.UnitDesc AS DISCHARGE_UNIT_DESC, S.WardId AS ADMISSION_WARD, T.WardId AS DISCHARGE_WARD, C.ICUHours AS HRS_ICU,
C.MVHours AS HRS_CMV, C.CCUHours AS HRS_CCU, U.TraumaId AS TRAUMA_CD, U.TraumaDesc AS TRAUMA_DESC, C.ISS AS ISS_SCORE,
C.ISSNew AS NEW_ISS, V.AdmSourceId AS ADM_SOURCE, V.AdmSourceDesc AS ADM_SOURCE_DESC, W.AdmTypeId AS ADM_TYPE,
W.AdmTypeDesc AS ADM_TYPE_DESC, X.DestinationId AS EXT_SRC, X.DestinationDesc AS EXT_DESC, AA.DestinationId AS DEST_CODE,
AA.DestinationDesc AS DEST_DESC, AB.DispositionId AS DISCHARGE_CODE, AB.DispositionDesc AS DISCHARGE_DESC,
B.PatientAddress1 AS ADDR1, B.PatientAddress2 AS ADDR2, B.PatientSuburb AS SUBURB, B.PatientPostcode AS PCODE,
B.PatientPhoneNo AS PHONE, B.BirthDate AS DOB, O.CountryId AS COUNTRY, O.CountryDesc AS COUNTRY_DESC, AC.LanguageId AS LANG_CODE,
AC.LanguageDesc AS LANG_DESC, P.ReligionId AS RELIG, P.ReligionDesc AS RELIG_DESC, B.DeathDate AS DEATH_DATE,
Y.PayClassId AS PAY_CLASS, Y.PayClassDesc AS PAY_DESC, Z.CareTypeId AS CARE_CODE, Z.CareTypeDesc AS CARE_DESC,
C.LEVDays AS LEV_DAYS, C.HIHDays AS HIH_DAYS, AD.AccountClassId AS ADM_CLASS, AD.AccountClassDesc AS ADM_DESC,
AE.AccountClassId AS SEP_CLASS, AE.AccountClassDesc AS SEP_DESC, AF.AccomTypeId AS ATYPE, AF.AccomTypeDesc AS ATYPE_DESC,
AG.ReAdmitId AS READMIT, AG.ReAdmitDesc AS READMIT_DESC, AH.EthnicOriginId AS ORIGIN_CODE, AH.EthnicOriginDesc AS ORIGIN_DESC,
C.HIVAIDS AS HIV_AIDS, C.InsLevCode AS INS_LEVEL, AI.FundId AS FUND_CODE, AI.FundDesc AS FUND_DESC, C.AdmDrCode AS ADM_DR,
AQ.Surname + ' ' + AQ.GivenNames AS ADM_DR_DESC, A.AttendingDrCode AS DIS_DOC, AR.Surname + ' ' + AR.GivenNames AS SEP_DR_DESC,
A.AdmDate AS ADM_TIME, A.DischargeDate AS DIS_TIME, AJ.CritCareTferId AS CRIT_CARE_TSFER, AJ.CritCareTferDesc AS CRIT_CARE_DESC,
C.PRSKey AS PRS2_KEY, AK.RefCareId AS SEP_REF, AK.RefCareDesc AS SEP_REF_DESC, C.RefDocName AS RD_NAME,
C.RefDocAddress1 AS RD_ADDRESS1, C.RefDocAddress2 AS RD_ADDRESS2, C.RefDocSuburb AS RD_SUBURB, C.RefDocPostcode AS RD_CODE,
C.RefDocPhoneNo AS RD_PHONE, AL.CarerAvId AS CARER_CODE, AL.CarerAvDesc AS CARER_DESC, AM.ClinSubProgCode AS SUB_PROG,
A.HospCode AS HOSP_CODE, AN.IntStayId AS DUR_STAY_CODE, AN.IntStayDesc AS DUR_STAY, AX.UnitCode AS TRANSFER_UNIT_CODE,
AO.UnitTferDate AS UT_TFR_DATE, AO.UnitExitDate AS UT_EXIT_DATE, AY.WardCode AS TRANSFER_WARD_CODE,
AP.WardTferDate AS WRD_TFR_DATE, AP.WardExitDate AS WRD_EXIT_DATE, LTRIM(STR(DATEDIFF(hour, A.AdmDate, A.DischargeDate))
+ ':' + LTRIM(STR(DATEDIFF(minute, A.AdmDate, A.DischargeDate) % 60))) AS HRS_MIN_STAY, N.code_dptype_code AS DPTYPE_CODE,
N.DiagVersion AS DIAG_VERSION, N.DiagSeq AS DIAG_SEQUENCE, N.DiagCode AS DIAG_CODE, N.ICDDesc AS DIAG_DESC,
N.ICDBlockCode AS ICDBLOCK_CODE, N.ICDBlockDesc AS ICDBLOCK_DESC, C.ContractLeaveFlag AS CTRCT_LEAVE_FLG, C.AdmBartScore,
C.SepBartScore, C.NIVHours AS NIV_HOURS, C.AdmWeight AS ADM_WEIGHT, B.MotherPMIid AS MOTH_PMID, G.LowTrimDays AS LOW_TRIM,
G.HighTrimDays AS HIGH_TRIM, A.Admid, Q.UnitId AS ADMISSION_UNIT_CODE, Q.UnitDesc AS ADMISSION_UNIT_DESC,
C.Coder AS CODER_INITIAL
FROM SQLRepository.dbo.tblAdmission AS A LEFT OUTER JOIN
SQLRepository.dbo.tblPMI AS B ON A.PMIid = B.PMIid LEFT OUTER JOIN
SQLRepository.dbo.tblLanguage AS AC ON B.LanguageId = AC.LanguageId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmissionSupp AS C ON A.Admid = C.Admid LEFT OUTER JOIN
SQLRepository.dbo.tblMDC AS AW ON C.MDCId = AW.MDCId LEFT OUTER JOIN
SQLRepository.dbo.tblSex AS E ON B.SexId = E.SexId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmissionDRG AS F ON A.Admid = F.AdmId LEFT OUTER JOIN
SQLRepository.dbo.tblDRG AS G ON F.DRGCode = G.DRGCode AND F.DRGVersion = G.DRGVersion LEFT OUTER JOIN
(SELECT H.AdmId, CAST('Dx' AS char(2)) AS code_dptype_code, H.DiagVersion, H.DiagSeq, RTRIM(LTRIM(H.DiagClassCode)) + LTRIM(H.DiagCode)
AS DiagCode, I.ICDDesc, L.ICDBlockCode, L.ICDBlockDesc
FROM SQLRepository.dbo.tblAdmissionDiag AS H LEFT OUTER JOIN
SQLRepository.dbo.tblICD AS I ON H.DiagVersion = I.ICDVersion AND H.DiagCode = I.ICDCode LEFT OUTER JOIN
(SELECT CAST('' AS varchar(5)) AS ICDBlockCode, CAST('' AS varchar(15)) AS ICDVersion, CAST('' AS varchar(100)) AS ICDBlockDesc)
AS L ON I.ICDBlockCode = L.ICDBlockCode
UNION ALL
SELECT M.AdmId, CAST('Px' AS char(2)) AS code_dptype_code, M.ProcVersion, M.ProcSeq, M.ProcCode, J.ICDDesc, K.ICDBlockCode,
K.ICDBlockDesc
FROM SQLRepository.dbo.tblAdmissionProc AS M LEFT OUTER JOIN
SQLRepository.dbo.tblICD AS J ON M.ProcVersion = J.ICDVersion AND M.ProcCode = J.ICDCode LEFT OUTER JOIN
SQLRepository.dbo.tblICDBlock AS K ON J.ICDBlockCode = K.ICDBlockCode AND J.ICDVersion = K.ICDVersion) AS N ON
A.Admid = N.AdmId LEFT OUTER JOIN
SQLRepository.dbo.tblCountry AS O ON B.PatientCountryId = O.CountryId LEFT OUTER JOIN
SQLRepository.dbo.tblReligion AS P ON B.ReligionId = P.ReligionId LEFT OUTER JOIN
SQLRepository.dbo.tblUnit AS Q ON C.AdmUnitId = Q.UnitId LEFT OUTER JOIN
SQLRepository.dbo.tblUnit AS R ON C.DisUnitId = R.UnitId LEFT OUTER JOIN
SQLRepository.dbo.tblWard AS S ON C.AdmWardId = S.WardId LEFT OUTER JOIN
SQLRepository.dbo.tblWard AS T ON C.DisWardId = T.WardId LEFT OUTER JOIN
SQLRepository.dbo.tblTrauma AS U ON C.TraumaId = U.TraumaId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmSource AS V ON A.AdmSourceId = V.AdmSourceId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmType AS W ON A.AdmTypeId = W.AdmTypeId LEFT OUTER JOIN
SQLRepository.dbo.tblProvider AS AT ON A.ReferringDrCode = AT.ProviderCode LEFT OUTER JOIN
SQLRepository.dbo.tblDestination AS X ON C.ExtSourceId = X.DestinationId LEFT OUTER JOIN
SQLRepository.dbo.tblDestination AS AA ON A.DestinationId = AA.DestinationId LEFT OUTER JOIN
SQLRepository.dbo.tblDisposition AS AB ON A.DispositionId = AB.DispositionId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmissionProc AS AU ON A.Admid = AU.AdmId LEFT OUTER JOIN
SQLRepository.dbo.tblICD AS AV ON AU.ProcCode = AV.ICDCode AND AU.ProcVersion = AV.ICDVersion LEFT OUTER JOIN
SQLRepository.dbo.tblPayClass AS Y ON A.PayClassId = Y.PayClassId LEFT OUTER JOIN
SQLRepository.dbo.tblCareType AS Z ON C.CareTypeId = Z.CareTypeId LEFT OUTER JOIN
SQLRepository.dbo.tblAccountClass AS AD ON C.AdmAccountClassId = AD.AccountClassId LEFT OUTER JOIN
SQLRepository.dbo.tblAccountClass AS AE ON C.DisAccountClassId = AE.AccountClassId LEFT OUTER JOIN
SQLRepository.dbo.tblAccomType AS AF ON C.AccomTypeId = AF.AccomTypeId LEFT OUTER JOIN
SQLRepository.dbo.tblReAdmit AS AG ON C.ReAdmitId = AG.ReAdmitId LEFT OUTER JOIN
SQLRepository.dbo.tblEthnicOrigin AS AH ON AH.EthnicOriginId = C.EthnicOriginId LEFT OUTER JOIN
SQLRepository.dbo.tblFund AS AI ON C.FundId = AI.FundId LEFT OUTER JOIN
SQLRepository.dbo.tblCritCareTfer AS AJ ON C.CritCareTferId = AJ.CritCareTferId LEFT OUTER JOIN
SQLRepository.dbo.tblRefCare AS AK ON C.RefCareId = AK.RefCareId LEFT OUTER JOIN
SQLRepository.dbo.tblCarerAv AS AL ON C.CarerAvId = AL.CarerAvId LEFT OUTER JOIN
SQLRepository.dbo.tblClinSubProg AS AM ON C.ClinSubProgId = AM.ClinSubProgId LEFT OUTER JOIN
SQLRepository.dbo.tblIntStay AS AN ON C.IntStayId = AN.IntStayId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmUnitTfer AS AO ON A.Admid = AO.AdmId LEFT OUTER JOIN
SQLRepository.dbo.tblAdmWardTfer AS AP ON A.Admid = AP.AdmId LEFT OUTER JOIN
SQLRepository.dbo.tblProvider AS AQ ON AQ.ProviderCode = C.AdmDrCode LEFT OUTER JOIN
SQLRepository.dbo.tblProvider AS AR ON AR.ProviderCode = A.AttendingDrCode LEFT OUTER JOIN
SQLRepository.dbo.tblUnit AS AX ON AO.UnitId = AX.UnitId LEFT OUTER JOIN
SQLRepository.dbo.tblWard AS AY ON AP.WardId = AY.WardId LEFT OUTER JOIN
SQLRepository.dbo.tblInlier AS BA ON C.InlierId = BA.InlierId
August 3, 2011 at 10:21 pm
I don't believe you're going to be able to do much with that view. The query is too big and the reason why it has a DISTINCT in it is because I can almost guarantee it has at least one "accidental cross join" in it otherwise known as a "many-to-many" join.
I don't know if it's possible on your end but the best way to fix this one is to break it up into smaller pieces using a Temp Table or two to hold a unique and much smaller interim result set to join to allong the way.
As a side bar, that view looks like something an ORM would produce for an "aggressive get" in a GUI and that view is typical of what an ORM would do to such a broad "get".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2011 at 12:18 am
Thank you for your reply. But i didnt get your "get"part. Can you explain a bit more.
thanks.
August 4, 2011 at 12:33 am
Another question would be: why are there only left outer joins?
There's not a single inner join (as far as I see).
I'm with Jeff here: it seems like this is an auto-generated query. What human being would alias the tables in alphabetical order giving up all readability???
It also seems like there is some room for improvemewnt on the table design level. The query indicates there ar easeveral columns in a table referencing to an identical (lookup?) table.
August 5, 2011 at 7:20 pm
Biz (8/4/2011)
Thank you for your reply. But i didnt get your "get"part. Can you explain a bit more.thanks.
When a GUI retrieves data from a database, it's sometime referred to as a "GET". An "Aggresive GET" is where everything to fill in perhaps multiple "pages" or screens of data (even though no one might go to those pages. These typically require joins from many, many tables and a lot of developers will use an ORM to automatically build the HQL or SQL necessary to "get" all of the information at once using a single monster query.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2011 at 3:35 am
A PageIOLatch is an IO subsystem wait. Means you're pulling more data than the drives can handle. To fix that you need to reduce the amount of data that the view needs. That usually means indexing, but your view is an unfiltered join of a tonne of tables. Unless you can filter that you're not going to be able to tune it much (if at all)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2011 at 5:47 pm
Thank you for your replies all.. am looking at the ways to tune the index.
thanks.
August 7, 2011 at 6:35 pm
Gail already pointed out the obvious. An index is mostly usefull when you filter data. Which you are not doing in any way here. This the the "download me the whole db" kind of query here.
Unless you have some sort of filtering applied to that view there's really nothing we or you can do besides adding more hardware to the problem.
Any way you can be opened to refactoring this process even a little bit?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply