question

Gaurav avatar image
Gaurav asked

Heavy reports in sharepoint not working.

Hello everyone, I have some reports in sharepoint which work fine when I use the date parameters for 2-3 months, but when I use one year in date parameter or try it for heavy reports,report dose not work and it seems like connection is closed at this point as it prompts for parameters again.However the query is still running in SSMS. Any help is appreciated.
sharepoint
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I would guess you are hitting some sort of timeout when querying too much data. You can either: - increase the timeout setting - reduce the dataset you are querying - rewrite the query so it runs faster even if you query more data - look at possible missing indexes - upgrade hardware
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
+1 I was too slow. :)
1 Like 1 ·
Håkan Winther avatar image
Håkan Winther answered
It sounds as a timeout, you should be able to change timeout values in reporting services and/or in Internet Information Services. You should also try to tune your underlying query and indexes. Are your statistics up to date? If not, you can have unpredictable performance issues depending on your data distribution and your filter ranges.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
So many questions to ask you - are there lots of columns being returned? - how many rows are being returned in the 2 types of execution - the one that works and the one that doesnt? - how long do the queries take to run in SSMS if you supply the parameter values? - are you using stored procedures to return the data? - are you using functions in the query anywhere?
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Just to confirm - from SSMS the data is successfully returned in 3-4 minutes when some parameters are used but when other values are supplied then it takes over 5 mins and fails? I think we need to see the code for the s.proc and get that tuned to give better performance. 15 columns isnt a lot unless they are all Text.

How many rows are being handled = 10,000's 100,000's or 1,000,000's?

Please edit your question and add the code of your procedure so that we can take a look for you.
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
as well as the SP code, let's take a look at the schema (including keys, constraints etc) of the tables being references by the SP, the number of rows in the tables, and details of indexes on those tables.
1 Like 1 ·
Gaurav avatar image Gaurav commented ·
There are around 10-15 columns in these reports.Query which takes 3-4 min in SSMS works fine,but when it is more than 5 min the report prompts for the parameters and the query is still running.I am using stored procedures to get the data.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
@Thomas - why not get a full backup of the database and we'll fix it and send it back?!?!?! Was going 1 step at a time! merit mark for enthusiasm though :)
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Fatherjack - I did ask just for the *relevant* tables. :-P
0 Likes 0 ·
Show more comments
Gaurav avatar image
Gaurav answered
Thank you all for your suggestions.The problem has been resolved. It was because the execution timeout in Web.Config was set to 360.I have increased it and reports are working fine now.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Glad its fixed but are you sure the report should take that long?
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.