February 4, 2016 at 11:39 pm
Hi there,
I wrote a query to meet my logic but performance kills. Want to replace the outer apply in following query,
select ProdA.Productid, ProdB.ProductID
FROM BHSProd.dbo.ClaimPayment CP WITH (NOLOCK)
INNER JOIN BHSProd.dbo.Claim C WITH (NOLOCK) ON CP.ClaimID = C.ClaimID_PK
AND CP.IsHistory = 0
AND CP.StatusID <> 5105 --ADJ
AND ISNULL(CP.ApprovedAmount, 0) >= 0 --Added instead of the commented code at the last lines
AND C.RowState <> 'R'
AND C.SyntheticClaimID IS NOT NULL
INNER JOIN BHSProd.dbo.ClaimProviderDetail CPD WITH (NOLOCK) ON CPD.ClaimID = C.ClaimID_PK
AND CPD.RowState <> 'R'
AND CPD.ProviderTypeID = 731 -- Rendering (Claim --> Provider Type)
INNER JOIN BHSProd.dbo.ClaimMemberDetail CMD WITH (NOLOCK) ON CMD.ClaimID = C.ClaimID_PK
AND CMD.LineOfBusinessID = @LOBID
INNER JOIN BHSProd.dbo.Member M WITH (NOLOCK) ON M.MemberID_PK = CMD.MemberID
AND M.RowState <> 'R'
INNER JOIN BHSProd.dbo.[User] U WITH (NOLOCK) ON U.UserID = ISNULL(CP.ModifiedBy, CP.AddBy)
LEFT JOIN vW_CLID CLID ON CLID.ClaimID = C.ClaimID_PK --CP.ClaimID
LEFT JOIN VW_ClaimRefundAmt ClaimRefundAmtON ClaimRefundAmt.ClaimID = C.ClaimID_PK
OUTER APPLY (
SELECT TOP 1 BP.ProductID AS ProductID
FROM BHSProd.dbo.BenefitPlan BP
INNER JOIN BHSProd.dbo.MemberCoverage MCov WITH (NOLOCK) ON MCov.BenefitPlanID = BP.BenefitPlanID
AND 1 = (
CASE
WHEN ISNULL(MCov.TerminationDate, '') <> ''
AND CONVERT(DATE, C.FirstDateOfService, 101) >= CONVERT(DATE, MCov.EffectiveDate, 101)
AND CONVERT(DATE, C.FirstDateOfService, 101) <= CONVERT(DATE, MCov.TerminationDate, 101)
THEN 1
WHEN ISNULL(MCov.TerminationDate, '') = ''
AND CONVERT(DATE, C.FirstDateOfService, 101) >= CONVERT(DATE, MCov.EffectiveDate, 101)
THEN 1
ELSE 0
END
)
WHERE MCov.MemberID = CMD.MemberID
) ProdA
OUTER APPLY (
SELECT TOP 1 BP.ProductID AS ProductID
FROM BHSProd.dbo.BenefitPlan BP WITH (NOLOCK)
INNER JOIN BHSProd.dbo.membercoverage MC WITH (NOLOCK) ON MC.BenefitPlanID = BP.BenefitPlanID
--AND MC.RowState <> 'R'
AND MC.MemberID = CMD.MemberID
ORDER BY ISNULL(MC.TerminationDate, MC.EffectiveDate) DESC
) AS ProdB
February 5, 2016 at 2:10 am
Suggestions:
1) It's a performance question, so you'd better include an execution plan in order to let people understand where the problem lies. Post the actual execution plan, not the extimated plan if possible.
2) Post table and index scripts. Without knowing your database, we cannot help.
3) Don't use NOLOCK: it doesn't boost your query performance, it just allows dirty reads on your data with interesting side effects such as reading rows multiple times or skipping them altogether.
-- Gianluca Sartori
February 5, 2016 at 2:12 am
Another suggestion: don't convert datatypes if you don't absolutely need to. Those CONVERT(date,something,101) will prevent you from using indexes on the columns.
-- Gianluca Sartori
February 5, 2016 at 2:12 am
Some observations to add to SpaghettiDBA's comments:
1.Remove any unnecessary tables from the FROM list. Begin with the two left-joined views. Since they are left joins, they cannot restrict the output, they can only change the number of rows returned – by introducing dupes.
2.Remove functions from filters and join criteria unless they are known to be SARGable: use CAST(datetime column AS DATE) instead of CONVERT.
3.The two APPLY blocks are similar, in fact the more complex one is more or less the same as the simpler one, but with an extra filter. Change the simpler one to a CROSS APPLY because you won’t eliminate results from the other.
You should end up with something like this, after a first pass of simple improvements:
USE BHSProd
GO
SELECT
ProdA.Productid,
ProdB.ProductID
FROM dbo.ClaimPayment CP
INNER JOIN dbo.Claim C
ON CP.ClaimID = C.ClaimID_PK
AND CP.IsHistory = 0
AND CP.StatusID <> 5105 --ADJ
--AND ISNULL(CP.ApprovedAmount, 0) >= 0 --Added instead of the commented code at the last lines
AND (CP.ApprovedAmount >= 0 OR CP.ApprovedAmount IS NULL)--Added instead of the commented code at the last lines
AND C.RowState <> 'R'
AND C.SyntheticClaimID IS NOT NULL
INNER JOIN dbo.ClaimProviderDetail CPD
ON CPD.ClaimID = C.ClaimID_PK
AND CPD.RowState <> 'R'
AND CPD.ProviderTypeID = 731 -- Rendering (Claim --> Provider Type)
INNER JOIN dbo.ClaimMemberDetail CMD
ON CMD.ClaimID = C.ClaimID_PK
AND CMD.LineOfBusinessID = @LOBID
INNER JOIN dbo.Member M
ON M.MemberID_PK = CMD.MemberID
AND M.RowState <> 'R'
INNER JOIN dbo.[User] U
ON (U.UserID = CP.ModifiedBy OR U.UserID = CP.AddBy) --ISNULL(CP.ModifiedBy, CP.AddBy)
--LEFT JOIN vW_CLID CLID
--ON CLID.ClaimID = C.ClaimID_PK --CP.ClaimID
--LEFT JOIN VW_ClaimRefundAmt ClaimRefundAmt
--ON ClaimRefundAmt.ClaimID = C.ClaimID_PK
OUTER APPLY (
SELECT TOP 1 BP.ProductID
FROM dbo.BenefitPlan BP
INNER JOIN dbo.MemberCoverage MCov
ON MCov.BenefitPlanID = BP.BenefitPlanID
AND MCov.MemberID = CMD.MemberID -- outer reference
AND CAST(C.FirstDateOfService AS DATE) >= CAST(MCov.EffectiveDate AS DATE)
AND (MCov.TerminationDate IS NULL OR CAST(C.FirstDateOfService AS DATE) <= CAST(MCov.TerminationDate AS DATE))
) ProdA
CROSS APPLY (
SELECT TOP 1 BP.ProductID
FROM dbo.BenefitPlan BP
INNER JOIN dbo.membercoverage MC
ON MC.BenefitPlanID = BP.BenefitPlanID
AND MC.MemberID = CMD.MemberID -- outer reference
--ORDER BY ISNULL(MC.TerminationDate, MC.EffectiveDate) DESC
ORDER BY MC.TerminationDate DESC, MC.EffectiveDate DESC
) ProdB
EDIT: Corrected code, thanks Hugo.
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
February 5, 2016 at 2:17 am
Tuning is never simple, and especially tuning a complex query such as the one you posted can be a challenge.
You say that you want to replace the OUTER APPLY. Why? How have you determined that this is the root cause of your performance problems? Also, there are two outer apply's in the query; I *think* you are looking at the first, but I don't like to assume.
I see two object names that start with vW_; that probably means they are views - not unlikely that they are the problem. Are these views regular views or indexed views? If they are indexed views, then what edition of SQL Server are you running?
Are all other object names really tables?
Are you using cross-database logic? If no, then why do you use three-part naming for most object names?
I see several bad practices in different places in the query. The first outer apply has a TOP 1 without ORDER BY, which means that your results are undefined. In the outer apply, you do date conversions that are known to cripple SQL Server's ability to use indexes. Both in the apply and in the main query, you have ISNULL calls; many of them are totally useless but will impact performance; some actually do something besides lowering performance, but might be replaceable.
And I hope that you are aware that NOLOCK is not a magic go-faster switch. It will give you a tiny performance advantage for having to take far less locks, and it might reduce blocking, but it can also result in returning incorrect data - not just uincommitted rows; you can also read a single row twice, skip rows, or get a run-time error - so you better build retry logic around that.
You use OUTER APPLY twice, and the only two columns included in the SELECT list come from those two sources. Do you really want NULL, NULL to be in your resultset? Are you sure you do not want CROSS APPLY instead?
The FROM list also contains a lot of object references that are never used. Maybe I am overlooking a reference somewhere, possible. But for instance, the [User] table (or view) appears to be mostly useless. If I assume that UserID is unique in the table (and again, I do not like to assume), then this is effectively just an EXISTS test - so write it that way to make the query easier to understand. Also, if you have foreign key constraints on the audit column (and if no, then why not), then even this EXISTS test is utterly useless. In many cases SQL Server will understand this and eliminate the useless test from the plan, but once the query becomes too complex SQL Server can start to miss such opportunities. And in this case the ISNULL expression by itself is probably sufficient to eliminate any chance of getting that simplicifation. For the two views, it is even worse, they are joined using outer joins - so they will never cause any row to be eliminated, but they might cause rows to be returned multiple times. I'm assuming that has not happened yet (or you would have posted for help with removing the duplicates instead of performance). But SQL Server almost certainly is unable to deduct from the view and table definitions that this can never happen, so it will have to include evaluation for the views just to verify that there are no multiple matching rows.
Perhaps the above helps you a few steps in the right direction. If you need more help, then you will have to start by providing more information. In order to help you, we need the following:
* The defintion of all objects used in the query. For tables that is the CREATE TABLE statement. (Please include all constraints and indexes, but feel free to eliminate columns that play no role in the query). For views, that is the CREATE VIEW statement (again, feel free to eliminate unused columns), plus indexes if it's an indexed view - and if views are used, then make sure that tables referenced by that view are also included in the script.
* The actual execution plan. (You can get this by executing the query in SQL Server Management Studio with the option to include the actual plan turned on; then switch to the execution plan pane, right-click on some whitespace in the plan and select the option to save the plan as a .sqlplan file, which you can then upload here).
Finally, I'd be interested to know how long exactly the query runs, and how fast you need it to be.
EDIT: Wow, that's what I get for typing long replies. There were no replies when I started typing, now I am mostly repeating what others said.
EDIT 2: I forgot to add that I know that posting all the information I requested is a lot of work, but without it we simply cannot help you. Once you do post it, I must make the disclaimer that you are not guaranteed to get help. Most people here do this kind of work for a liviing. Spending some of our time to give help for free is okay, we like to do that. But if the problem you post is so big that it will take us a full day, it's hard to justify spending that amount of time here instead of on billable work. So while there definitely is a chance that you will receive help, it is not certain.
February 5, 2016 at 2:23 am
ChrisM@Work (2/5/2016)
ORDER BY MC.TerminationDate, MC.EffectiveDate DESC
I didn't go over all your modifications, but this one just struck my eye.
I think you intended this to be:
ORDER BY MC.TerminationDate DESC, MC.EffectiveDate DESC
February 5, 2016 at 2:26 am
Hugo Kornelis (2/5/2016)
ChrisM@Work (2/5/2016)
ORDER BY MC.TerminationDate, MC.EffectiveDate DESCI didn't go over all your modifications, but this one just struck my eye.
I think you intended this to be:
ORDER BY MC.TerminationDate DESC, MC.EffectiveDate DESC
You're absolutely right Hugo, thank you for pointing this out.
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
February 5, 2016 at 7:56 am
Hugo Kornelis (2/5/2016)
ChrisM@Work (2/5/2016)
ORDER BY MC.TerminationDate, MC.EffectiveDate DESCI didn't go over all your modifications, but this one just struck my eye.
I think you intended this to be:
ORDER BY MC.TerminationDate DESC, MC.EffectiveDate DESC
This is a breaking change. The results of this change are different from the original query.
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY mc.TerminationDate DESC, mc.EffectiveDate DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 5, 2016 at 8:02 am
drew.allen (2/5/2016)
Hugo Kornelis (2/5/2016)
ChrisM@Work (2/5/2016)
ORDER BY MC.TerminationDate, MC.EffectiveDate DESCI didn't go over all your modifications, but this one just struck my eye.
I think you intended this to be:
ORDER BY MC.TerminationDate DESC, MC.EffectiveDate DESC
This is a breaking change. The results of this change are different from the original query.
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY mc.TerminationDate DESC, mc.EffectiveDate DESC
Drew
This is a fixing change, and is in fact the original code I posted:
SELECT *
FROM (
VALUES
(CAST(NULL AS DATE),CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT *
FROM (
VALUES
(CAST(NULL AS DATE),CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY mc.TerminationDate, mc.EffectiveDate DESC
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
February 5, 2016 at 9:04 am
No, it's still a breaking change.
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01'),
('2016-12-31', '2016-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT *
FROM (
VALUES(CAST(NULL AS DATE), CAST('2016-01-01' AS DATE)),
('2015-12-31', '2015-01-01'),
('2016-12-31', '2016-01-01')
) AS mc(TerminationDate, EffectiveDate)
ORDER BY mc.TerminationDate, mc.EffectiveDate DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 5, 2016 at 9:19 am
Yes, you are right. I had overlooked that. Both rewrites of the ORDER BY are incorrect, it needs to be kept as it was (with the ISNULL or with a COALESCE which in this case is the same).
A more significant rewrite could perhaps fix this in some way, but that requires a lot more information then we currently have.
February 5, 2016 at 9:39 am
Hugo Kornelis (2/5/2016)
Yes, you are right. I had overlooked that. Both rewrites of the ORDER BY are incorrect, it needs to be kept as it was (with the ISNULL or with a COALESCE which in this case is the same).A more significant rewrite could perhaps fix this in some way, but that requires a lot more information then we currently have.
It has such a small impact on performance, certainly compared with other gotchas in the code, that it's probably a red herring anyway. Thanks for correcting and pointing this out Drew.
DROP TABLE #Sample
SELECT n, EffectiveDate, TerminationDate = DATEADD(HOUR,ABS(CHECKSUM(NEWID()))%1000,EffectiveDate)
INTO #Sample
FROM (
SELECT n, EffectiveDate = DATEADD(HOUR,0-n,GETDATE())
FROM (
SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) h (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) i (n)
) d
) e;
WITH Updater AS (SELECT TOP(50000) * FROM #Sample ORDER BY NEWID())
UPDATE Updater SET TerminationDate = NULL;
CREATE CLUSTERED INDEX ix_Stuff ON #Sample (TerminationDate, EffectiveDate)
-------------------------------------------------------------------------------------------------------
SELECT mc.*
INTO #TEMP10
FROM #Sample mc
SELECT mc.*
INTO #TEMP1
FROM #Sample mc
ORDER BY ISNULL(mc.TerminationDate, mc.EffectiveDate) DESC
SELECT mc.*
INTO #TEMP2
FROM #Sample mc
CROSS APPLY (SELECT OrderBy = MAX(OrderBy) FROM (VALUES (mc.TerminationDate),(mc.EffectiveDate)) d (OrderBy)) o
ORDER BY o.OrderBy DESC
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply