none
decreasing run time

    Question

  • Hi,

    I've got a graph in Reporting services created from a 250,000 rows table.

    When I run the query in my Database it takes 2 seconds.

    But in reporting services it takes about 25 seconds for the graph to be created (I've attached the graph).

    Is there a way to decrease the run time?Graph 

    Thursday, July 04, 2013 11:57 AM

Answers

All replies

  • What do you mean by "in my database"?  What program are you using to query in your database?  Are you saying it takes 25 seconds to query it in SSMS?  What does your SQL query look like?

    Ryan D

    Saturday, July 06, 2013 11:35 PM
  • Does the query have parameters? Can you show us the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 07, 2013 6:32 AM
  • I'm using the Microsoft SQL Server Management Studio (SQL Server 2012).

    in SSMS the query takes 2 seconds (even 1 second).

    But the web from reporting services does "Loading" for about 25 seconds until creates a graph.

    I'm creating 1 month graph in a 5 minutes resolution.

    If I'll create 1 week graph, it will take about 12 seconds for the graphs to be created,  so I guess it takes time for the graph to be created.

    This is the query that runs in the background:

    SELECT [SourceHub]
          ,[DateHour]
          ,[NetworkName]
          ,[QOS]
          ,bw_allocated_DS/1.2 as bw_allocated_DS
          ,[SumOfAllBPS]
      FROM [IDReportsDWH].[dbo].[TrafficQOS_DS_MODCOD_USA]
    where SourceHub = 'nms-crawford-nss10-evu'
    and SourceHub in ('NMS-CRAWFORD-NSS10-EVU')
    and NetworkName in ('Evolution_AMC12_Network')
    and QOS in ('Advantage')
    and DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00'

    union all

    SELECT [SourceHub]
          ,[DateHour]
          ,[NetworkName]
          ,[QOS]
          ,bw_allocated_DS
          ,[SumOfAllBPS]
      FROM [IDReportsDWH].[dbo].[TrafficQOS_DS_MODCOD_USA]
    where SourceHub <> 'nms-crawford-nss10-evu'
    and SourceHub in ('NMS-CRAWFORD-NSS10-EVU')
    and NetworkName in ('Evolution_AMC12_Network')
    and QOS in ('Advantage')
    and DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00'
    order by DateHour

    I've attached a print screen from how it looks in the WEB 

    Parameters and Graph

     
    Sunday, July 07, 2013 8:34 AM
  • I'm using Parameters - I've attached it

    in SSMS the query takes 2 seconds (even 1 second).

    But the web from reporting services does "Loading" for about 25 seconds until creates a graph.

    I'm creating 1 month graph in a 5 minutes resolution.

    If I'll create 1 week graph, it will take about 12 seconds for the graphs to be created,  so I guess it takes time for the graph to be created.

    This is the query that runs in the background:

    SELECT [SourceHub]
          ,[DateHour]
          ,[NetworkName]
          ,[QOS]
          ,bw_allocated_DS/1.2 as bw_allocated_DS
          ,[SumOfAllBPS]
      FROM [IDReportsDWH].[dbo].[TrafficQOS_DS_MODCOD_USA]
    where SourceHub = 'nms-crawford-nss10-evu'
    and SourceHub in ('NMS-CRAWFORD-NSS10-EVU')
    and NetworkName in ('Evolution_AMC12_Network')
    and QOS in ('Advantage')
    and DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00'

    union all

    SELECT [SourceHub]
          ,[DateHour]
          ,[NetworkName]
          ,[QOS]
          ,bw_allocated_DS
          ,[SumOfAllBPS]
      FROM [IDReportsDWH].[dbo].[TrafficQOS_DS_MODCOD_USA]
    where SourceHub <> 'nms-crawford-nss10-evu'
    and SourceHub in ('NMS-CRAWFORD-NSS10-EVU')
    and NetworkName in ('Evolution_AMC12_Network')
    and QOS in ('Advantage')
    and DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00'
    order by DateHour

    I've attached a print screen from how it looks in the WEB 

    Parameters and Graph

     
    Sunday, July 07, 2013 8:35 AM
  • Thanks 

    What indexes do you have? What if  you add a hint OPTION(RECOMILE) to the WHEREs condition?

    where SourceHub = 'nms-crawford-nss10-evu'
    and SourceHub in ('NMS-CRAWFORD-NSS10-EVU')
    and NetworkName in ('Evolution_AMC12_Network')
    and QOS in ('Advantage')
    and DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00'

    OPTION (RECOMPILE)

    Does it work faster?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 07, 2013 8:56 AM
  • I think the "OPTION (RECOMPILE)" decreased the run time in 1-2 seconds.

    I'm using a clustered index on 4 fields:

    exec [dbo].[usp_PrintAndLogMSG] 'Create Clustered index on [TrafficQOS_DS_MODCOD_USA]','[usp_CreateIndexesBeforeLoading]'
    CREATE CLUSTERED INDEX [idx_CI_SourceHub_Network_QOS_TrafficQOS_DS_MODCOD_USA] ON [dbo].[TrafficQOS_DS_MODCOD_USA] ([SourceHub] ASC, [DateHour] ASC,[NetworkName] ASC, [QOS] ASC)

    But anyway, I don't think the index is the case, cause through SSMS I'm running it with no problem (takes about a seconds).

    Do you know if it's a known problem that it takes time for graphs to be created (specially graphs with a lot of data)?

     
    Sunday, July 07, 2013 9:45 AM
  • I am not sure about what  you have posted above? Is that possible to show the execution plan?

    >>>I think the "OPTION (RECOMPILE)" decreased the run time in 1-2 seconds.

    Does it mean you have tried and confirmed the  above?

    Read Erland;s article as well. http://www.sommarskog.se/query-plan-mysteries.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Marked as answer by ThomasYaris30 Monday, July 08, 2013 9:04 AM
    Sunday, July 07, 2013 9:53 AM
  • So you are grouping your data for a month in five minute intervals? Is there really a need for the interval to be so small when you are looking at a month?

    • Marked as answer by ThomasYaris30 Monday, July 08, 2013 9:04 AM
    Sunday, July 07, 2013 10:54 PM
  • And if this is what you need to do, then perhaps look at getting your SQL query to group the data into five minute intervals, instead of your chart doing all the work.
    • Marked as answer by ThomasYaris30 Monday, July 08, 2013 9:04 AM
    Sunday, July 07, 2013 10:56 PM
  • I have a few suggestions:
    Replace all IN statements with = in your WHERE clause.  
    This should dramatically improve processing time.  Also, change this last line to:
    "And (DateHour between '2013-06-01 00:00:00' and '2013-07-07 00:00:00')"

    What is the datatype of DateHour?  Is it datetime or integer?  It might be faster to use one of the DateTime functions.

    Ryan D

    Monday, July 08, 2013 12:46 PM
  • You're right.

    But it there a way to make the graph, lets say a 30 minutes interval, when querying above 2 weeks?

    My SQL query already grouping every 5 minutes. The user can decides how long he wants to see the graph from.

    Is there a way to make the graph be created with different intervals according to the time range the user type?

    This is how my web looks like (As you can see the user type "Start Date" and "End Date")

    Parameters and Graph

    Monday, July 08, 2013 6:22 PM