deepakagarwalathome (10/1/2012)
...There are no indexes on any of the tables...
This might have something to do with it. The first sentence of an excellent indexing article[/url] reads "Indexes are fundamental to database design".
Your query can be made more efficient by changing the structure slightly:
SELECT
COUNT(*) FRMP_RecordCount ,
b.RECORD_ID FRMP_Record_Id ,
b.METER_ID FRMP_Meter_Id ,
CAST(a.FromDate AS DATE) FRMP_FromDate ,
CAST(a.ToDate AS DATE) FRMP_ToDate ,
CAST(b.Billing_Start_Date AS DATE) FRMP_Billing_Start_Date ,
CAST(b.Billing_End_Date AS DATE) FRMP_Billing_End_Date ,
NetAmount FRMP_NetAmount ,
a.Party FRMP_Party ,
b.PROVIDER FRMP_Provider
FROM (
SELECT
RECORD_ID ,
METER_ID ,
BILLING_START_DATE ,
BILLING_END_DATE ,
NET_AMOUNT AS NetAmount ,
PROVIDER,
SUMnet_AMOUNT = SUM(net_AMOUNT) OVER(PARTITION BY METER_ID, BILLING_START_DATE, BILLING_END_DATE, Provider)
FROM NETWORK_VALIDATION..evd_NonNBV_ForChecking_BAU
) b
LEFT JOIN NETWORK_VALIDATION..MSATS_DATA_BAU_FINAL a
ON a.NMI = b.METER_ID
AND b.BILLING_START_DATE BETWEEN a.FromDate AND a.ToDate
AND b.billing_end_date BETWEEN a.FromDate AND a.ToDate
AND a.Role = 'FRMP'
WHERE b.SUMnet_AMOUNT <> 0
GROUP BY
b.RECORD_ID ,
b.METER_ID ,
CAST(a.FromDate AS DATE) FRMP_FromDate ,
CAST(a.ToDate AS DATE) FRMP_ToDate ,
CAST(b.Billing_Start_Date AS DATE) FRMP_Billing_Start_Date ,
CAST(b.Billing_End_Date AS DATE) FRMP_Billing_End_Date ,
b.NetAmount ,
a.Party ,
b.PROVIDER
But it's no substitute for correct indexing. The tables are only half the story.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden