June 18, 2015 at 3:10 pm
Hi All,
Need expertise in making my view run fast. Please find the below Query(view) which i am referring to.
CREATE VIEW [dbo].[finalstatus_view]
AS
WITH ClaimFinalStatus ( CID,CREATEDATE,PlanID,status,[Order] )
AS(
--ran in 4 seconds
SELECT
C.CID,
C.CREATEDATE,
C.planid,
status,
DENSE_RANK() OVER(PARTITION BY CASE WHEN C.CID LIKE '%[ARMS]%'
THEN LTRIM(RTRIM(SUBSTRING(LTRIM(RTRIM(C.CID)),1,PATINDEX('%[^0-9]%',LTRIM(RTRIM(C.CID)))-1))) + LTRIM(RTRIM(C.PLANID))
WHEN C.CID NOT LIKE '%[ARMS]%'
THEN LTRIM(RTRIM(C.CID))+ LTRIM(RTRIM(C.PLANID)) END,c.planid
ORDER BY C.CREATEDATE DESC) AS 'Order'
FROM dbo.ABC c
where c.cid='%1419282888%'
)
SELECT CID,status,PlanID
FROM ClaimFinalStatus
WHERE [Order] = 1
Basically I am getting finalstatus of a CID , The above Query for one CID is running pretty fast in 3-6 seconds but this is view which we will join with other tables so i ran like the below it is running ~10 minutes. Can any one suggest how Can i tweak my Query in the view to run fast enough. Also ABC table is having 3.7 Million CID's and I have a Primary Key and clustered index on CID. I have changed the original table name and field names . Thanks any help can be appreciated .
select * from [dbo].[finalstatus_view]
where cid like '%141928888%'
----
please see below, We have a previous version of the view which is not populating correctly.. so i wrote the new version(above one which is pulling out correct but running very slow) . Please help me .Thanks
SELECT c.CID
FROM ABC c
WHERE ( c.status = 'PAID'
OR c.status = 'DENIED'
OR c.status = 'REVERSED'
)
--AND c.resubCID = ''
AND c.createdate = ( SELECT MAX(cinner.createdate)
FROM ABC cinner
WHERE cinner.CID LIKE
CASE
WHEN SUBSTRING(RTRIM(c.CID),LEN(RTRIM(c.CID))- 1, 2) LIKE '[ARMS]'
THEN SUBSTRING(RTRIM(c.CID),0,LEN(RTRIM(c.CID))- 1) + '%'
--when SUBSTRING(RTRIM(c.CID),LEN(RTRIM(c.CID)) - 2, 3) LIKE '[AR][1-9]M'
--then SUBSTRING(RTRIM(c.CID),0,LEN(RTRIM(c.CID))- 2) + '[%]'+'M'
--when SUBSTRING(RTRIM(c.CID),LEN(RTRIM(c.CID)) - 2, 2) LIKE '[AR]_'
--then SUBSTRING(RTRIM(c.CID),0,LEN(RTRIM(c.CID))- 2) + '%'
ELSE RTRIM(c.CID) + '%'
End
AND ( cinner.status = 'PAID'
OR cinner.status = 'DENIED'
OR cinner.status = 'REVERSED'
) --and CID ='14274622771A1M' -- '15006838462'--'14274622771A1M'
)
Thanks,
Kalyan
June 18, 2015 at 3:35 pm
A couple of thoughts:
1.) Any chance you can UPDATE the base table's CID field to be LTRIM(RTRIM(CID)) on an overnight job and no longer have to deal with those parts of the CASE statement?
2.) That CASE statement with PATINDEX is the most likely killer. How about adding a computed and persisted field to the table that implements that CASE statement, and then you can simplify the view to use it instead?
3.) The LIKE portion of the WHERE clause uses a large number of digits, but still needs leading and trailing % characters. This is another performance killer, so I'm wondering if that sequence of digits is always at the beginning or end, or is it that way because of the spaces that might be there (which might be solved by item 1 above) ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2015 at 7:37 am
Hi Steve,
Thanks for your reply . Please see my answers below.
1) This is a database for a third-party product where we cant make changes to the Database table structure. I.e we cannot load LTRIM(RTRIM(CID)) on an overnight job
2)As i told for step 1 we cannot create computed column as well in the table for CASE statement with PATINDEX .
3) The reason I kept '%141928888%' is there will be CID like 141928888S12 or 1419288881X2 or 14192888812A or 14192888812 or 141928888M121 . So we need to get the base CID which is 141928888 and adding with the planid then finding out the unique between them in the Dense rank ordering by create date desc to get the most recent status.
Can you/someone please suggest if i can rewrite the Query to my old version of the view which i mentioned in my First post(because my old view is runnning very fast when compared to the new one.
Thanks,
Kalyan.
June 19, 2015 at 7:46 am
dhanekulakalyan (6/19/2015)
3) The reason I kept '%141928888%' is there will be CID like 141928888S12 or 1419288881X2 or 14192888812A or 14192888812 or 141928888M121 . So we need to get the base CID which is 141928888 and adding with the planid then finding out the unique between them in the Dense rank ordering by create date desc to get the most recent status.
In that case you want
LIKE '141928888%', you don't need the leading wildcard and it's the leading wildcard which makes indexes useless.
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
June 19, 2015 at 8:42 am
GilaMonster (6/19/2015)
dhanekulakalyan (6/19/2015)
3) The reason I kept '%141928888%' is there will be CID like 141928888S12 or 1419288881X2 or 14192888812A or 14192888812 or 141928888M121 . So we need to get the base CID which is 141928888 and adding with the planid then finding out the unique between them in the Dense rank ordering by create date desc to get the most recent status.In that case you want
LIKE '141928888%', you don't need the leading wildcard and it's the leading wildcard which makes indexes useless.
Yep..... that's why I was asking about it. My guess is, that one change could seriously speed up that query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2015 at 8:55 am
Hi Gail,
I just gave one example 141928888M here thats the claimid field there are 3.7 claimids eg: 22141213888M or 33333356A123 something like that.
Thanks,
Kalyan
June 19, 2015 at 2:56 pm
dhanekulakalyan (6/19/2015)
Hi Gail,I just gave one example 141928888M here thats the claimid field there are 3.7 claimids eg: 22141213888M or 33333356A123 something like that.
Thanks,
Kalyan
I don't see how that answers the questions we asked... What does " there are 3.7 claimids ..." mean ? Something like a "claimid" doesn't generally have a fractional number of occurrences, so please go into considerably more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2015 at 12:49 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply