Looking for solutions for huge data Report

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.

Rose
Rose JohnsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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 DudeCommented:
>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.
Jim
Rose JohnsonAuthor Commented:
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]
GO

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

SET QUOTED_IDENTIFIER ON
GO



/**************************************************************************/
--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
--)
--AS

SET NOCOUNT ON


******************************************************************************/
       
BEGIN
            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      
            ,pc.groupnpi      
            ,cl.referralid      
            ,cl.lastupdate
            ,cl.totalpaid
            ,cl.totalamt            
                                          
            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                                                                           
                                                                               
         WHERE ((@DATETYPE = 'SERVICE' AND cl.STARTDATE BETWEEN @StartDate AND @EndDate)
                         OR
                        (@DATETYPE = 'UPDATE' AND cl.LASTUPDATE BETWEEN @StartDate AND @EndDate)
                         OR
                        (@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
                         cl.[claimid]
                        ,case when @HEADER_LINE = 'HEADER'  THEN 'N/A' ELSE cast(cd.claimline as varchar(8)) end [claimline]
                        ,cl.[dos]
                        ,cl.[carriermemid]      
                        ,cl.[member]
                        ,cl.[provid]                  
                        ,cl.[npi]
                        ,cl.[serv_provider]                   
                        ,cl.[payto]
                        ,cl.[tin]
                        ,[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]
                        ,cl.[status]
                        ,Remit_comments =  CAST(eob.eobexplanation AS VARCHAR(MAX))      
                        ,[CHECK #] = pc.checknbr                  
                        ,cl.Check_date
                        ,cl.RecDate
                        ,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]
                        ,cl.[programid]
                        ,cl.[rev_programid]
            ,cl.groupnpi
            ,cl.referralid      
            ,cl.lastupdate
            ,cl.totalpaid
            ,cl.totalamt

      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)
         ELSE
               select * from #USP2000577M_2 order by claimid

END
   


/**************************************************************************/

GO
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rose JohnsonAuthor Commented:
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.
Jim HornSQL Server Data DudeCommented:
>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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ãoMSSQL Senior EngineerCommented:
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.
Benefits:
- 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
Cons:
- 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.
Rose JohnsonAuthor Commented:
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.
Rose JohnsonAuthor Commented:
Hi All,

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

Rose
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSIS

From novice to tech pro — start learning today.