Query take time to get result

1

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

2

Hi

Sometimes in my experience

  1. Scenario 1 = it could be data issue like below

Example:
Meaning data for all the previous months 10 million
this particular month 10 thousand

  1. Scenario 2 = Loading error
    Data = 10 million
    Error while loading at 10 thousand record
    Rest not loaded
    Only 10 thousand loaded

Please check
Thanks

3

Try moving the HAVING clause into the WHERE - and eliminate the GROUP BY completely. You are not using any aggregate functions - therefore you really don't need to group the data.

You also need to review the relationships of the tables and how they are being joined - are you sure you want to RIGHT OUTER JOIN the Customer_Vendor_Master$ table?

From CustomerMaster_1 As CustomerMaster_1_1 
Inner Join AR$ On CustomerMaster_1_1.Customer = AR$.Branch
Inner Join Customer_Master$ On AR$.Account = Customer_Master$.Customer 
Left Outer Join Phase_Master$ On AR$.Branch = Phase_Master$.Customer 
Left Outer Join CustomerMaster_1 On CustomerMaster_1.Customer = Customer_Vendor_Master$.VendorCustomerCode
right Outer Join Customer_Vendor_Master$ On AR$.Branch = Customer_Vendor_Master$.CustomerCode

Restructuring the FROM clause - I get the above. If we start with the Customer_Vendor_Master$ then everything else would be INNER JOIN or LEFT OUTER JOIN:

From Customer_Vendor_Master$
Inner Join AR$ On AR$.Branch = Customer_Vendor_Master$.CustomerCode
Inner Join CustomerMaster_1 cm2 On cm2.Customer = Customer_Vendor_Master$.VendorCustomerCode
Left Outer Join CustomerMaster_1 cm1 On cm1.Customer = AR$Branch
Left Outer Join Customer_Master$ On Customer_Master$.Customer = AR$.Account
Left Outer Join Phase_Master$ On Phase_Master$.Customer = AR$.Branch

Not sure if this works out the same - but it should as the HAVING clause essentially eliminates the RIGHT OUTER JOIN and also eliminates the LEFT OUTER JOIN for the Customer_Master$ table.

For the WHERE clause - it can be:

WHERE AR$.Year = 2018
AND AR$.Month = 1
AND Customer_Master$.Main_Category IN ('APMIP', 'TGMIP')

If you included the GROUP BY to remove duplicates - that generally indicates a missing relationship on one or more tables. If the duplicates are because you are only looking at 'header' values from the tables - then you can use DISTINCT instead of the GROUP BY. Before adding that though - verify the relationships on all tables are correct and that you really do need to remove duplicates. Adding either a DISTINCT or GROUP BY just to insure you don't get duplicates adds an additional sort that isn't needed - and will cause the query to take much longer than necessary.