Weird timeout, 1 report doesn't render even if the query is over in 5 secs...
-
[Not a fix/soln, just to investigate further]
Hi there,
Are you using a matrix?
Are the columns in the table only fields from the query with groupings.
In the dataset properties tab, are all the fields and values mapped to columns from the query.
How about paramteres. Query based Parameter Values. Are they cascading or tied to UserID. Do the parameter queries return records as you expect
Are you using expressions with CommandText in the report
Are there dynamic expression based cosmetics, coloring/font/visibility/ pagination/page break
How about with/ without header/footer, And any vbscript code and are they legit?
Can you just use a table control without any groups and render the columns in a flat table on the report and see if the report runs. If it does, we can nail the issue on one of the above or the groups and can find ways to mitigate it.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
It looks like there is a begin time, and end time, along with the various components.
60000 = 60 seconds on one of my reports.
Convert TimeDateRetrieval, TimeProcessing, and TimeRendering.
They should add up to what you see between TimeStart and TimeEnd.
Greg E
-
Hi there,
Are you using a matrix?
Yes
Are the columns in the table only fields from the query with groupings.
There are 2 calculatd fields once in ssrs year(date) and month(date).
In the dataset properties tab, are all the fields and values mapped to columns from the query.
No, 2 calculated fields.
How about paramteres. Query based Parameter Values. Are they cascading or tied to UserID. Do the parameter queries return records as you expect
2 dates, 3 static lists of parameters. Nothing tied to userID. No parameter queries.
Are you using expressions with CommandText in the report
I don't know what that is so I'm going to say no?!
Are there dynamic expression based cosmetics, coloring/font/visibility/ pagination/page break
How about with/ without header/footer, And any vbscript code and are they legit?
Yes there is. However they were all there since day 0 of implementation and the report ran like clockwork for 6 months before crashing (again 45 secs in the longest runs to be rendered in html)
Can you just use a table control without any groups and render the columns in a flat table on the report and see if the report runs. If it does, we can nail the issue on one of the above or the groups and can find ways to mitigate it.
I'll be able to test that tommorrow. I'll let you know the results.
One more thing, I finally had an error show up late last night (can't read next data row from dataset... or something like that).
That led to known bug when the server throws the warning... nulls ignored in aggregation (very rough translation from french).
I tried set ansi_warnings OFF but that didn't work
I then adjusted the query on the server so that the warning was not thrown... basically and an SUM(isnull(col, 0)) on all aggregated data.
The report now runs, however the cpu is pegged to 99.99% on the oltp server for 5-10 minutes because of the added work... and still renders in the same 30 odd seconds.
I removed permissions to the report and added a nightly subscription run to dump an excel version on the network as a short term workaround.
I'm going back in tommorrow to see how I can fix this to run on-demand without bringing the server to its knees.
I might have to ask the users to split this report into 2-3 reports so that it's much less demanding (the report is basically a dump the whole DB in a single spreadsheet). They didn't have the budget for building a cube either...
Oh the fun with this one!!!
-
Geez! you have answered yes to most of the questions......
See, if , for a matrix cell against a row/col group, the query does not return a value, then SSRS can do nothing, but wait indefinitely. Say, for example, you are grouping by product in the row and months across. IF you are not sure if you will have values for all products across all months then DO NOT use matrix. On an empty cell (no product sold for that particular month), the report engine will go mad. Add to the aggregations/ data validations/ expressions/colors based on existing value in that cell. I would say that cell is not even considered null. It is a vaccum. No sane rules/ logic can apply/justify the report behaviour.
Also, if you are using the calc fields(month and year) in the dataset for grouping across (matrix columns), it is a big NO No. For each row in the result set, two columns are calculated at run time and then grouped(across, i.e, the matrix col grouping). Then, if, for a cell in the matrix, there are no values, please repeat the above para.
Sorry, the justification "But it was running fine 6 months back" do not sell, because as you see, data has changed, new data has entered. I am betting my this months salary on few matrix cells do not have values!!
If you HAVE to use a matrix, then do the isnull 0 in the query. At least all cells in the matrix will have a value.
Also, add the calc fields to the query first and then bring them on to dataset, if you use them for your matrix column groupings
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
Thanks for the info, I'll take all that into consideration when I rewrite tomorrow.
Also, I appreciate your running fine comments.
I agree that data has changed. However I tested that report on date ranges from 1 month to 5 years worth of data (system implementation date) and it always ran in ±45 seconds (db is about 20 GB is size now instead of 19 GB in implementation, so I really doubt that amount of data is the issue here). Also the sql query ran in 6-9 secs back then and it still does now. That's one of the reasons I wascompletly stumped about this one.
How much of an impact can the formatting have in the cells? Can it double the rendering time or just make a 1-2% improvement.
Anyhow, thanks again I'll let you know how it goes after tuning.
-
BTW, got any great performance / tuning / best practices article for reporting services?
-
No best practices. It is all from mistakes that I learnt(continously learning) from.
As you rightly pointed out, DB size does not matter, but if you find time to exlpore data there would definitely be a cell in the matirx corresponding to a row and column group with no data, and that is creating trouble.
To reiterate
Please, in the query, bring values for all row and col groups. Eg., if no product A sold for march 09, insert a record with 0 for prod a and march 09 in SQL itself, so that in the matrix there will be a value (no null no vaccum) and engine will render it.
On a matrix cell, are you computing percents/dividing on the aggregated numbers (after checking for null using expressions in that particular cell.) You might do a isnothing() to check for null value in that cell and then calculate percent/divide. The report will not throw error/exception at design time. But during run time, it will not know what to do, because there is no value in that cell and it is also legally not null.
If yes, then query should return values definitely for all matrix cells.
Please do not group using the calculated cols in the dataset. Please create them in the sql query.
Also, in the report are you using Previous/Next to aggregate/show hide, when you say ......"can't read next data row from dataset"
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
It turned out that adding with recompile whipped out parameter sniffing and bad query plans. Then adding SET ANSI_WARNINGS OFF... INSIDE the SP solved the warning.
Now the report is back to running in its old 45 sec.
I also removed all the formatting and vb behind code. That chopped about 10 secs processing but the users preffer to wait and have formatting so I'm still running the old version...
It really took both solutions, neither one by itself solved the problem!
Thanks again for all the help.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply