Report Rendering Issue

Question

Report Rendering Issue

Ronnie Jones

Hall of Fame

Points: 3377
More actions
April 15, 2014 at 9:19 am

#287678

Hello All,
I have a report consisting of 2 columns that counts the number of distinct instances of a specific column:
ie, SELECT [TEXT], COUNT([TEXT]) AS [COUNT], FROM…
The report uses parameters for several of the columns to filter the data. There are 2 date parameters and 3 text parameters. The WHERE clause looks like this:
WHERE LogDate BETWEEN @MyDate1 AND @MyDate2 AND ServerName = @SERV AND MessageType = @MESSAGET AND [TEXT] LIKE @TEXT
I also created a subreport consisting of the details of the above results. So, you click on the COUNT and all of the parameters are passed to the subreport to return all of the details associated with those records – actually from the same table.
The primary report works fine and return results in seconds but the subreport is where my problem is. All of the parameters are being passed to the subreport correctly and I don’t receive any errors. It’s just that the report eventually times out. If I try to run the subreport as a standalone report, I get the same results – it times out. But if I run the same exact query in SSMS declaring, setting and using the parameters, it returns data in seconds.
It’s not the performance of the server or SSRS (that I’m aware of) because I have hundreds of reports and they render data fairly quickly. So it’s something about this particular report.
Does anyone have any suggestions as to what I can modify to improve the performance or test to isolate the problem? I know the first answer might be to increase the SystemReportTimeout setting but if running that same query in SSMS took a long time to return records, then I would accept that. But that’s not the case. The query runs fine in SSMS. So, again, unless I’m missing something, I would expect the same performance in rendering the report (understanding that it’s not exact but should be fairly close).
I really appreciate any assistance on this.
Thanks!
Ronnie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

How is the dataset in the subreport getting the data? Is it running a query against the datasource or a stored procedure? If you can write it as a stored procedure I would try that first and see what your performance is like.

Joie Andrew
"Since 1982"

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 2

Hi Joie,

Thanks for your suggestion. You are pretty much correct. I figured out the problem to be related to the parameter.

The table has over 40 million rows and one of the columns that I'm filtering using a parameter, I created a dataset for it. Initially, the dataset was used to generate a drop down list value for that parameter, but that took too long and I decided to set the Available Value parameter property to none since I was passing the value to the parameter from the primary report.

What I didn't realize is that even though I wasn't using that dataset for the parameter, because there was a query associated with the dataset, SSRS must still run that query and load it into memory. Once I deleted that dataset, it rendered results much faster than before.

The one thing that I haven't tested was setting up a SP to run the query and just call that. Thanks to your suggestion, I will give that a try for future scenarios using large datasets. I keep forgetting that SP's are supposed to improve overall performance anyway.

Thanks for your reply!!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

What I didn't realize is that even though I wasn't using that dataset for the parameter, because there was a query associated with the dataset, SSRS must still run that query and load it into memory.

Yeah that can kill your report performance if you are not careful. I know I try out various things on reports while designing them and it is really easy to forget to delete one that you might not need and have that kill performance. Especially if it is pulling back tons of records with large values

Glad you got it working!

Joie Andrew
"Since 1982"

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 4

While I have your attention, do you have any suggestions on how I can query on a value with a single quote in the middle of a string of text?

Going back to this particular report, the one column that I had setup in that dataset is a varchar(max) data type and has a set of single quotes in the middle of the text. Ex: This is the day 'it is supposed to start to warm up' and look nice outside.

Now, if I were querying on that specific string, I would just use 2 single quotes on both ends to escape the special character such as:

'This is the day ''it is supposed to start to warm up'' and look nice outside.'

But in this case, I don't know where the single quotes will be positioned. As well as I'm passing the string to a parameter (variable) such as @TEXT.

I wrote a function to remove the quotes from that column but that takes a while to complete and new data will be loaded in the table every week. I will have to re-run that function every week.

Just wondering if there is a better way to handle a situation like this.

Thanks!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

So you are looking for a string with single quotes in your WHERE clause, or you are just returning data with single quotes in them?

Joie Andrew
"Since 1982"

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 6

I'm actually returning data with single quotes in the string. So, in my query, I'm including a column name "Reason" and the data in that column, pretty much every row, consists of a string of words and numbers (IP address in most cases) that have single quotes in the string. For example, the string will look like this - A dog walking down the street on 'today looked like my' old dog.

See the single quotes around "today looked like my"? And in the next row, the quotes could be wrapped around another set of words. So, I don't know where they will appear, just that they do appear.

And so, when I pass that column to the sub-report, it will return zero rows because I believe it has something to do with the single quotes. I tried to replace the quotes that I'm passing to the sub-report using an expression but either I'm not doing something right or it just doesn't work - =Replace(Field!Reason.Value, " ' ", " ").

I wrote a SQL function to remove the quotes and since the data that gets inserted into the table is imported, as a workaround, I remove the quotes before inserting into the table. That definitely increases the time it takes to complete.

So, that's my dilemma but I can make it work - maybe not the most efficient way but I can get it.

Thanks,