Execution of view

Question

Hi All,

 

 

In Backed we have had a view which contains left join query which gives 1lakh records. This left join query result is stored in the view. 

 

Now we are retrieving the top 100 records from the view by giving some filterartions. So it is taking 1minute time.

 

In our application we are getting operation time out exception we are getting.

 

Below is the view  

-----------------------

CREATE VIEW [dbo].[v_Circuit_locator_data]

AS

SELECT dbo.FFCL_DATA.Vendor_Name, dbo.FFCL_DATA.BAN, dbo.FFCL_DATA.ACNA, dbo.FFCL_DATA.LATA, dbo.FFCL_DATA.State, dbo.FFCL_DATA.Bill_Date,

dbo.FFCL_DATA.Stripped_EC_Circuit_ID, dbo.FFCL_DATA.Stripped_EC_Circuit_ID AS Stripped_Vendor_Circuit_ID, dbo.FFCL_DATA.Internal_Circuit_ID,

dbo.FFCL_DATA.Service_Established_Date AS Service_Est_Date, dbo.FFCL_DATA.CFA AS CFA, dbo.FFCL_DATA.Mileage AS Mileage,

dbo.FFCL_DATA.Charge_Amount AS Charge_Amount, dbo.ffcl_claims.QwestClaimNumber, dbo.ffcl_claims.ClaimType, DATEDIFF(m, GETDATE(),

dbo.FFCL_DATA.Term_agreement_end_date) AS Term_Remaining, dbo.FFCL_DATA.BAN_Category1 AS BAN_Category1

FROM dbo.FFCL_DATA LEFT OUTER JOIN

dbo.ffcl_claims ON dbo.FFCL_DATA.Stripped_EC_Circuit_ID = dbo.ffcl_claims.CircuitID AND dbo.FFCL_DATA.Vendor_Name = dbo.ffcl_claims.Carrier

 

 

Below is the query for calling that view records

-------------------------------------------------------------

 SELECT top 100 Vendor_Name, BAN, ACNA, LATA, State, Bill_Date, Stripped_EC_Circuit_ID, Stripped_Vendor_Circuit_ID,

Internal_Circuit_ID, MIN(Service_Est_Date) AS Service_Est_date, MAX(Mileage) AS Mileage,

SUM(Charge_Amount) AS Charge_Amount, QwestClaimNumber, ClaimType, Term_Remaining,Stripped_EC_Circuit_ID AS Circuit_ID,

BAN_Category1 FROM v_Circuit_locator_data WHERE Internal_Circuit_ID like'%DS1%'group by Vendor_Name,

BAN, ACNA, LATA, State, Bill_Date, Stripped_EC_Circuit_ID, Stripped_Vendor_Circuit_ID, Internal_Circuit_ID, Service_Est_Date,

Mileage, QwestClaimNumber, ClaimType, Term_Remaining,Stripped_EC_Circuit_ID, BAN_Category1 order by bill_date desc, vendor_name asc

 

-----------------

 

Can we make the above quries response fast. 

 

Please post your answers to do optimization