May 27, 2015 at 5:38 am
Hi,
I'm having performance issues with a view I'm using on sql azure and want to add an index but there seems to be quite a few constraints, can anyone tell me if it is possible with these views (perhaps a clustered index as an identity(1,1)) and how I would do it, thanks. (I did read somewhere that for a view to have an index it can't call another view - this would be a bit of a sticking point as the first view does reference the second view)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[vw_ReferralKPIs] AS
SELECT dbo.PatientRefKPI.ClientRef, dbo.PatientRefKPI.dbPatID, dbo.PatientRefKPI.dbPatLastName, dbo.PatientRefKPI.dbPatBirthday, dbo.PatDetail.dbAddDate,
dbo.vw_PatReferrals.FirstName, dbo.vw_PatReferrals.LastName, dbo.StaffRefKPI.dbStaffLastName, dbo.Status.dbStatusDesc,
dbo.vw_PatReferrals.dbOtherRefType, dbo.vw_PatReferrals.RefTypeWord
FROM dbo.PatDetail
LEFT OUTER JOIN dbo.StaffRefKPI
RIGHT OUTER JOIN dbo.PatientRefKPI ON dbo.StaffRefKPI.dbStaffCnt = dbo.PatientRefKPI.dbDocID AND StaffRefKPI.ClientRef = PatientRefKPI.ClientRef
RIGHT OUTER JOIN dbo.Status ON dbo.PatientRefKPI.dbStatusID = dbo.Status.dbStatusID AND Status.ClientRef = PatientRefKPI.ClientRef
ON dbo.PatDetail.dbPatCnt = dbo.PatientRefKPI.dbPatCnt AND PatDetail.ClientRef = PatientRefKPI.ClientRef
LEFT OUTER JOIN dbo.vw_PatReferrals ON dbo.PatientRefKPI.dbPatCnt = dbo.vw_PatReferrals.dbPatCnt AND vw_PatReferrals.ClientRef = PatientRefKPI.ClientRef
WHERE (dbo.vw_PatReferrals.dbRefType = 'i')
GO
CREATE view [dbo].[vw_PatReferrals] AS
SELECT
LnkPatReferral.ClientRef,
dbo.InlineMaxDateTime(
dbo.LnkPatReferral.CotegaModifiedTime, dbo.PatientRefKPI.CotegaModifiedTime,
dbo.Responsible.CotegaModifiedTime, dbo.Siblings.CotegaModifiedTime,
dbo.OutsideDoctors.CotegaModifiedTime, dbo.Specialty.CotegaModifiedTime,
dbo.OtherReferrals.CotegaModifiedTime, dbo.StaffRefKPI.CotegaModifiedTime
) as CotegaModifiedTime,
LnkPatReferral.dbPatCnt,
LnkPatReferral.dbLPatRefAnyPtr,
LnkPatReferral.dbPatRefTablePtr,
LnkPatReferral.dbRefDate,
LnkPatReferral.dbRefType,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN 'PATIENT'
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN 'RPARTY'
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN 'SIBLING'
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN 'DOCTOR'
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN 'OTHER'
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN 'STAFF'
END AS RefTypeWord,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN PatientRefKPI.dbPatCnt
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN Responsible.dbRespCnt
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN Siblings.dbSibCnt
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN OutsideDoctors.dbDoctorCnt
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN OtherReferrals.dbOtherCnt
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN StaffRefKPI.dbStaffCnt
END AS FoundName,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN PatientRefKPI.dbPatFirstName
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN Responsible.dbRespFirstName
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN Siblings.dbSibFirstName
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN OutsideDoctors.dbDoctorFirstName
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN OtherReferrals.dbOtherRefFirstName
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN StaffRefKPI.dbStaffFirstName
END AS FirstName,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN PatientRefKPI.dbPatMiddleName
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN Responsible.dbRespMiddleName
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN Siblings.dbSibMiddleName
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN OutsideDoctors.dbDoctorMiddleName
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN OtherReferrals.dbOtherRefMiddleName
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN StaffRefKPI.dbStaffMiddleName
END AS MiddleName,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN PatientRefKPI.dbPatLastName
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN Responsible.dbRespLastName
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN Siblings.dbSibLastName
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN OutsideDoctors.dbDoctorLastName
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN OtherReferrals.dbOtherRefLastName
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN StaffRefKPI.dbStaffLastName
END AS LastName,
CASE
WHEN LnkPatReferral.dbPatRefTablePtr = 1 THEN PatientRefKPI.dbPatCommonName
WHEN LnkPatReferral.dbPatRefTablePtr = 2 THEN Responsible.dbRespCommonName
WHEN LnkPatReferral.dbPatRefTablePtr = 3 THEN Siblings.dbSibCommonName
WHEN LnkPatReferral.dbPatRefTablePtr = 4 THEN OutsideDoctors.dbDoctorCommonName
WHEN LnkPatReferral.dbPatRefTablePtr = 5 THEN OtherReferrals.dbOtherRefCommonName
WHEN LnkPatReferral.dbPatRefTablePtr = 6 THEN StaffRefKPI.dbStaffCommonName
END AS CommonName,
Specialty.dbSpecDesc,
Specialty.dbSpecCnt,
OtherReferrals.dbOtherRefType
FROM LnkPatReferral
LEFT JOIN PatientRefKPI ON LnkPatReferral.dbLPatRefAnyPtr = PatientRefKPI.dbPatCnt AND LnkPatReferral.dbPatRefTablePtr = 1 AND LnkPatReferral.ClientRef = PatientRefKPI.ClientRef
LEFT JOIN Responsible ON LnkPatReferral.dbLPatRefAnyPtr = Responsible.dbRespCnt AND LnkPatReferral.dbPatRefTablePtr = 2 AND LnkPatReferral.ClientRef = Responsible.ClientRef
LEFT JOIN Siblings ON LnkPatReferral.dbLPatRefAnyPtr = Siblings.dbSibCnt AND LnkPatReferral.dbPatRefTablePtr = 3 AND LnkPatReferral.ClientRef = Siblings.ClientRef
LEFT JOIN OutsideDoctors ON LnkPatReferral.dbLPatRefAnyPtr = OutsideDoctors.dbDoctorCnt AND LnkPatReferral.dbPatRefTablePtr = 4 AND LnkPatReferral.ClientRef = OutsideDoctors.ClientRef
LEFT JOIN Specialty ON OutsideDoctors.dbSpeciality = Specialty.dbSpecCnt AND LnkPatReferral.ClientRef = Specialty.ClientRef
LEFT JOIN OtherReferrals ON LnkPatReferral.dbLPatRefAnyPtr = OtherReferrals.dbOtherCnt AND LnkPatReferral.dbPatRefTablePtr = 5 AND LnkPatReferral.ClientRef = OtherReferrals.ClientRef
LEFT JOIN StaffRefKPI ON LnkPatReferral.dbLPatRefAnyPtr = StaffRefKPI.dbStaffCnt AND LnkPatReferral.dbPatRefTablePtr = 6 AND LnkPatReferral.ClientRef = StaffRefKPI.ClientRef
May 27, 2015 at 5:45 am
Not possible. To start with, you have outer joins which aren't allowed in an indexed view
This isn't an Azure limitation, the earthed SQL product has the same long list of indexed view limitations.
Perhaps an alternative approach? Instead of trying to index the views, maybe post the queries that are slow, the specific queries which access the views, along with their execution plans, table definitions and index definitions and we can try and help you tune the queries.
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
May 27, 2015 at 6:25 am
Views that call to other views and nest and join on views is a notorious code smell that leads to poor performance. My suggestion, write the query you want directly against the tables rather than use views.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 27, 2015 at 7:10 am
Thanks guys, I shall look into your advice.
May 27, 2015 at 7:33 am
Actually I may have been barking up the wrong tree. Running the sql below (which references the first view, which in turn references the second view) without the 3 SUM statements returns the result set pretty quickly and the SUM statements don't reference the views. Can you see any easy optimizing of those statements (I know I haven't posted any data, sorry)
SELECT
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
,
SUM(CASE WHEN t.LedgerAmount > 0 AND t.LedgerType !=29 AND t.LedgerType !=30 AND t.LedgerType != 31 AND t.LedgerType != 1 OR t.LedgerType = 16 THEN t.LedgerAmount ELSE 0.00 END) AS Charges,
SUM(CASE WHEN t.LedgerAmount < 0 AND t.LedgerType != 1 AND t.LedgerType != 16 AND t.LedgerType != 45 OR t.LedgerType = 29 OR t.LedgerType = 30 OR t.LedgerType = 31 THEN t.LedgerAmount ELSE 0.00 END) AS Payments,
SUM(CASE WHEN t.LedgerType = 1 OR t.LedgerType = 46 THEN t.LedgerAmount ELSE 0.00 END) * -1 AS Contracts
FROM
vw_ReferralKPIs r
LEFT JOIN Transactions t ON t.PatientID = r.dbPatID
AND r.ClientRef = t.ClientRef
LEFT JOIN Patient p ON p.dbPatID = r.dbPatID
AND r.ClientRef = p.ClientRef
WHERE
(r.dbAddDate >= '2014-01-01' OR '2014-01-01' = '')
AND (r.dbAddDate <= '2015-05-27 23:59' OR '2015-05-27 23:59' = '')
AND (r.dbStaffLastName IN ('') OR '' = '')
AND (r.LastName IN ('Tio ')) --OR 'Tio ' = '')
AND r.ClientRef = 'INV'
GROUP BY
r.dbPatID, p.dbPatFirstName, p.dbPatLastName, r.dbstatusdesc, r.dbAddDate, r.LastName, r.dbStaffLastName
thanks,
May 27, 2015 at 7:39 am
I may republish the previous post in the sql 2008 forum as I don't think it is sql azure related now.
May 27, 2015 at 8:07 am
mattech06 (5/27/2015)
Can you see any easy optimizing of those statements (I know I haven't posted any data, sorry)
I was about to say 'execution plans, table definitions and index definitions', then I realised you're doing this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
But other than that, table definitions, index definitions and execution plan please.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply