Report performance of SQL Server

Question

Post reply

Report performance of SQL Server

LLSQL42

SSC Enthusiast

Points: 199
More actions
August 19, 2020 at 2:24 pm

#3781658

Hi,
Currently i having SSRS hosted in ASP NET Web Applications Portal.

It will connect to my SQL Server Stored Procedure for query records.
The issues is, when the month range of the report is wide, for example 1-2 month, report will facing slow to display.

Any hint? Or any suggestion/other mechanism which is i can fasten the SSRS on RDBMS SQL Server?

Viewing 5 posts - 1 through 4 (of 4 total)

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

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 1

Hi,

we are not able to look at your data. How big is your database, how much rows has your table?

What kind of stored procedure are you using?

If your stored procedure collects a lot of data, a query could take some time.

Kind regards,

Andreas

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

As Andreas has already eluded too, your going to need to go and tune the query doing the data gathering, be that reducing what its doing, adding indexes moving things around and once that is performant, go to the RDL itself and remove any unnecessary visuals, groupings, aggregations etc.

There's multiple phases to report rendering, majority of the time its in the get of the data so tune that first. Then its usually people get to visual happy when people just want a simple table of data and not pie charts , KPI metrics etc, so only get what you need and only show what you need to.

GroverVivek SSCommitted Points: 1674 More actions · Answer 3

So you can follow the below steps to tune the query if you have access to the database to run the same sp by passing the parameters coming through application call:

Run the sp in SSMS after setting statistics io setting on for the session
Take note of logical reads on the table. If there are more logical reads, then see if you are missing any index in the table.
Look code to remove extra joins, extra columns that are not needed. If breaking the large query into small query helps then you can try that also.

Regards
VG

sgmunson SSC Guru Points: 110640 More actions · Answer 4

LLSQL42 wrote:

Hi,
Currently i having SSRS hosted in ASP NET Web Applications Portal.
It will connect to my SQL Server Stored Procedure for query records.
The issues is, when the month range of the report is wide, for example 1-2 month, report will facing slow to display.
Any hint? Or any suggestion/other mechanism which is i can fasten the SSRS on RDBMS SQL Server?

Can't help but be curious as to why you would host SSRS the way you are (as highlighted in BOLD RED text above)... You can just as easily go directly to the web link in any compatible browser, so it would seem completely unnecessary...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)