Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Stuck with this update statement Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 10:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963

I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.


  Post Attachments 
Temp_SQLPlan.sqlplan (29 views, 718.19 KB)
Post #1450397
Posted Wednesday, May 8, 2013 1:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
curious_sqldba (5/7/2013)

I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.


Hover your mouse over the UPDATE icon and look at the memory grant value. You may want to sit down first.

Other folks might come up with suggestions for optimization if they could see the query. It looks overcomplicated from the plan - tables 12 and 5 are read up to 8 times each.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450431
Posted Wednesday, May 8, 2013 8:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
ChrisM@Work (5/8/2013)
curious_sqldba (5/7/2013)

I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.


Hover your mouse over the UPDATE icon and look at the memory grant value. You may want to sit down first.

Other folks might come up with suggestions for optimization if they could see the query. It looks overcomplicated from the plan - tables 12 and 5 are read up to 8 times each.


Yup, it is using about 20gb of memory.
Post #1450601
Posted Wednesday, May 8, 2013 8:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 6,813, Visits: 14,028
curious_sqldba (5/8/2013)
ChrisM@Work (5/8/2013)
curious_sqldba (5/7/2013)

I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.


Hover your mouse over the UPDATE icon and look at the memory grant value. You may want to sit down first.

Other folks might come up with suggestions for optimization if they could see the query. It looks overcomplicated from the plan - tables 12 and 5 are read up to 8 times each.


Yup, it is using about 20gb of memory.


Can we see the query please? With table names obfuscated?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1450604
Posted Thursday, May 9, 2013 8:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 1,061, Visits: 2,578
ChrisM@Work (5/8/2013)
curious_sqldba (5/8/2013)
ChrisM@Work (5/8/2013)
curious_sqldba (5/7/2013)

I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.


Hover your mouse over the UPDATE icon and look at the memory grant value. You may want to sit down first.

Other folks might come up with suggestions for optimization if they could see the query. It looks overcomplicated from the plan - tables 12 and 5 are read up to 8 times each.


Yup, it is using about 20gb of memory.


Can we see the query please? With table names obfuscated?


I didn't look at the whole plan in detail, but the fact that the multiple index seeks supporting what appears to be a series of self-joins of Table5 are returning around 100 million rows each for an update that eventually affects about 12,000 rows suggests that there's lots of room for improvement in the query.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1451145
Posted Thursday, May 9, 2013 6:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 5:50 PM
Points: 37, Visits: 303

There are lot of stuff u can do to improve this query. First thing I would recomend is Upadating the stats on most of the tables in the query.

I have attached screen shot that contains one example. THe diffrence b/w the actual number of rows and estimated number of rows is huge. That is clear indication that statiscs are skewed.

Regards.


  Post Attachments 
Skewed.bmp (15 views, 1.26 MB)
Post #1451391
Posted Tuesday, May 14, 2013 2:15 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
dedicatedtosql (5/9/2013)

There are lot of stuff u can do to improve this query. First thing I would recomend is Upadating the stats on most of the tables in the query.

I have attached screen shot that contains one example. THe diffrence b/w the actual number of rows and estimated number of rows is huge. That is clear indication that statiscs are skewed.

Regards.


No luck, updated the statistics still same. Attached is the code, the column names are different from the original sql plan but it is for the same


Edit: Even if i change the query to update only 0 records, when i hover my mouse in the exec plan it still shows that it is using 20gb of memory.


BEGIN TRAN

UPDATE MYTable
SET [ID] = V.ID,
[FacilityPatientID] = V.FacilityPatientID,
[FirstName] = V.FirstName,
[MiddleInitial] = V.MiddleInitial,
[LastName] = V.LastName,
[SSN] = V.SSN,
[Sex] = V.Sex,
[DOB] = V.DOB,
[EncounterID] = V.EncounterID,
[IDX] = V.IDX,
[MS4] = V.MS4,
[PersonID] = V.PersonID,
[GuarantorID] = V.GuarantorID,
[PatientType] = V.PatientType,
[FacilityPatientType] = V.FacilityPatientType,
[AdmitDate] = V.AdmitDate,
[PayerType] = V.PayerType,
[FacilityCode] = V.FacilityCode,
[Phone] = V.Phone,
[Address] = V.Address,
[Address2] = V.Address2,
[City] = V.City,
[State] = V.State,
[Zip] = V.Zip,
[FacilityPlanCode] = V.FacilityPlanCode,
[VerificationStatus] = V.VerificationStatus,
[PayorCode] = V.PayorCode,
[PayorName] = V.PayorName,
[ServiceFieldCode] = V.ServiceFieldCode,
[ResidualBalance] = V.ResidualBalance,
[IsScheduled] = V.IsScheduled,
[IsAdmitted] = V.IsAdmitted,
[IsDischarged] = V.IsDischarged,
[IsFinalBilled] = V.IsFinalBilled,
[BilledDate] = V.BilledDate,
[BilledAmount] = V.BilledAmount,
[TotalBalance] = V.TotalBalance,
[PatientBalance] = V.PatientBalance,
[PayorBalance] = V.PayorBalance,
[WriteOff] = V.WriteOff,
[PriorBalance] = V.PriorBalance,
[Collected] = V.Collected,
[CollectionDate] = V.CollectionDate,
[CollectionMethod] = V.CollectionMethod,
[NonCollectionReasonId] = V.NonCollectionReasonId,
[CustomerSatisfaction] = V.CustomerSatisfaction,
[CollectedBy] = V.CollectedBy,
[EstimatedResidual] = V.EstimatedResidual,
[TaskStatus_P] = V.TaskStatus_P,
[TaskStatus_C] = V.TaskStatus_C,
[TaskStatus_S] = V.TaskStatus_S,
[TaskStatus_R] = V.TaskStatus_R,
[TaskStatus_MN] = V.TaskStatus_MN,
[TaskStatus_ATH] = V.TaskStatus_ATH,
[TaskStatus_HIS] = V.TaskStatus_HIS,
[InsertDate] = V.InsertDate,
[PlanType] = V.PlanType,
[SubscriberCode] = V.SubscriberCode,
[ServicePointCode] = V.ServicePointCode,
[CurrentFinancialClass] = V.CurrentFinancialClass,
[DischargeDate] = V.DischargeDate,
[BusinessRuleID] = V.BusinessRuleID,
[TaskExceptionID] = V.TaskExceptionID,
[TotalCharges] = V.TotalCharges,
[BadDebt] = V.BadDebt
FROM MyView AS V (nolock)
INNER JOIN [MYTable] AS A (nolock)
ON A.Id = V.Id
WHERE A.Pid = 9
AND ( Isnull(A.ID, 0) <> Isnull(V.ID, 0)
OR Isnull(A.FacilityPatientID, '') <> Isnull(V.FacilityPatientID, '')
OR Isnull(A.FirstName, '') <> Isnull(V.FirstName, '')
OR Isnull(A.MiddleInitial, '') <> Isnull(V.MiddleInitial, '')
OR Isnull(A.LastName, '') <> Isnull(V.LastName, '')
OR Isnull(A.SSN, '') <> Isnull(V.SSN, '')
OR Isnull(A.Sex, '') <> Isnull(V.Sex, '')
OR Isnull(A.DOB, '') <> Isnull(V.DOB, '')
OR Isnull(A.EncounterID, '') <> Isnull(V.EncounterID, '')
OR Isnull(A.IDX, '') <> Isnull(V.IDX, '')
OR Isnull(A.MS4, '') <> Isnull(V.MS4, '')
OR Isnull(A.PersonID, 0) <> Isnull(V.PersonID, 0)
OR Isnull(A.GuarantorID, 0) <> Isnull(V.GuarantorID, 0)
OR Isnull(A.PatientType, '') <> Isnull(V.PatientType, '')
OR Isnull(A.FacilityPatientType, '') <> Isnull(V.FacilityPatientType, '')
OR Isnull(A.AdmitDate, '') <> Isnull(V.AdmitDate, '')
OR Isnull(A.PayerType, '') <> Isnull(V.PayerType, '')
OR Isnull(A.FacilityCode, '') <> Isnull(V.FacilityCode, '')
OR Isnull(A.Phone, '') <> Isnull(V.Phone, '')
OR Isnull(A.Address, '') <> Isnull(V.Address, '')
OR Isnull(A.Address2, '') <> Isnull(V.Address2, '')
OR Isnull(A.City, '') <> Isnull(V.City, '')
OR Isnull(A.State, '') <> Isnull(V.State, '')
OR Isnull(A.Zip, '') <> Isnull(V.Zip, '')
OR Isnull(A.FacilityPlanCode, '') <> Isnull(V.FacilityPlanCode, '')
OR Isnull(A.VerificationStatus, 0) <> Isnull(V.VerificationStatus, 0)
OR Isnull(A.PayorCode, '') <> Isnull(V.PayorCode, '')
OR Isnull(A.PayorName, '') <> Isnull(V.PayorName, '')
OR Isnull(A.ServiceFieldCode, '') <> Isnull(V.ServiceFieldCode, '')
OR Isnull(A.ResidualBalance, 0) <> Isnull(V.ResidualBalance, 0)
OR Isnull(A.IsScheduled, 0) <> Isnull(V.IsScheduled, 0)
OR Isnull(A.IsAdmitted, 0) <> Isnull(V.IsAdmitted, 0)
OR Isnull(A.IsDischarged, 0) <> Isnull(V.IsDischarged, 0)
OR Isnull(A.IsFinalBilled, 0) <> Isnull(V.IsFinalBilled, 0)
OR Isnull(A.BilledDate, '1/1/1900') <> Isnull(V.BilledDate, '1/1/1900')
OR Isnull(A.BilledAmount, 0) <> Isnull(V.BilledAmount, 0)
OR Isnull(A.TotalBalance, 0) <> Isnull(V.TotalBalance, 0)
OR Isnull(A.PatientBalance, 0) <> Isnull(V.PatientBalance, 0)
OR Isnull(A.PayorBalance, 0) <> Isnull(V.PayorBalance, 0)
OR Isnull(A.WriteOff, 0) <> Isnull(V.WriteOff, 0)
OR Isnull(A.PriorBalance, 0) <> Isnull(V.PriorBalance, 0)
OR Isnull(A.Collected, 0) <> Isnull(V.Collected, 0)
OR Isnull(A.CollectionDate, '') <> Isnull(V.CollectionDate, '')
OR Isnull(A.CollectionMethod, '') <> Isnull(V.CollectionMethod, '')
OR Isnull(A.NonCollectionReasonId, 0) <> Isnull(V.NonCollectionReasonId, 0)
OR Isnull(A.CustomerSatisfaction, 0) <> Isnull(V.CustomerSatisfaction, 0)
OR Isnull(A.CollectedBy, 0) <> Isnull(V.CollectedBy, 0)
OR Isnull(A.EstimatedResidual, 0) <> Isnull(V.EstimatedResidual, 0)
OR Isnull(A.TaskStatus_P, 0) <> Isnull(V.TaskStatus_P, 0)
OR Isnull(A.TaskStatus_C, 0) <> Isnull(V.TaskStatus_C, 0)
OR Isnull(A.TaskStatus_S, 0) <> Isnull(V.TaskStatus_S, 0)
OR Isnull(A.TaskStatus_R, 0) <> Isnull(V.TaskStatus_R, 0)
OR Isnull(A.TaskStatus_MN, 0) <> Isnull(V.TaskStatus_MN, 0)
OR Isnull(A.TaskStatus_ATH, 0) <> Isnull(V.TaskStatus_ATH, 0)
OR Isnull(A.TaskStatus_HIS, 0) <> Isnull(V.TaskStatus_HIS, 0)
OR Isnull(A.InsertDate, '1/1/1900') <> Isnull(V.InsertDate, '1/1/1900')
OR Isnull(A.PlanType, '') <> Isnull(V.PlanType, '')
OR Isnull(A.SubscriberCode, '') <> Isnull(V.SubscriberCode, '')
OR Isnull(A.ServicePointCode, '') <> Isnull(V.ServicePointCode, '')
OR Isnull(A.CurrentFinancialClass, '') <> Isnull(V.CurrentFinancialClass, '')
OR Isnull(A.DischargeDate, '') <> Isnull(V.DischargeDate, '')
OR Isnull(A.BusinessRuleID, '') <> Isnull(V.BusinessRuleID, '')
OR Isnull(A.TaskExceptionID, '') <> Isnull(V.TaskExceptionID, '')
OR Isnull(A.TotalCharges, 0) <> Isnull(V.TotalCharges, 0)
OR Isnull(A.BadDebt, 0) <> Isnull(V.BadDebt, 0) )
--rollback tran



Post #1452824
Posted Tuesday, May 14, 2013 2:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:09 PM
Points: 20,739, Visits: 32,528
No much we can do with the update statement since it joins to a view. We would need to see the DDL for the view and the underlying tables (including indexes).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1452834
Posted Wednesday, May 15, 2013 12:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963

i can try to get the schema.

In the meanwhile is there a better way to write this piece:

where Isnull(A.FPID, '') <> Isnull(V.FPID, '')
Post #1453251
Posted Wednesday, May 15, 2013 1:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 1,061, Visits: 2,578
curious_sqldba (5/15/2013)

i can try to get the schema.

In the meanwhile is there a better way to write this piece:

where Isnull(A.FPID, '') <> Isnull(V.FPID, '')


The logic embodied in that form of condition is cumbersome no matter how you write it - managing NULLs in an ANSI SQL 92-compliant environment requires careful attention.

Some thoughts on your query in general:

1. An update with a single join of a table and view that generates the plan you posted indicates a very complex view!

2. That many inequality conditions are very challenging to handle with any kind of efficiency since you basically can't index the table(s) in any way that's useful to the optimizer. You'll end up with table scan(s) all over the place.

3. This looks like an effort to update the table where the data for the same entity differs between the table and the view. If what you want is for the table to look just like the view, why not just write the UPDATE statement without the conditions?








Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1453258
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse