Performance

Hello,
I am currently evaluating RS for one of our projects. I have a question
related to performance. I noticed that it takes between 2 and 5 seconds for
a report to get processed. I am talking about a very simple 'prototype'
report, with just a small table (less than 20 rows). I have profiled the
stored procedure that returns the data, it takes less than 100 ms to run,
and RS runs on the same machine as SQL Server. So it's report generation
itself that takes so long. My application is supposed to deliver on-demand,
real-time Web reports, so 5 seconds for a report is not quite acceptable (I
am currently using a reporting engine that I've developed over a couple of
weeks, and it renders the same report in less than 10 ms, but I want
something more sophisticated). I am pretty sure this is some
installation/configuration setting that I'm missing. Could anybody help?
Thanks a lot,
MikeThis does not seem right. I get a lag if I have not accessed report server
for awhile (or really, if no one has, not just me). I now keep a simple
report that I have set to autorefresh every minute so that I don't get this
lag. But assuming that is not what you are seeing, then something else is
going on. Most of my reports are subsecond to 1 to 2 seconds. And my 2
second ones are going against 1 - 10 million row tables. The data returned
is usually between 50 rows to at the most 2,000 rows. So I can say it would
surprise me if you couldn't get this as a subsecond report. In my case I am
going against Sybase but I have tested against SQL Server as well and SQL
Server screams compared to Sybase so if anything your performance should be
better than what I am seeing.
Where does the data for your report reside? Is it on SQL Server on the same
server you have RS installed on or is it somewhere else? What is the server
hardware like, how much RAM in particular?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am currently evaluating RS for one of our projects. I have a question
> related to performance. I noticed that it takes between 2 and 5 seconds
> for a report to get processed. I am talking about a very simple
> 'prototype' report, with just a small table (less than 20 rows). I have
> profiled the stored procedure that returns the data, it takes less than
> 100 ms to run, and RS runs on the same machine as SQL Server. So it's
> report generation itself that takes so long. My application is supposed to
> deliver on-demand, real-time Web reports, so 5 seconds for a report is not
> quite acceptable (I am currently using a reporting engine that I've
> developed over a couple of weeks, and it renders the same report in less
> than 10 ms, but I want something more sophisticated). I am pretty sure
> this is some installation/configuration setting that I'm missing. Could
> anybody help?
> Thanks a lot,
> Mike
>|||Hi Bruce,
And thank you for your response.
The data for my report resides in SQL Server, on the same machine as the RS.
The server is a dual P4 Xeon 3.0 GHz with 2 GB of RAM running Windows 2003.
Only SQL and RS run on this machine, and for now it's a development server
and no one else besides me touches it.
After reading your post I have copied the data on my workstation (dual Xeon
2.8GHz, 1GB RAM, Windows XP) and I get the same results. I really don't
think it's the hardware...
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OlnSkbU8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> This does not seem right. I get a lag if I have not accessed report server
> for awhile (or really, if no one has, not just me). I now keep a simple
> report that I have set to autorefresh every minute so that I don't get
> this lag. But assuming that is not what you are seeing, then something
> else is going on. Most of my reports are subsecond to 1 to 2 seconds. And
> my 2 second ones are going against 1 - 10 million row tables. The data
> returned is usually between 50 rows to at the most 2,000 rows. So I can
> say it would surprise me if you couldn't get this as a subsecond report.
> In my case I am going against Sybase but I have tested against SQL Server
> as well and SQL Server screams compared to Sybase so if anything your
> performance should be better than what I am seeing.
> Where does the data for your report reside? Is it on SQL Server on the
> same server you have RS installed on or is it somewhere else? What is the
> server hardware like, how much RAM in particular?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
> news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am currently evaluating RS for one of our projects. I have a question
>> related to performance. I noticed that it takes between 2 and 5 seconds
>> for a report to get processed. I am talking about a very simple
>> 'prototype' report, with just a small table (less than 20 rows). I have
>> profiled the stored procedure that returns the data, it takes less than
>> 100 ms to run, and RS runs on the same machine as SQL Server. So it's
>> report generation itself that takes so long. My application is supposed
>> to deliver on-demand, real-time Web reports, so 5 seconds for a report is
>> not quite acceptable (I am currently using a reporting engine that I've
>> developed over a couple of weeks, and it renders the same report in less
>> than 10 ms, but I want something more sophisticated). I am pretty sure
>> this is some installation/configuration setting that I'm missing. Could
>> anybody help?
>> Thanks a lot,
>> Mike
>|||One of my development servers is almost exactly what you have here. It
should be screaming for you. If you have a report against either pubs,
northwind, or adventureworks2000 that is slow for you then post the report
RDL and I'll check it out. You really should be seeing subsecond response
for simple report that you have described.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
news:uk3iGyo8EHA.2276@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> And thank you for your response.
> The data for my report resides in SQL Server, on the same machine as the
RS.
> The server is a dual P4 Xeon 3.0 GHz with 2 GB of RAM running Windows
2003.
> Only SQL and RS run on this machine, and for now it's a development server
> and no one else besides me touches it.
> After reading your post I have copied the data on my workstation (dual
Xeon
> 2.8GHz, 1GB RAM, Windows XP) and I get the same results. I really don't
> think it's the hardware...
> Mike
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OlnSkbU8EHA.1404@.TK2MSFTNGP11.phx.gbl...
> > This does not seem right. I get a lag if I have not accessed report
server
> > for awhile (or really, if no one has, not just me). I now keep a simple
> > report that I have set to autorefresh every minute so that I don't get
> > this lag. But assuming that is not what you are seeing, then something
> > else is going on. Most of my reports are subsecond to 1 to 2 seconds.
And
> > my 2 second ones are going against 1 - 10 million row tables. The data
> > returned is usually between 50 rows to at the most 2,000 rows. So I can
> > say it would surprise me if you couldn't get this as a subsecond report.
> > In my case I am going against Sybase but I have tested against SQL
Server
> > as well and SQL Server screams compared to Sybase so if anything your
> > performance should be better than what I am seeing.
> >
> > Where does the data for your report reside? Is it on SQL Server on the
> > same server you have RS installed on or is it somewhere else? What is
the
> > server hardware like, how much RAM in particular?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
> > news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> Hello,
> >>
> >> I am currently evaluating RS for one of our projects. I have a question
> >> related to performance. I noticed that it takes between 2 and 5 seconds
> >> for a report to get processed. I am talking about a very simple
> >> 'prototype' report, with just a small table (less than 20 rows). I have
> >> profiled the stored procedure that returns the data, it takes less than
> >> 100 ms to run, and RS runs on the same machine as SQL Server. So it's
> >> report generation itself that takes so long. My application is supposed
> >> to deliver on-demand, real-time Web reports, so 5 seconds for a report
is
> >> not quite acceptable (I am currently using a reporting engine that I've
> >> developed over a couple of weeks, and it renders the same report in
less
> >> than 10 ms, but I want something more sophisticated). I am pretty sure
> >> this is some installation/configuration setting that I'm missing. Could
> >> anybody help?
> >>
> >> Thanks a lot,
> >>
> >> Mike
> >>
> >
> >
>|||Could there be some network, firewall, or DNS settings that need to be
resolved? There may be a dependency on something external that is hindering
performance. I know it's all on the same machine, but sometimes even
internal traffic tries to use the network connection.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
news:uk3iGyo8EHA.2276@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> And thank you for your response.
> The data for my report resides in SQL Server, on the same machine as the
> RS. The server is a dual P4 Xeon 3.0 GHz with 2 GB of RAM running Windows
> 2003. Only SQL and RS run on this machine, and for now it's a development
> server and no one else besides me touches it.
> After reading your post I have copied the data on my workstation (dual
> Xeon 2.8GHz, 1GB RAM, Windows XP) and I get the same results. I really
> don't think it's the hardware...
> Mike
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OlnSkbU8EHA.1404@.TK2MSFTNGP11.phx.gbl...
>> This does not seem right. I get a lag if I have not accessed report
>> server for awhile (or really, if no one has, not just me). I now keep a
>> simple report that I have set to autorefresh every minute so that I don't
>> get this lag. But assuming that is not what you are seeing, then
>> something else is going on. Most of my reports are subsecond to 1 to 2
>> seconds. And my 2 second ones are going against 1 - 10 million row
>> tables. The data returned is usually between 50 rows to at the most 2,000
>> rows. So I can say it would surprise me if you couldn't get this as a
>> subsecond report. In my case I am going against Sybase but I have tested
>> against SQL Server as well and SQL Server screams compared to Sybase so
>> if anything your performance should be better than what I am seeing.
>> Where does the data for your report reside? Is it on SQL Server on the
>> same server you have RS installed on or is it somewhere else? What is the
>> server hardware like, how much RAM in particular?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
>> news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I am currently evaluating RS for one of our projects. I have a question
>> related to performance. I noticed that it takes between 2 and 5 seconds
>> for a report to get processed. I am talking about a very simple
>> 'prototype' report, with just a small table (less than 20 rows). I have
>> profiled the stored procedure that returns the data, it takes less than
>> 100 ms to run, and RS runs on the same machine as SQL Server. So it's
>> report generation itself that takes so long. My application is supposed
>> to deliver on-demand, real-time Web reports, so 5 seconds for a report
>> is not quite acceptable (I am currently using a reporting engine that
>> I've developed over a couple of weeks, and it renders the same report in
>> less than 10 ms, but I want something more sophisticated). I am pretty
>> sure this is some installation/configuration setting that I'm missing.
>> Could anybody help?
>> Thanks a lot,
>> Mike
>>
>|||I came across this in a blog by a MS RS developer. It mentions a problem
with anti-virus software that can slow things down
http://blogs.msdn.com/tudortr
He mentions a variety of things for performance so I suggest checking it out
but one thing stood out that I wondered might be going on for you:
"Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am currently evaluating RS for one of our projects. I have a question
> related to performance. I noticed that it takes between 2 and 5 seconds
for
> a report to get processed. I am talking about a very simple 'prototype'
> report, with just a small table (less than 20 rows). I have profiled the
> stored procedure that returns the data, it takes less than 100 ms to run,
> and RS runs on the same machine as SQL Server. So it's report generation
> itself that takes so long. My application is supposed to deliver
on-demand,
> real-time Web reports, so 5 seconds for a report is not quite acceptable
(I
> am currently using a reporting engine that I've developed over a couple of
> weeks, and it renders the same report in less than 10 ms, but I want
> something more sophisticated). I am pretty sure this is some
> installation/configuration setting that I'm missing. Could anybody help?
> Thanks a lot,
> Mike
>|||I came across some performance info on a blog from a developer. Lots of good
stuff but one thing in particular stood out that I wondered might be
happening to you.
>>>>>>>snip>>>>>>>
If performance is extremely bad even for single users, check the Application
Restarts counter in the ASP.NET category; some antivirus software is known
to "touch" configuration files, thus causing expansive Application Domain
restarts in the report server web service. For more information, search
http://support.microsoft.com/ for articles relating to "antivirus and
ASP.NET".
>>>>>>>snip>>>>>>>
Here is his blog:
http://blogs.msdn.com/tudortr
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Sarbu" <m_sarbu@.yahoo.com> wrote in message
news:uyqYq1T8EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am currently evaluating RS for one of our projects. I have a question
> related to performance. I noticed that it takes between 2 and 5 seconds
for
> a report to get processed. I am talking about a very simple 'prototype'
> report, with just a small table (less than 20 rows). I have profiled the
> stored procedure that returns the data, it takes less than 100 ms to run,
> and RS runs on the same machine as SQL Server. So it's report generation
> itself that takes so long. My application is supposed to deliver
on-demand,
> real-time Web reports, so 5 seconds for a report is not quite acceptable
(I
> am currently using a reporting engine that I've developed over a couple of
> weeks, and it renders the same report in less than 10 ms, but I want
> something more sophisticated). I am pretty sure this is some
> installation/configuration setting that I'm missing. Could anybody help?
> Thanks a lot,
> Mike
>