Hi Expert,
i am using SQL since last two year and, below query is store in excel which is link with SQL server.
also using below query since last two year and i have never face any problem.
AR Table is the main table (around 5500 milion line item data), other table are master tables from which further information data comming.
every month i am uploading around 1 lack (100 milion) line data for preveious month in this table,
e.g. last data i have uploaded for jan-18 month,
and AR table having seperate column to filter year and month (you can see in query also), now my problem is, in below query getting result in just few second for 2017 year and 12 month, but when i change the filter parameter from 2017 to 2018 and in month 12 to 1 for extracting data of Jan-18 it take so much time, some time it take 30 mnts while previeous any period getting result in just 6 second.
because of this delay, excel query editor not able to display result in excel table and get timeout.
since last 15 days i am stuck in this but not able to find out problem, i have reset all dependent table, check all table 3 times but not done.
please help me
SELECT AR$.Year, AR$.Month, AR$.Account, Customer_Master$.Name, Customer_Master$.RG, Customer_Master$.Final_District, AR$.Branch, CustomerMaster_1_1.Name AS Farmer_Name,
Customer_Master$.Main_Category, Customer_Vendor_Master$.VendorCode AS [Dealer Vendor Code], Customer_Vendor_Master$.VendorCustomerCode AS [Dealer Code],
CustomerMaster_1.Name AS [Dealer Name], CustomerMaster_1_1.Village, CustomerMaster_1_1.Mandal, Phase_Master$.Phase, AR$.GL, AR$.FD_Total AS [Total Fall in due],
AR$.[OD_0-30] + AR$.[OD_31-60] + AR$.[OD_61-90] AS [Up to 90 Days], AR$.[OD_91-120] + AR$.[OD_121-150] + AR$.[OD_151-180] AS [91 to 180 Days], AR$.[OD_181-365] AS [181 to 365 Days],
AR$.OD_1to2Yr AS [1 to 2 Year], AR$.OD_2to3Yr AS [2 to 3 Year], AR$.OD_3YrMore AS [3 Year More], AR$.OD_Total AS [Total Overdue], AR$.Total_AR AS [Total AR]
FROM CustomerMaster_1 AS CustomerMaster_1_1 INNER JOIN
AR$ INNER JOIN
Customer_Master$ ON AR$.Account = Customer_Master$.Customer ON CustomerMaster_1_1.Customer = AR$.Branch LEFT OUTER JOIN
Phase_Master$ ON AR$.Branch = Phase_Master$.Customer LEFT OUTER JOIN
CustomerMaster_1 RIGHT OUTER JOIN
Customer_Vendor_Master$ ON CustomerMaster_1.Customer = Customer_Vendor_Master$.VendorCustomerCode ON AR$.Branch = Customer_Vendor_Master$.CustomerCode
GROUP BY AR$.Year, AR$.Month, AR$.Account, Customer_Master$.Name, Customer_Master$.RG, Customer_Master$.Final_District, AR$.Branch, AR$.FD_Total, AR$.[OD_0-30] + AR$.[OD_31-60] + AR$.[OD_61-90],
AR$.[OD_91-120] + AR$.[OD_121-150] + AR$.[OD_151-180], AR$.[OD_181-365], AR$.OD_1to2Yr, AR$.OD_2to3Yr, AR$.OD_3YrMore, AR$.OD_Total, AR$.Total_AR, Customer_Vendor_Master$.VendorCustomerCode,
CustomerMaster_1.Name, Customer_Vendor_Master$.VendorCode, Customer_Master$.Main_Category, CustomerMaster_1_1.Name, AR$.GL, CustomerMaster_1_1.Village, CustomerMaster_1_1.Mandal,
Phase_Master$.Phase
HAVING
(AR$.Year = 2018) AND (AR$.Month = 1) AND (Customer_Master$.Main_Category = N'APMIP') OR
(AR$.Year = 2018) AND (AR$.Month = 1) AND (Customer_Master$.Main_Category = N'TGMIP')