Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Faster than Management Studio!? Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 1:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:02 AM
Points: 7, Visits: 61
Hi all,

When I run a stored procedure as a direct query in Management studio it's taking ~18 seconds to complete, however when I run the exact same stored procedure in SSRS it's taking about 3 seconds for the query to be called and the report to fully render. I could understand if it was the other way around, but I'm a bit baffled by this. Any ideas?

Thanks.
Post #1559813
Posted Wednesday, April 9, 2014 2:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:51 AM
Points: 1,536, Visits: 1,745
In that past I see this for 2 reasons

1 - "SET" option differ in SSMS than the default connection to the server i.e. ARITHMETIC ABORT (check server setting and your query options within SSMS)

2 - If you are debugging the SP and provide local variables then I have also seen a different execution plan selected.
Post #1559820
Posted Wednesday, April 9, 2014 3:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.
(just a wild guess here)

Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559845
Posted Wednesday, April 9, 2014 5:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:02 AM
Points: 7, Visits: 61
Koen Verbeeck (4/9/2014)
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.
(just a wild guess here)

Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.


I'm not sure how to check the execution plan for the procedure when it's called from Reporting Services as I've not looked at that before. Is there an easy way?

Caching is disabled on the reporting, and if I change aspects of the stored procedure the changes are reflected live in the report on a normal report refresh, so I'm confident there's no caching going on.


Post #1559892
Posted Wednesday, April 9, 2014 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Oblivion (4/9/2014)
Koen Verbeeck (4/9/2014)
Another side effect from SSMS is that it has to display all the rows in the result set. It might do it not so efficiently as SSRS.
(just a wild guess here)

Anyway, to really compare you would have to take a look at the execution plan and rule out caching as well.


I'm not sure how to check the execution plan for the procedure when it's called from Reporting Services as I've not looked at that before. Is there an easy way?


I think you can capture it using Profiler. (at least the query)
The plan might be retrieved from a DMV as well, but that's a bit out of my comfort zone.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1559900
Posted Wednesday, April 9, 2014 6:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:51 AM
Points: 1,536, Visits: 1,745
If it is currently in the cache then use the query below (replacing the check with your SP name).

SELECT  UseCounts ,
Objtype ,
[text] ,
query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%check%'

Note: It can take a while to run, so please plan accordingly.


Post #1559905
Posted Wednesday, April 9, 2014 7:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 3:38 PM
Points: 23, Visits: 57
SSRS designer caches query results by default. So, if it has cached results, it won't rerun the query at all
Post #1559924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse