Looking for solutions for huge data Report

Rose Johnson
Rose Johnson used Ask the Experts™
Hi experts,

Anyone can offer any solutions for huge data. Our claim data is huge even though only 2 years data. For SSRS report, some parameters one year results are over 100 million rows, takes forever to deliver. e.g. some reports are 40 columns,  need to join 20 tables, very slow.

We are thinking to make two flat tables, pre-join all the tables
or make a star schema data warehouse ...

Please give me some suggestions, if these solutions work or you have any better ones.

Thank you in advance.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

Without knowing more specifics about your data it's hard to come up with a clear plan but I think moving towards a more traditional multi-dimensional star schema would be a good first step.  You may also find the need to generate some pre-aggregated tables like claim totals by day/month/etc.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

>Please give me some suggestions
Either hire an experienced reporting developer or architect, or contract with a local consulting company to build a reporting data warehouse database, ETL jobs that feed it, and reports.

There's no reason why a reporting function should bang against tables that could output 100 million rows, and I would be highly suspect that such processes would slow down other non-reporting functions using the same data.

>We are thinking to make two flat tables, pre-join all the tables or make a star schema data warehouse ...
You're on the right track, but there is not enough information here to be actionable, and what you need is way more then the scope of a single EE question.

Good luck.


Hi Brian Crowe,

Thank you for replying!

It happens just providing data, no aggregation needed, here's the example of sp of the report. Trouble is our DBA does give us any rights, can't connect Analysis, Report, Integration Service engine, everything has to be on production server.

USE [ReportsDB]

/****** Object:  StoredProcedure [MY].[USP2000577M_Provider_Claims]    Script Date: 02/10/2016 09:27:05 ******/


--CREATE PROCEDURE [MY].[USP2000577M_Provider_Claims-1]
      Declare      @FROMDATE         AS DATETIME
            ,@THRUDATE         AS DATETIME
            ,@PROVID               AS VARCHAR(MAX) = NULL
            ,@TAXID               AS VARCHAR(MAX) = NULL
            ,@NPI               AS VARCHAR(MAX) = NULL
            ,@PROCCODE         AS VARCHAR(500) = NULL
            ,@STATUS               AS VARCHAR(500)  = NULL
            ,@HEADER_LINE   AS CHAR(6)      = NULL
            ,@DATETYPE         AS VARCHAR(7)   = NULL
            ,@GRPNPI        AS VARCHAR (MAX)= NULL
            ,@AHCCCSID      AS VARCHAR (100)= NULL


            if (@PROVID IS NOT NULL  AND @PROVID <> '')       set @PROVID = REPLACE(@PROVID,' ','')
            if (@NPI IS NOT NULL  AND @NPI <> '')             set @NPI = REPLACE(@NPI,' ','')
            if (@TAXID IS NOT NULL AND @TAXID <> '')          set @TAXID = REPLACE(@TAXID,' ','')
            if (@AHCCCSID IS NOT NULL AND @AHCCCSID <> '')    set @AHCCCSID = REPLACE(@AHCCCSID,' ','')
            if (@GRPNPI IS NOT NULL AND @GRPNPI <> '')        set @GRPNPI = REPLACE(@GRPNPI,' ','')
            if (@STATUS IS NOT NULL AND @STATUS <> '')        set @STATUS = REPLACE(@STATUS,' ','')
            if (@PROCCODE IS NOT NULL AND @PROCCODE <> '')    set @PROCCODE = REPLACE(@PROCCODE,' ','')

            declare @StartDate varchar(19)
            declare @EndDate varchar(19)

            SET @StartDate      = CONVERT(varChar(19), @FROMDATE, 101) + ' 00:00:00'
            SET @EndDate      = CONVERT(varChar(19), @THRUDATE, 101)   + ' 23:59:00'

            IF OBJECT_ID('TEMPDB.dbo.#USP2000577M_1') IS NOT NULL
            DROP TABLE #USP2000577M_1

            select distinct
                         [claimid] = RTRIM(cl.claimid)
                        ,[dos] =  cl.startdate
                        ,[carriermemid]= RTRIM(ek.carriermemid)                   
                        ,[member] = RTRIM(m.fullname)      
                        ,[provid] = RTRIM(p.provid)                        
                        ,[npi] = RTRIM(p.npi)                                    
                        ,[serv_provider] = RTRIM(p.fullname)                        
                        ,[payto] = RTRIM(p2.fullname)      
                        ,[tin] = p2.fedid
                        ,[status] = RTRIM(cl.status)
                        ,Check_date = cl.paiddate
                        ,RecDate = cl.logdate                   
                        ,[pat_control_num] = cl.controlnmb
                        ,[programid] = ek.programid      
                        ,[rev_programid] = ek.programid      
            INTO #USP2000577M_1            
            from PLANREPORT_QNXT_MMIC.DBO.claim                                          cl (NOLOCK)
            JOIN PLANREPORT_QNXT_MMIC.DBO.member                                    m  (NOLOCK)         ON cl.memid              = m.memid
            join PlanReport_QNXT_MMIC.dbo.enrollkeys                              ek (NOLOCK)    on cl.enrollid      = ek.enrollid
            JOIN PLANREPORT_QNXT_MMIC.DBO.affiliation                              a  (NOLOCK)         ON cl.affiliationid = a.affiliationid                              
            JOIN PLANREPORT_QNXT_MMIC.DBO.provider                                    p  (NOLOCK)         ON a.provid                    = p.provid                                    
            JOIN PLANREPORT_QNXT_MMIC.DBO.provider                                    p2 (NOLOCK)         ON a.affiliateid        = p2.provid      
            left join MMIC_Data_Mart.MY.provider_contracts                  pc (NOLOCK)            ON cl.provid        = pc.provid
                                                                                AND ek.programid     = pc.programid
                                                                                AND p.npi            = pc.provnpi
                                                                                AND a.affiliationid  = pc.affiliationid
            LEFT JOIN (SELECT pxv.paymentid, pxv.claimid
                                FROM PLANREPORT_QNXT_MMIC.DBO.payvoucher  (NOLOCK) pxv
                                INNER JOIN [planreport_QNXT_MMIC].[dbo].[payment]  (NOLOCK) pxmt ON pxv.paymentid = pxmt.paymentid AND pxmt.status <> 'VOID') pv ON cl.claimid      = pv.claimid                                                                           
                        (@DATETYPE = 'UPDATE' AND cl.LASTUPDATE BETWEEN @StartDate AND @EndDate)
                        (@DATETYPE = 'PAID' AND cl.PAIDDATE BETWEEN @StartDate AND @EndDate))
            AND ((@STATUS = '' OR @STATUS IS NULL) OR ((@STATUS != '' OR @STATUS IS NOT NULL) AND (cl.status IN (SELECT value FROM ReportsDB.DI.USF_Split(@STATUS,',')))))
            AND ((@PROVID = '' OR @PROVID IS NULL) OR ((@PROVID != '' OR @PROVID IS NOT NULL) AND (p.provid in (SELECT value FROM ReportsDB.DI.USF_Split(@PROVID,',')))))
         AND ((@NPI = '' OR @NPI IS NULL) OR ((@NPI != '' OR @NPI IS NOT NULL) AND (p.NPI in (SELECT value FROM ReportsDB.DI.USF_Split(@NPI,',')))))
            AND ((@TAXID = '' OR @TAXID IS NULL) OR ((@TAXID != '' OR @TAXID IS NOT NULL) AND (p2.fedid in (SELECT value FROM ReportsDB.DI.USF_Split(@TAXID,',')))))
            AND ((@AHCCCSID = '' OR @AHCCCSID IS NULL) OR ((@AHCCCSID != '' OR @AHCCCSID IS NOT NULL) AND (ek.carriermemid in (SELECT value FROM ReportsDB.DI.USF_Split(@AHCCCSID,',')))))

         CREATE INDEX IDX_ClaimID ON #USP2000577M_1(claimid)
             IF OBJECT_ID('TEMPDB.DBO.#USP2000577M_2') IS NOT NULL DROP TABLE #USP2000577M_2
            select distinct
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE cast(cd.claimline as varchar(8)) end [claimline]
                        ,[Authorizationid] = CASE WHEN (cl.referralid = '' AND cd2.referralid = '')      THEN 'No Auth' ELSE (CASE WHEN cl.referralid > '.' THEN cl.referralid ELSE cd2.referralid END)       END             
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE cd.prindiag END Diagnosis
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE cd.servcode END servcode
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE CAST(cd.servunits AS CHAR(6)) END [Servunits]
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE CAST(cd.modcode AS CHAR(6)) END  [modifier]
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE CAST(RTRIM(cd.revcode) AS CHAR(6)) END [Rev_code]
                        ,Remit_comments =  CAST(eob.eobexplanation AS VARCHAR(MAX))      
                        ,[CHECK #] = pc.checknbr                  
                        ,case when @HEADER_LINE = 'HEADER'  THEN cl.lastupdate else cd.lastupdate end Last_Update
                        ,case when @HEADER_LINE = 'HEADER'  THEN cl.totalamt else cd.claimamt end [billed_amt]
                        ,case when @HEADER_LINE = 'HEADER'  THEN
                                          (Select sum([contractpaid]) FROM planreport_QNXT_MMIC.DBO.claimdetail  (NOLOCK)  cd3 where cl.claimid = cd3.claimid)
                                    else  cd.[contractpaid]
                         end  [contractpaid]
                        ,case when @HEADER_LINE = 'HEADER'  THEN cl.totalpaid else cd.amountpaid end [paid_amt]                        ,cl.[pat_control_num]

      INTO #USP2000577M_2
      from #USP2000577M_1 cl  (NOLOCK)
      join PLANREPORT_QNXT_MMIC.DBO.claimdetail      AS cd (NOLOCK)      ON cl.claimid = cd.claimid
      LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.claimdetail AS cd2 (NOLOCK) ON cL.claimid = cd2.claimid  AND cd2.claimline = 1                        
            LEFT OUTER JOIN planreport_qnxt_MMIC.dbo.claimeobexplain eob (NOLOCK) ON cd.claimid = eob.claimid                  
            LEFT JOIN (SELECT pxv.paymentid, pxv.claimid
                                FROM PLANREPORT_QNXT_MMIC.DBO.payvoucher  (NOLOCK) pxv
                                INNER JOIN [planreport_QNXT_MMIC].[dbo].[payment]  (NOLOCK) pxmt ON pxv.paymentid = pxmt.paymentid AND pxmt.status <> 'VOID') pv ON cl.claimid      = pv.claimid                   
            LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.paycheck AS pc (NOLOCK) ON pv.paymentid      = pc.paymentid AND cd.fundid = pc.fundid                                    
            LEFT JOIN PLANREPORT_QNXT_MMIC.DBO.claimattribute AS ca (NOLOCK) ON cl.claimid = ca.claimid      AND attributeid IN ('T02491957', 'T02579383', 'T02580499')                  
      WHERE 1 = 1
            and ((@PROCCODE = '' OR @PROCCODE IS NULL)  OR ((@PROCCODE != '' OR @PROCCODE IS NOT NULL) and (cd.servcode       in (SELECT value FROM ReportsDB.DI.USF_Split(@PROCCODE,',')))))
            and ((@GRPNPI = '' OR @GRPNPI IS NULL)  OR ((@GRPNPI != '' OR @GRPNPI IS NOT NULL) and (cl.groupnpi in (SELECT value FROM ReportsDB.DI.USF_Split(@GRPNPI,',')))))
      order by cl.claimid      
         CREATE INDEX IDX_ClaimID ON #USP2000577M_2(claimid)
         if @HEADER_LINE <> 'HEADER'  
             select * from #USP2000577M_2 order by claimid,CAST(claimline as int)
               select * from #USP2000577M_2 order by claimid



Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Hi Jim Horn,

Thank you for replying!

Please see the my last comment, does it give you any information you need?

I'm thinking the problem is we have to run report directly in production server, and we don't have right to move data to any where, only have a small ref database we can create a table. They said no budget to hire anyone else, even though buy Analysis, Report, Integration Service engines, DBA said they are other databases which  I've never heard. Anyway, we only can deal with it under such circumstances.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
>Trouble is our DBA does give us any rights ... everything has to be on production server.
Most DBA's I've met would threaten violence if a developer told them they wanted to create a reporting process that returned 100 million rows and ran it against a production environment.  I have yet to meet a DBA that would actually propose such a thing.

And if you doubt that statement, wait until this report runs and all of the sudden order entry, purchasing, payroll, and other important daily functions come to a screeching halt, and see who screams the loudest.

I'll stand by my original comments.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
1) you need to use dynamic sql instead of coding all the variables in the WHERE.  That type of code always takes much, much longer to run.
2) if you need to create temp table(s), you should create a clustered index on it(them) before loading, esp. if it(they) contain millions of rows.
3) the source tables (PLANREPORT_%) need to have the necessary indexes on them, including the best clustered index.
4) For very large reports that are unavoidable, pre-run them and cache them.  Then the users can get them instantly (or as instantly as SSRS can deliver millions of pre-formed rows!).
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
If the report is only against archive or historical data, you can have an archive process that move the data to another database, even in another MSSQL instance.
- Don't impact your live database when report is running;
- Reduce your live database size with a positive performance impact since has less rows to process
- Historical data isn't available in your application for consultation

Another option is to use partitioned table. This will improve the performance of your queries and data will still available for your application.


Thank you all your contributions, I'm having hard time to decide which suggestions I can apply. Since some of them are out of my knowledge especially DBA's parts ( I can't expect on DBA's collaboration though, but it's good to have some knowledge) so please give me some time to figure out what can really help. Sorry for the delay comments.


Hi All,

I took part suggestions of each of you.
Thank you all the answers.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial