Hi All,

Need expertise in making how i can make 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%'

Thanks,

Kalyan

 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'                                 
                          )

Please avoid double posts, especially to none related forums, like here for SSRS

https://social.msdn.microsoft.com/Forums/en-US/91094325-746e-4667-93d4-793d6491975f/sql-view-running-slow-with-simple-query?forum=transactsql