Large Fact Table Query Optimization using (ROW_NUMBER() OVER (PARTITION BY) ) 1

BTrees · Dec 30, 2018

Hi All,
I have the following query,it takes more than 10 minutes. How can it be optimized. please help. thanks

DECLARE @InitDt AS date
SELECT @InitDt = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-16, 0)
;with cte as
(
SELECT ROW_NUMBER() OVER (PARTITION BY dim.Act_ID , dt.YEAR_NUMBER, dt.MONTH_OF_YEAR_NUMBER ORDER BY dt.FULL_DATE DESC) rn
,dim.Act_id, dim.Mid
, ft.Amount
,dt.YEAR_NUMBER
, dt.MONTH_OF_YEAR_NUMBER
, dt.FULL_DATE
, ft.END_DATE
FROM dbo.FACT_Table ft
JOIN DIM_Table dim ON ft.DIMID_ACCOUNT = dim.DIMID_ACCOUNT
JOIN DIM_DATE dt ON dt.FULL_DATE BETWEEN ft.BEGIN_DATE AND ft.END_DATE
WHERE dt.FULL_DATE >= @InitDt
)
SELECT *
FROM cte c
where rn = 1
---------------
The fact table has huge amount of data. The purpose of this query to retrieve only month-end/latest amount for each Act_Id, for the last 16 months.
The dim-date and Fact has no pk-fk relationship
The indexes on tables are as follows
- Dim_date (unique,Non-clustered) on FULL_DATE, 2- Dim_Date (Pk,Unique, clustered, asc) on DimId date (this dimid_date is being used for lookup)
-FACT_Table( Non-Unique, Non-Clustered) on BEGIN_DATE and END_DATE
-DIM_table(uniquee non-clustered on Act_id (with few other fields of the table).
Hope this will help in understdanding the scenareo. ( Please see latest query plan attached , after query execution)

fredericofonseca · Jan 2, 2019

main optimization will to change (or add a new index) index DIM_DATE.AK_DIM_DATE_FULL_DATE to include YEAR_NUMBER and MONTH_OF_YEAR_NUMBER.
this in order to avoid the expensive key lookup.

as for the query itself - without knowing the data I can only suggest the following - may or not work but will at least I hope guide you on a slightly different path.
It may even be the case that the basecte below should be added to a temp table instead of a CTE.
And it may also be that the join to dimdate should instead be a outer apply ... select top 1 dim date.

Code:

with basecte as
(
    SELECT  ROW_NUMBER() OVER (PARTITION BY dda.ACCOUNT_ID , convert(varchar(6), fddaa.PERIOD_END_DATE, 112) ORDER BY fddaa.PERIOD_END_DATE DESC) row_num
          , dda.ACCOUNT_ID
          , dda.MEMBERSHIP_ID_CURRENT
          , fddaa.ACCOUNT_BALANCE_AMOUNT
          , fddaa.PERIOD_BEGIN_DATE
          , fddaa.PERIOD_END_DATE
          , dda.[ACCOUNT_SUB_CLASS_CODE]
          , Case 
            When dda.ACCOUNT_SUB_CLASS_CODE IN ('ck-00C133', 'Ck-00C134', 'CML-00Y880','CML-00Y881','CML-00Y882','CML-00Y883') then 'Y'
            Else 'N'
            End as Syndicated_Account_YN   

    FROM dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
    JOIN dbo.DIM_DEPOSIT_ACCOUNT dda ON fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT
 
     WHERE fddaa.PERIOD_END_DATE  >= @StartDate
           AND dda.DIMID_DEPOSIT_ACCOUNT <> -1
          -- AND dda.ACCOUNT_CLOSED_DATE = '2999-12-31'
           AND  dda.ACCOUNT_SUB_CLASS_CODE NOT IN ('ck-00C130','ck-00C137')
       
)
,cte as 
(

    SELECT  ROW_NUMBER() OVER (PARTITION BY basecte.ACCOUNT_ID , dd.YEAR_NUMBER, dd.MONTH_OF_YEAR_NUMBER ORDER BY dd.FULL_DATE DESC) row_num
          , basecte.ACCOUNT_ID
          , basecte.MEMBERSHIP_ID_CURRENT
          , basecte.ACCOUNT_BALANCE_AMOUNT AS 'Deposit Balance'
          -- ,dd.YEAR_NUMBER
          --, dd.MONTH_OF_YEAR_NUMBER
          --, dd.MONTH_SHORT_NAME
          , dd.FULL_DATE AS 'TRANSACTION_POSTING_DATE'
          , basecte.PERIOD_END_DATE as 'Period End Date'
          , basecte.[ACCOUNT_SUB_CLASS_CODE]
          , basecte.Syndicated_Account_YN   

    FROM basecte
    JOIN dbo.DIM_DATE dd ON dd.FULL_DATE BETWEEN basecte.PERIOD_BEGIN_DATE AND basecte.PERIOD_END_DATE
 
 WHERE basecte.row_num = 1
)
SELECT c.ACCOUNT_ID
     , c.[Deposit Balance]
     --, c.YEAR_NUMBER, c.MONTH_OF_YEAR_NUMBER, c.MONTH_SHORT_NAME, 
    , c.TRANSACTION_POSTING_DATE
    , c.[Period End Date]
    , c.MEMBERSHIP_ID_CURRENT
    , c.Syndicated_Account_YN
    , c.ACCOUNT_SUB_CLASS_CODE
FROM cte c
       where row_num = 1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

BTrees · Jan 2, 2019

Thanks Fred, I tried it but the query execution. time is same. Further can you please hide/del this original query?

fredericofonseca · Jan 2, 2019

what have you tried? I posted 4 possible things that may be required and a combination of them should work.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

BTrees · Jan 3, 2019

Hi Fred,
I added a new index. Didn't find much difference with existing query. Further here is temp table @tbk for basecte. I was struggling to use outer apply (top 1) with 2nd CTE "cte" as defined in the query in your previous msg). Can you please help achieving this.
many thanks.

DECLARE @StartDate AS date
SELECT @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-14, 0)
declare @tbl table
(
ACCOUNT_ID varchar(50)
,MEMBERSHIP_ID_CURRENT varchar(50)
,[Deposit_Balance] decimal(18, 2)
,PERIOD_BEGIN_DATE date
, [Period End Date] date
, ACCOUNT_SUB_CLASS_CODE varchar(50)
,Syndicated_Account_YN varchar(1)

)

insert into @tbl
( ACCOUNT_ID, MEMBERSHIP_ID_CURRENT , [Deposit_Balance] ,PERIOD_BEGIN_DATE ,[Period End Date] , ACCOUNT_SUB_CLASS_CODE , Syndicated_Account_YN )
select ACCOUNT_ID
,MEMBERSHIP_ID_CURRENT
,ACCOUNT_BALANCE_AMOUNT
,PERIOD_BEGIN_DATE
,PERIOD_END_DATE
,[ACCOUNT_SUB_CLASS_CODE]
,Syndicated_Account_YN from

( SELECT ROW_NUMBER() OVER (PARTITION BY dda.ACCOUNT_ID , convert(varchar(6), fddaa.PERIOD_END_DATE, 112) ORDER BY fddaa.PERIOD_END_DATE DESC) row_num
, dda.ACCOUNT_ID
, dda.MEMBERSHIP_ID_CURRENT
, fddaa.ACCOUNT_BALANCE_AMOUNT
, fddaa.PERIOD_BEGIN_DATE
, fddaa.PERIOD_END_DATE
, dda.[ACCOUNT_SUB_CLASS_CODE]
, Case
When dda.ACCOUNT_SUB_CLASS_CODE IN ('ck-00C133', 'Ck-00C134', 'CML-00Y880','CML-00Y881','CML-00Y882','CML-00Y883') then 'Y'
Else 'N'
End as Syndicated_Account_YN

FROM dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
JOIN dbo.DIM_DEPOSIT_ACCOUNT dda ON fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT

WHERE fddaa.PERIOD_END_DATE >= @StartDate
AND dda.DIMID_DEPOSIT_ACCOUNT <> -1
-- AND dda.ACCOUNT_CLOSED_DATE = '2999-12-31'
AND dda.ACCOUNT_SUB_CLASS_CODE NOT IN ('ck-00C130','ck-00C137')
)a where row_num = 1
select * from @tbl

fredericofonseca · Jan 3, 2019

before I post new code could you please try and answer the following.

1 - post the new index you created
2 - post the explain plan for the query after you created the index - if it is not using it or if it is still doing a key lookup then its the wrong index.

3 - does the PERIOD_BEGIN_DATE and PERIOD_END_DATE encompass more than 1 month or are they always within the same month
this is very important as if it does go through more than 1 calendar month the query I posted before is incorrect and I do need to add an extra bit of code to cater for it.
Note that if it does go through 2 month a single record from the fact table will show up on your output twice, once for each year/month.

4 - give us the output of the following query
select count(*)
from
(select distinct fddaa.PERIOD_BEGIN_DATE, fddaa.PERIOD_END_DATE --- EDIT to fix names
FROM dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
JOIN dbo.DIM_DEPOSIT_ACCOUNT dda ON fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT

WHERE fddaa.PERIOD_END_DATE >= @StartDate
) t

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

fredericofonseca · Jan 3, 2019

try this
untested and there may be typo errors but should perform - unless your server is very low spec,

Code:

if object_id('tempdb..#fact_account') is not null
drop table #fact_account;


-- select base fact records onto a temp table - should be around 8million rows 
-- as such using a table variable is not feasible - those should have a max of 1-2 k records

select *

into #fact_account
from (
SELECT  ROW_NUMBER() OVER (PARTITION BY dda.ACCOUNT_ID , convert(varchar(6), fddaa.PERIOD_END_DATE, 112) ORDER BY fddaa.PERIOD_END_DATE DESC) row_num
          , dda.ACCOUNT_ID
          , dda.MEMBERSHIP_ID_CURRENT
          , fddaa.ACCOUNT_BALANCE_AMOUNT
          , fddaa.PERIOD_BEGIN_DATE
          , fddaa.PERIOD_END_DATE
          , dda.[ACCOUNT_SUB_CLASS_CODE]
          , Case 
            When dda.ACCOUNT_SUB_CLASS_CODE IN ('ck-00C133', 'Ck-00C134', 'CML-00Y880','CML-00Y881','CML-00Y882','CML-00Y883') then 'Y'
            Else 'N'
            End as Syndicated_Account_YN   
    FROM dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
    JOIN dbo.DIM_DEPOSIT_ACCOUNT dda ON fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT
 
     WHERE fddaa.PERIOD_END_DATE  >= @StartDate
           AND dda.DIMID_DEPOSIT_ACCOUNT <> -1
          -- AND dda.ACCOUNT_CLOSED_DATE = '2999-12-31'
           AND  dda.ACCOUNT_SUB_CLASS_CODE NOT IN ('ck-00C130','ck-00C137')
) t
where t.row_num = 1

 create clustered index #fact_account_ix1 on #fact_account
    (PERIOD_BEGIN_DATE
    , PERIOD_END_DATE
    )

 if object_id('tempdb..#base_dates') is not null
    drop table #base_dates;

 select distinct PERIOD_BEGIN_DATE
               , PERIOD_END_DATE
 into #base_dates
 FROM fact_account

  if object_id('tempdb..#work_dates') is not null
    drop table #work_dates

    select t.PERIOD_BEGIN_DATE
         , t.PERIOD_END_DATE
         , t.FULL_DATE
         , t.YEAR_NUMBER
         , t.MONTH_OF_YEAR_NUMBER
         , t.MONTH_SHORT_NAME

    into #work_dates
    from (
    select bd.PERIOD_BEGIN_DATE
         , bd.PERIOD_END_DATE
         , dd.FULL_DATE
         , dd.YEAR_NUMBER
         , dd.MONTH_OF_YEAR_NUMBER
         , dd.MONTH_SHORT_NAME
         , ROW_NUMBER() OVER (PARTITION BY dd.YEAR_NUMBER, dd.MONTH_OF_YEAR_NUMBER ORDER BY dd.FULL_DATE DESC) row_num
    from #base_dates bd
    JOIN dbo.DIM_DATE dd ON dd.FULL_DATE BETWEEN bd.PERIOD_BEGIN_DATE AND bd.PERIOD_END_DATE
    ) t
    where row_num = 1

    create clustered index #work_date_ix1 on #work_dates
    (PERIOD_BEGIN_DATE
    , PERIOD_END_DATE
    )

    
    SELECT  fa.ACCOUNT_ID
          , fa.MEMBERSHIP_ID_CURRENT
          , fa.ACCOUNT_BALANCE_AMOUNT AS 'Deposit Balance'
          -- ,dd.YEAR_NUMBER
          --, dd.MONTH_OF_YEAR_NUMBER
          --, dd.MONTH_SHORT_NAME
          , dd.FULL_DATE AS 'TRANSACTION_POSTING_DATE'
          , fa.PERIOD_END_DATE as 'Period End Date'
          , fa.[ACCOUNT_SUB_CLASS_CODE]
          , fa.Syndicated_Account_YN   

    FROM #fact_account fa
    inner join #work_dates dd
    on dd.PERIOD_BEGIN_DATE = fa.PERIOD_BEGIN_DATE
    and dd.PERIOD_END_DATE = fa.PERIOD_END_DATE

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

BTrees · Jan 6, 2019

Hi Fred,
here are answers of your questions
1--1 - post the new index you created
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20190102-135009] ON [dbo].[DIM_DATE]
(
[FULL_DATE] ASC
)
INCLUDE ( [YEAR_NUMBER],
[MONTH_OF_YEAR_NUMBER]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

2 - post the explain plan for
Attached (estimated query plan with new index)

3 - does the PERIOD_BEGIN_DATE and PERIOD_END_DATE encompass more than 1 month (here is sample of data
PERIOD_BEGIN_DATE PERIOD_END_DATE
4/1/2015 8/6/2015
4/8/2013 12/23/2015
1/9/2015 12/23/2015
8/13/2015 12/23/2015
12/29/2009 12/28/2015
7/12/2011 12/31/2015
11/9/2011 12/31/2015

4- - give us the output of the following query
output count = 69956

fredericofonseca · Jan 7, 2019

so... the index didn't work as you changed the query from the first one - new column that was commented out on the original query is now missing on the index.
on the index above month_short_name is missing on the include list - but again this will only work if you don't change the query again to include further columns from dim_date.

It also seems that you didn't run the explain plan on the same setup as the original one - on the first plan the fact_deposit table had a columnstore index, and the latest one does not.
and the latest one also seems to either have a different dim_date table or statistics are not updated on one of the 2 runs you gave us

regarding the period begin/end dates - was afraid it would be like that - again I ask if having the same record being output multiple times (for each year/month between begin/end date) is what you need.

and finally I have 2 possible options to the query below - they may or not give the exact results you need and even if they give the same name of records per account_id the values may not be the same for the remaining columns.
would appreciate if you could try both of them, get the explain plain (for the real execution) for both and post here.
and obviously compare to your own run to see if results are the same.

option 1

Code:

-- option 1
if object_id('tempdb..#fact_account') is not null
    drop table #fact_account;


-- select base fact records onto a temp table - should be around 8million rows 
-- as such using a table variable is not feasible - those should have a max of 1-2 k records


select *
    into #fact_account
from (select row_number() over (partition by dda.ACCOUNT_ID, fddaa.PERIOD_BEGIN_DATE, fddaa.PERIOD_END_DATE order by fddaa.PERIOD_END_DATE desc) row_num
           , dda.ACCOUNT_ID
           , dda.MEMBERSHIP_ID_CURRENT
           , fddaa.ACCOUNT_BALANCE_AMOUNT
           , fddaa.PERIOD_BEGIN_DATE
           , fddaa.PERIOD_END_DATE
           , dda.[ACCOUNT_SUB_CLASS_CODE]
           , case
             when dda.ACCOUNT_SUB_CLASS_CODE in ('ck-00C133', 'Ck-00C134', 'CML-00Y880', 'CML-00Y881', 'CML-00Y882', 'CML-00Y883')
                 then 'Y'
             else 'N'
             end as syndicated_account_yn
       from dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
       join dbo.DIM_DEPOSIT_ACCOUNT dda
           on fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT

       where fddaa.PERIOD_END_DATE >= @StartDate
           and dda.DIMID_DEPOSIT_ACCOUNT <> -1
           -- AND dda.ACCOUNT_CLOSED_DATE = '2999-12-31'
           and dda.ACCOUNT_SUB_CLASS_CODE not in ('ck-00C130', 'ck-00C137')
) t
where t.row_num = 1;

create clustered index #fact_account_ix1 on #fact_account
(PERIOD_BEGIN_DATE
, PERIOD_END_DATE
);

if object_id('tempdb..#base_dates') is not null
    drop table #base_dates;

select distinct PERIOD_BEGIN_DATE
              , PERIOD_END_DATE
    into #base_dates
from fact_account;

-- get all month end records for each block of begin/end dates
if object_id('tempdb..#work_dates') is not null
    drop table #work_dates;

select t.PERIOD_BEGIN_DATE
     , t.PERIOD_END_DATE
     , t.FULL_DATE
     , t.YEAR_NUMBER
     , t.MONTH_OF_YEAR_NUMBER
     , t.MONTH_SHORT_NAME

    into #work_dates
from (select bd.PERIOD_BEGIN_DATE
           , bd.PERIOD_END_DATE
           , dd.FULL_DATE
           , dd.YEAR_NUMBER
           , dd.MONTH_OF_YEAR_NUMBER
           , dd.MONTH_SHORT_NAME
           , row_number() over (partition by t.PERIOD_BEGIN_DATE, t.PERIOD_END_DATE, dd.YEAR_NUMBER, dd.MONTH_OF_YEAR_NUMBER order by dd.FULL_DATE desc) row_num
       from #base_dates bd
       inner join dbo.DIM_DATE dd
           on dd.FULL_DATE between bd.PERIOD_BEGIN_DATE and bd.PERIOD_END_DATE
) t
where row_num = 1;

create clustered index #work_date_ix1 on #work_dates
(PERIOD_BEGIN_DATE
, PERIOD_END_DATE
);


select fa.ACCOUNT_ID
     , fa.MEMBERSHIP_ID_CURRENT
     , fa.ACCOUNT_BALANCE_AMOUNT as 'Deposit Balance'
     , dd.YEAR_NUMBER
     , dd.MONTH_OF_YEAR_NUMBER
     , dd.MONTH_SHORT_NAME
     , dd.FULL_DATE as 'TRANSACTION_POSTING_DATE'
     , fa.PERIOD_END_DATE as 'Period End Date'
     , fa.[ACCOUNT_SUB_CLASS_CODE]
     , fa.Syndicated_Account_YN

from #fact_account fa
inner join #work_dates dd
    on dd.PERIOD_BEGIN_DATE = fa.PERIOD_BEGIN_DATE
    and dd.PERIOD_END_DATE = fa.PERIOD_END_DATE;

option 2

Code:

-- option 2


-- get distinct combination os begin/end dates
if object_id('tempdb..#base_dates') is not null
    drop table #base_dates;

select distinct fddaa.PERIOD_BEGIN_DATE
              , fddaa.PERIOD_END_DATE
    into #base_dates
from dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
inner join dbo.DIM_DEPOSIT_ACCOUNT dda
    on fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT

where fddaa.PERIOD_END_DATE >= @StartDate;



-- get all month end records for each block of begin/end dates
if object_id('tempdb..#work_dates') is not null
    drop table #work_dates;

select t.PERIOD_BEGIN_DATE
     , t.PERIOD_END_DATE
     , t.FULL_DATE
     , t.YEAR_NUMBER
     , t.MONTH_OF_YEAR_NUMBER
     , t.MONTH_SHORT_NAME

    into #work_dates
from (select bd.PERIOD_BEGIN_DATE
           , bd.PERIOD_END_DATE
           , dd.FULL_DATE
           , dd.YEAR_NUMBER
           , dd.MONTH_OF_YEAR_NUMBER
           , dd.MONTH_SHORT_NAME
           , row_number() over (partition by t.PERIOD_BEGIN_DATE, t.PERIOD_END_DATE, dd.YEAR_NUMBER, dd.MONTH_OF_YEAR_NUMBER order by dd.FULL_DATE desc) row_num
       from #base_dates bd
       inner join dbo.DIM_DATE dd
           on dd.FULL_DATE between bd.PERIOD_BEGIN_DATE and bd.PERIOD_END_DATE
) t
where row_num = 1;

create clustered index #work_date_ix1 on #work_dates
(PERIOD_BEGIN_DATE
, PERIOD_END_DATE
)

;
with cte
as
(select row_number() over (partition by dda.ACCOUNT_ID, dd.YEAR_NUMBER, dd.MONTH_OF_YEAR_NUMBER order by dd.FULL_DATE desc) row_num
      , dda.ACCOUNT_ID
      , dda.MEMBERSHIP_ID_CURRENT
      , fddaa.ACCOUNT_BALANCE_AMOUNT as 'Deposit Balance'
      , dd.YEAR_NUMBER
      , dd.MONTH_OF_YEAR_NUMBER
      , dd.MONTH_SHORT_NAME
      , dd.FULL_DATE as 'TRANSACTION_POSTING_DATE'
      , fddaa.PERIOD_END_DATE as 'Period End Date'
      , dda.[ACCOUNT_SUB_CLASS_CODE]
      , case
        when dda.ACCOUNT_SUB_CLASS_CODE in ('ck-00C133', 'Ck-00C134', 'CML-00Y880', 'CML-00Y881', 'CML-00Y882', 'CML-00Y883')
            then 'Y'
        else 'N'
        end as Syndicated_Account_YN

       from dbo.FACT_DAILY_DEPOSIT_ACCOUNT_AGGREGATE fddaa
       join dbo.DIM_DEPOSIT_ACCOUNT dda
           on fddaa.DIMID_DEPOSIT_ACCOUNT = dda.DIMID_DEPOSIT_ACCOUNT
       join #work_dates dd
           on dd.PERIOD_BEGIN_DATE = fddaa.PERIOD_BEGIN_DATE
           and dd.PERIOD_END_DATE = fddaa.PERIOD_END_DATE

       where dd.FULL_DATE >= @StartDate
           and dda.DIMID_DEPOSIT_ACCOUNT <> -1
           -- AND dda.ACCOUNT_CLOSED_DATE = '2999-12-31'
           and dda.ACCOUNT_SUB_CLASS_CODE not in ('ck-00C130', 'ck-00C137')
)

select c.ACCOUNT_ID
     , c.[Deposit Balance]
     , c.YEAR_NUMBER
     , c.MONTH_OF_YEAR_NUMBER
     , c.MONTH_SHORT_NAME
     , c.TRANSACTION_POSTING_DATE
     , c.[Period End Date]
     , c.MEMBERSHIP_ID_CURRENT
     , c.syndicated_account_yn
     , c.ACCOUNT_SUB_CLASS_CODE
from cte c
where row_num = 1;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

BTrees · Jan 14, 2019

Thanks Fred, great options. I tried both and option 2 worked. However I found some discrepancies in number. I will look into my main query. Thanks

fredericofonseca · Jan 14, 2019

great.
Out of curiosity how long does it take with my code?

Note that some of the differences between my code and your original one may be due to the fact that both mine and your queries have "order by" on row_number function that are not deterministic - as such values of ACCOUNT_BALANCE_AMOUNT, Syndicated_Account_YN and ACCOUNT_SUB_CLASS_CODE may be different due to this. Maybe some of the other values also.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

BTrees · Jan 15, 2019

Hi Fred, it took 7 to 8 m. Further can you please help me if I want to hide this thread as it tends to reveal some information. This is not nice to display it publically.Thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Large Fact Table Query Optimization using (ROW_NUMBER() OVER (PARTITION BY) ) 1

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

fredericofonseca

IS-IT--Management

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

BTrees

IS-IT--Management

fredericofonseca

IS-IT--Management

BTrees

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor