Hi Gail

Thanks for your response.

I don't know how would the table definition help but here they are :-

evd_NonNBV_ForChecking_BAU

([METER_ID] [varchar](10) NOT NULL,

[BILLING_START_DATE] [smalldatetime] NOT NULL,

[BILLING_END_DATE] [smalldatetime] NOT NULL,

[RECORD_ID] [int] IDENTITY(1,1) NOT NULL,

[NET_AMOUNT] [decimal](15, 2) NOT NULL,

[PROVIDER] [varchar](10) NOT NULL

)

MSATS_DATA_BAU_FINAL

([SequenceNumber] [numeric](18, 0) NULL,

[CreationDate] [datetime] NULL,

[MaintenanceDate] [datetime] NULL,

[RowStatus] [varchar](5) NULL,

[FromDate] [datetime] NULL,

[ToDate] [datetime] NULL,

[NMI] [char](10) NULL,

[Party] [varchar](30) NULL,

[Role] [varchar](20) NULL,

[CATS_FILE_ID] [int] NULL

)

There are no indexes on any of the tables

The execution plans for both options (one with temp table and the other with derived table) are attached herewith for your perusal. Also, attached is the spreadsheet showing the statistics and result output for the option with temp table. Will send the same when the query finishes execution with the derived tables.

Hope this would you in determining what is causing this inordinate delay in the query execution with the derived tables.

P.S The attachment has now (at 1.00 pm AEST) been updated to include the results and statistics for the option with derived tables as well.

This option took 75 minutes against less than a minute with temp table!!!!!!!!!!!!!!!!!!!!!

Best regards

Deepak