Execution of view

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]

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R6y ago
Hi,
Try to refer below links :
https://stackoverflow.com/questions/3533757/how-to-execute-all-views-in-database-through-stored-procedure
0
Sourav Mukherjee7y ago
SQL Server Indexed views might be a great choice in this situation. Test the situation in non prod env first before directly implementing to production.
0
Suraj Kumar7y ago
Please check with the link

https://www.c-sharpcorner.com/article/sql-server-indexed-views/
0
Sreekanth Reddy7y ago
Apply index for your columns(not in view. Main base tables) and use the same join and try to fetch.
Might increase some sort of performance.

Thank you.
0
Suraj Kumar7y ago
You can check the execution plan and suggestion also by SQL Server management studio. It will help you. Check execution plan for both view and select queries statements. Try to create stored procedure of select and run and check the performance also.
0
Sreekanth Reddy7y ago
Can we try for indexed views and apply index for "Internal_Circuit_ID"
and Vendor_Name ?

Thank you.
0