Response Time RRS feed

  • Question

  • User-1499457942 posted


      I have below query in SSRS and it takes approx 6-7 min. There are more than 1 crore records. Can we improve the query further to reduce response time

    SELECT A.[G_L Account No_] as 'Account No' ,  A.[Global Dimension 1 Code], (select [Name] from [XYZ$G_L Account]  where [No_] = A.[G_L Account No_]) as Name,
    (select sum([Debit Amount])-sum([Credit Amount])   from [XYZ Retail Limited$G_L Entry]  where [Posting Date] < @frdate and [G_L Account No_] = A.[G_L Account No_] and [Global Dimension 1 Code] = A.[Global Dimension 1 Code] ) as OAmt,
    (select sum([Debit Amount])-sum([Credit Amount])  from [XYZ$G_L Entry]  where [Posting Date] between @frdate and @todate and [G_L Account No_] = A.[G_L Account No_] and [Global Dimension 1 Code] = A.[Global Dimension 1 Code] ) as TAmt,
    (select sum([Debit Amount])-sum([Credit Amount])  from [XYZ$G_L Entry]  where [Posting Date] <= @todate and [G_L Account No_] = A.[G_L Account No_] and [Global Dimension 1 Code] = A.[Global Dimension 1 Code] ) as ClAmt
    from [XYZ$G_L Entry] as A
    where A.[Posting Date] <= @todate 
    group by A.[G_L Account No_], A.[Global Dimension 1 Code] 


    Thursday, December 21, 2017 9:27 AM

All replies

  • User347430248 posted

    Hi JagjitSingh,

    you had mentioned that,"I have below query in SSRS and it takes approx 6-7 min. There are more than 1 crore records. Can we improve the query further to reduce response time".

    are you going to display 1 crore data in your report?

    i don't think so and if you display then also it will quite difficult for a person to view and understand it properly.

    you just try to fetch the data that you are going to display on report.

    you not need to fetch unnecessary data. it will increase the load and fetching time.

    instead of showing 1 crore data , you can try to show the summary for that 1 crore data which is much more easy to understand for any one.

    you can try to refer article below may give you some information.

    My data takes too long to retrieve.

    More report data requires more resource use, more network traffic, more processing time, and more storage. Analyze the issues presented in your report to determine how much data that you need, and then only retrieve that data from the report data sources.

    More Data is Retrieved for a Report Than is Needed

    Filter, sort, and aggregation is more efficient on the data source than during report processing. Write queries to return only the level of detail that you show in the report. The following list suggests ideas for evaluating each report query in the report:

    • Write queries with WHERE clauses or HAVING clauses that limit the data to just what the user must see in the report. Use query parameters to restrict data that is retrieved at run time. Query parameters are automatically bound to corresponding report parameters, and enable a user to decide which data they are interested in. For more information, see Filtering Rows by Using WHERE and HAVING.

      When you create a snapshot report that has report parameters that filter the data, all possible data that could be displayed in the report must be saved in the snapshot. In this case, do not use query parameters in the dataset queries. Instead, manually create report parameters that you can use in filter expressions to enable the user to specify the report data they want.

    • Write queries with the ORDER BY clause to presort data that is retrieved for a report. Sort the data in the order you want it sorted in the report. Presorted data improves report processing time because of the way it is stored in memory. Many report processing tasks do not require sorting data before processing it. For example, SUM is not order-dependent. Data within group instances is not automatically sorted. If you do not need sorted data in the report, do not set sort expressions on the dataset or data region. For more information, see ORDER BY Clause (Transact-SQL) and How to: Sort Data in a Data Region (Report Builder 3.0 and SSRS).

      Sorting groups or sorting by aggregate values is much simpler in the report than in the query and is frequently more efficient also.

    • Write queries with GROUP BY to aggregate values on the data source.

      Many times, the most effective way to communicate information is by aggregating values and displaying summaries. You can calculate some level of aggregates on the data source and retrieve them for a dataset. The "detail" data in the dataset now represents aggregates calculated on the data source. For more information, see Summarizing Query Results (Visual Database Tools).

      After these pre-aggregated values are in a report, you can continue to aggregate the values as long as you are using an aggregate function that is mathematically transitive, for example, SUM. For example, assume that you have a set of 6 values: 1, 2, 3, 4, 5, 6. If you group the values into pairs, you have a set of 3 values: 3, 7, 11. You can calculate the sum on the first set (21) and calculate the sum of the second set (21) and the sums are the same regardless of the grouping. If you average the values in the sets by using the AVG function, you get a different result for each set. The average for the set of 6 is 21/6 or 3.5. The average of the set of 3 is 21/3 or 7. AVG is not a transitive function.

    • Consider the amount of data needed for a chart or gauge. Drawing hundreds of points in a few pixels on a monitor degrades performance and does not enhance the visual display of the graphics. More than 7 or 8 slices in a pie chart is of questionable value. For more information, see information in specific chart types listed in Chart Types (Report Builder 3.0 and SSRS).

    • For report items with conditional visibility, the report processor must apply grouping, sorting, and filtering expressions even if only the top level of data is at first visible. Although on-demand processing in SQL Server 2008 Reporting Services optimizes data evaluation by processing only data that is visible, all possible data is part of the report. If the user is only interested in seeing detail data some of the time, a drillthrough report is a better choice. For more information, see Types of Reports.

    • Consider creating execution snapshots for a report. A report snapshot includes all report data retrieved for the datasets in the report definition. For more information, see Creating, Modifying, and Deleting Snapshots in Report History.

    for a detailed article, you can try to refer link below.

    Troubleshooting Reports: Report Performance



    Friday, December 22, 2017 1:43 AM