May 7, 2013 at 10:28 pm
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.
May 8, 2013 at 1:18 am
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.
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
May 8, 2013 at 8:20 am
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.
May 8, 2013 at 8:23 am
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?
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
May 9, 2013 at 8:22 am
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
May 9, 2013 at 6:50 pm
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.
May 14, 2013 at 2:15 pm
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
May 14, 2013 at 2:31 pm
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).
May 15, 2013 at 12:30 pm
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, '')
May 15, 2013 at 1:34 pm
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
May 15, 2013 at 3:41 pm
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, '') . . .
. . .
. . .
. . .
If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.
create table MYTable
(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);
create index ix_pid_xupdate on MYTable ( pid, xupdate );
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 15, 2013 at 8:40 pm
Eric M Russell (5/15/2013)
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, '') . . .
. . .
. . .
. . .
If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.
create table MYTable(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);
create index ix_pid_xupdate on MYTable ( pid, xupdate );
Thanks. Few questions before i try:
i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?
ii) What changes do i need to make in my update statement?
Thanks
May 16, 2013 at 7:26 am
curious_sqldba (5/15/2013)
Thanks. Few questions before i try:i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?
ii) What changes do i need to make in my update statement?
Thanks
The CHECKSUM function returns a hashed integer value computed over a list of columns or expressions. I'm thinking you would include the full list of non-key columns for which you are comparing.
select
checksum( 200, 'abc', '2013/05/10' ) checksum_a,
checksum( 200, 'xyz', '2013/05/10' ) checksum_b;
checksum_a checksum_b
----------- -----------
-713813704 -713651384
If you are comparing two different tables, then when adding the checksum column, the column list and datatypes should be specified exactly the same to insure successful matching. I don't know what maximum number of columns are supported in the list, but it's far more than 20 at least.
In your JOIN or WHERE clause, you would still be doing a seperate exact match on the key column(s) like PID or ID. So if you want to write a query that compares tables A and B, both keyed on PID, and return those rows for which non-key columns are different, it would be something like this:
select *
from A
join B on B.PID = A.PID and B.xchecksum != A.xchecksum;
For that reason, the index on each table would be on PID and xchecksum.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 16, 2013 at 9:19 am
curious_sqldba (5/15/2013)
Eric M Russell (5/15/2013)
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, '') . . .
. . .
. . .
. . .
If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.
create table MYTable(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);
create index ix_pid_xupdate on MYTable ( pid, xupdate );
Thanks. Few questions before i try:
i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?
ii) What changes do i need to make in my update statement?
Thanks
Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...
May 16, 2013 at 9:21 am
There's a very good chance that the smart folks here could eliminate the view from the query, if they could see the view definition.
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply