i have to develop a cryatal report from SQL database.
I have a option to choose stored procedure OR a view
i need to know which will work better on a large network in terms of speed a
nd performance
Thanx in advancessaud wrote:
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
Athough there will be a little less network traffic involved in calling a
stored procedure as opposed to selecting from a view, the network is pretty
much irrelevant to this question, unless you are doing something like
returning all the records to Crystal where they are filtered, grouped and
sorted. With a stored procedure, all of this activity can take place
_before_ the records are sent over the wire, so you may see an improvement
in network traffic in that respect.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||I would definitely choose stored procedures over views.
1. In a stored procedure, you can use variables and temporary tables to make
complex calculations from varios sources. In a view, you are pretty limited.
With stored procedures you can avoid using e.g. array variables in Crystal
Reports and keep it as a simple presentation layer.
2. By avoiding calculations in Crystal Reports, you can decrease network
traffic a lot. By just sending the 20 resulting rows instead of the 20000
rows involved in the caculation, you could gain a lot in performance.
To sum up: Do your calculations in stored procedures and your presentation
in Crystal Reports.
- Kristoffer -
"ssaud" <ssaud.1l655w@.mail.codecomments.com> wrote in message
news:ssaud.1l655w@.mail.codecomments.com...
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
>
> --
> ssaud
> ---
> Posted via http://www.codecomments.com
> ---
>|||Using stored procedures allows you to execute the same execution plan for
the report, therefore cutting down on system resources required to complete
the query.
"ssaud" <ssaud.1l655w@.mail.codecomments.com> wrote in message
news:ssaud.1l655w@.mail.codecomments.com...
> i have to develop a cryatal report from SQL database.
> I have a option to choose stored procedure OR a view
> i need to know which will work better on a large network in terms of
> speed and performance
> Thanx in advance
>
> --
> ssaud
> ---
> Posted via http://www.codecomments.com
> ---
>
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Friday, March 30, 2012
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?
You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>
|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>
|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes about
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CPU
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture the
execution plan at runtime, I might be able to explain why the performance can
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
--
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorrow.
> If you need to specify a hint it is possible that the query can be rewritten
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> > Does anyone know how to track the actual execution plan the server uses? I
> > have one query designed for a frequently run report that normally takes
> > about
> > 3 seconds to return. However, the Profiler logs show me that in certain
> > cases, it took more than 3 minutes to return and it cost a huge amount of
> > CPU
> > time. However, when I test it manually, it always returns in less than 3
> > seconds and nothing wrong in the execution plan. I am wondering that at
> > runtime, the server might decide to choose a different plan, especially
> > when
> > the report is called by multiple users at the same time. If I can capture
> > the
> > execution plan at runtime, I might be able to explain why the performance
> > can
> > vary so dramatically, and find a way to tune it.
> >
> > Another thing, not sure if it is related or not. I am using table hint to
> > direct the server to use one specific index in order to speed it up.
> > Because
> > I noticed if I don't, server sometimes choose a cluster index scan which
> > is
> > too slow and costly. So, if I use table hint in the query and several
> > instances of it are called concurrently, is there any performance
> > concerns?
> >
> >
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
performance trace
Does anyone know how to track the actual execution plan the server uses? I
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
have one query designed for a frequently run report that normally takes abou
t
3 seconds to return. However, the Profiler logs show me that in certain
cases, it took more than 3 minutes to return and it cost a huge amount of CP
U
time. However, when I test it manually, it always returns in less than 3
seconds and nothing wrong in the execution plan. I am wondering that at
runtime, the server might decide to choose a different plan, especially when
the report is called by multiple users at the same time. If I can capture th
e
execution plan at runtime, I might be able to explain why the performance ca
n
vary so dramatically, and find a way to tune it.
Another thing, not sure if it is related or not. I am using table hint to
direct the server to use one specific index in order to speed it up. Because
I noticed if I don't, server sometimes choose a cluster index scan which is
too slow and costly. So, if I use table hint in the query and several
instances of it are called concurrently, is there any performance concerns?You can run a trace with a filter for that particular sp and include the
showplan event. That way you can see each time it is called what it is
actually using for a plan. The only real performance concern with a hint is
that you never let SQL Server determine what the best way may be.
Conditions and data can change and what may be OK today may not be tomorrow.
If you need to specify a hint it is possible that the query can be rewritten
so that the optimizer always chooses the right plan.
Andrew J. Kelly SQL MVP
"Joseph" <Joseph@.discussions.microsoft.com> wrote in message
news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
> Does anyone know how to track the actual execution plan the server uses? I
> have one query designed for a frequently run report that normally takes
> about
> 3 seconds to return. However, the Profiler logs show me that in certain
> cases, it took more than 3 minutes to return and it cost a huge amount of
> CPU
> time. However, when I test it manually, it always returns in less than 3
> seconds and nothing wrong in the execution plan. I am wondering that at
> runtime, the server might decide to choose a different plan, especially
> when
> the report is called by multiple users at the same time. If I can capture
> the
> execution plan at runtime, I might be able to explain why the performance
> can
> vary so dramatically, and find a way to tune it.
> Another thing, not sure if it is related or not. I am using table hint to
> direct the server to use one specific index in order to speed it up.
> Because
> I noticed if I don't, server sometimes choose a cluster index scan which
> is
> too slow and costly. So, if I use table hint in the query and several
> instances of it are called concurrently, is there any performance
> concerns?
>|||Thanks, Andrew, for the tip. However, when I include the 'Execution Plan'
event, it does not matter if I set up a filter for that specific SP or not,
it shows all execution plans for all queries running on the server. I tried
to include 'Show Plan Text', but the capture the text data does not show any
details. Any idea?
"Andrew J. Kelly" wrote:
> You can run a trace with a filter for that particular sp and include the
> showplan event. That way you can see each time it is called what it is
> actually using for a plan. The only real performance concern with a hint
is
> that you never let SQL Server determine what the best way may be.
> Conditions and data can change and what may be OK today may not be tomorro
w.
> If you need to specify a hint it is possible that the query can be rewritt
en
> so that the optimizer always chooses the right plan.
> --
> Andrew J. Kelly SQL MVP
>
> "Joseph" <Joseph@.discussions.microsoft.com> wrote in message
> news:CD9BE668-ADFD-4960-BAF9-CF31A026B074@.microsoft.com...
>
>|||Joseph wrote:
> Thanks, Andrew, for the tip. However, when I include the 'Execution
> Plan' event, it does not matter if I set up a filter for that
> specific SP or not, it shows all execution plans for all queries
> running on the server. I tried to include 'Show Plan Text', but the
> capture the text data does not show any details. Any idea?
>
Execution Plan requires only TextData. All other execucution plan events
require the BinaryData column as well. To filter on the procedure, use
the ObjectID. ObjectName does not filter on SP-related events. In any
case, any event you select for the trace that does not map to the
ObjectID column shows up. Try and stick to the
SP:Starting/Completed/StmtStarting/StmtCompleted events to avoid
excessive trace output.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Monday, March 26, 2012
Performance Question
Just curious if anyone has experience with this.
SSRS2005
I have a report that has a dataset and populates a table.
I have 8 sub-reports that are placed on this report.
Is this approach the best for performance, or should I add the other 8
datasets to this one report,
and then include a table for each of them.
Just wondering what kind of overhead this is creating using the Sub Reports.
Thanks in advance for your reply'sIt sounds like this not a master-detail type of thing. I use subreports if I
plan on using the report inside multiple reports or if I am doing
master-detail. Otherwise I put it all in the same report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cexley@.enableconsulting.com> wrote in message
news:%23Fgv55x4GHA.772@.TK2MSFTNGP02.phx.gbl...
> Just curious if anyone has experience with this.
> SSRS2005
> I have a report that has a dataset and populates a table.
> I have 8 sub-reports that are placed on this report.
> Is this approach the best for performance, or should I add the other 8
> datasets to this one report,
> and then include a table for each of them.
> Just wondering what kind of overhead this is creating using the Sub
> Reports.
> Thanks in advance for your reply's
>|||Thanks Bruce. I created them as seperate reports so that I could use them
in multiple places. I was just wondering about performance hits and whether
there was a preferred method of achieving better performace.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ercR5Oy4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> It sounds like this not a master-detail type of thing. I use subreports if
> I plan on using the report inside multiple reports or if I am doing
> master-detail. Otherwise I put it all in the same report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cexley@.enableconsulting.com> wrote in message
> news:%23Fgv55x4GHA.772@.TK2MSFTNGP02.phx.gbl...
>> Just curious if anyone has experience with this.
>> SSRS2005
>> I have a report that has a dataset and populates a table.
>> I have 8 sub-reports that are placed on this report.
>> Is this approach the best for performance, or should I add the other 8
>> datasets to this one report,
>> and then include a table for each of them.
>> Just wondering what kind of overhead this is creating using the Sub
>> Reports.
>> Thanks in advance for your reply's
>>
>
SSRS2005
I have a report that has a dataset and populates a table.
I have 8 sub-reports that are placed on this report.
Is this approach the best for performance, or should I add the other 8
datasets to this one report,
and then include a table for each of them.
Just wondering what kind of overhead this is creating using the Sub Reports.
Thanks in advance for your reply'sIt sounds like this not a master-detail type of thing. I use subreports if I
plan on using the report inside multiple reports or if I am doing
master-detail. Otherwise I put it all in the same report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cexley@.enableconsulting.com> wrote in message
news:%23Fgv55x4GHA.772@.TK2MSFTNGP02.phx.gbl...
> Just curious if anyone has experience with this.
> SSRS2005
> I have a report that has a dataset and populates a table.
> I have 8 sub-reports that are placed on this report.
> Is this approach the best for performance, or should I add the other 8
> datasets to this one report,
> and then include a table for each of them.
> Just wondering what kind of overhead this is creating using the Sub
> Reports.
> Thanks in advance for your reply's
>|||Thanks Bruce. I created them as seperate reports so that I could use them
in multiple places. I was just wondering about performance hits and whether
there was a preferred method of achieving better performace.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ercR5Oy4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> It sounds like this not a master-detail type of thing. I use subreports if
> I plan on using the report inside multiple reports or if I am doing
> master-detail. Otherwise I put it all in the same report.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cexley@.enableconsulting.com> wrote in message
> news:%23Fgv55x4GHA.772@.TK2MSFTNGP02.phx.gbl...
>> Just curious if anyone has experience with this.
>> SSRS2005
>> I have a report that has a dataset and populates a table.
>> I have 8 sub-reports that are placed on this report.
>> Is this approach the best for performance, or should I add the other 8
>> datasets to this one report,
>> and then include a table for each of them.
>> Just wondering what kind of overhead this is creating using the Sub
>> Reports.
>> Thanks in advance for your reply's
>>
>
Performance Question
I have a stored procedure that a report uses. When I run this sp in
management studio it takes about four and a half minutes to run. When I run
the report, the report takes more than 15 minutes to generate. Any idea why
there is such a large difference in the time it takes to run the sp vs
generate the report?
Thanks.How many records are returned? The number of records being rendered makes a
big difference with Reporting Services.
Also, a 4 1/2 minute report is very long, have you looked into optimizing
it. For instance, does your database need some additional indexes?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I have a stored procedure that a report uses. When I run this sp in
>management studio it takes about four and a half minutes to run. When I
>run the report, the report takes more than 15 minutes to generate. Any
>idea why there is such a large difference in the time it takes to run the
>sp vs generate the report?
> Thanks.
>|||Where is the data located?
Maybe on the reporting server it takes longer to transmit the info.
"Tim Kelley" wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.
>
>|||How is the data being rendered? Are you using a matrix versus a table
to display the data?
HTH
Jason Strate
On Oct 15, 2:27 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.|||The data is stored on a different SQL server.
Tim
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:99D473D3-5B0C-44DD-8326-5D63415A3510@.microsoft.com...
> Where is the data located?
> Maybe on the reporting server it takes longer to transmit the info.
>
>
> "Tim Kelley" wrote:
>> I have a stored procedure that a report uses. When I run this sp in
>> management studio it takes about four and a half minutes to run. When I
>> run
>> the report, the report takes more than 15 minutes to generate. Any idea
>> why
>> there is such a large difference in the time it takes to run the sp vs
>> generate the report?
>> Thanks.
>>|||Before optimizing the stored procedure it would run for 45 minutes and not
finish. The sp returns 998 records.
Tim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
> How many records are returned? The number of records being rendered makes
> a big difference with Reporting Services.
> Also, a 4 1/2 minute report is very long, have you looked into optimizing
> it. For instance, does your database need some additional indexes?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>|||That is not many records so rendering should not be an issue. Try using With
Recompile when creating the stored procedure and see if that makes any
difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ubvT7s2DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> Before optimizing the stored procedure it would run for 45 minutes and not
> finish. The sp returns 998 records.
> Tim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
>> How many records are returned? The number of records being rendered makes
>> a big difference with Reporting Services.
>> Also, a 4 1/2 minute report is very long, have you looked into optimizing
>> it. For instance, does your database need some additional indexes?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>>
>
management studio it takes about four and a half minutes to run. When I run
the report, the report takes more than 15 minutes to generate. Any idea why
there is such a large difference in the time it takes to run the sp vs
generate the report?
Thanks.How many records are returned? The number of records being rendered makes a
big difference with Reporting Services.
Also, a 4 1/2 minute report is very long, have you looked into optimizing
it. For instance, does your database need some additional indexes?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I have a stored procedure that a report uses. When I run this sp in
>management studio it takes about four and a half minutes to run. When I
>run the report, the report takes more than 15 minutes to generate. Any
>idea why there is such a large difference in the time it takes to run the
>sp vs generate the report?
> Thanks.
>|||Where is the data located?
Maybe on the reporting server it takes longer to transmit the info.
"Tim Kelley" wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.
>
>|||How is the data being rendered? Are you using a matrix versus a table
to display the data?
HTH
Jason Strate
On Oct 15, 2:27 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.|||The data is stored on a different SQL server.
Tim
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:99D473D3-5B0C-44DD-8326-5D63415A3510@.microsoft.com...
> Where is the data located?
> Maybe on the reporting server it takes longer to transmit the info.
>
>
> "Tim Kelley" wrote:
>> I have a stored procedure that a report uses. When I run this sp in
>> management studio it takes about four and a half minutes to run. When I
>> run
>> the report, the report takes more than 15 minutes to generate. Any idea
>> why
>> there is such a large difference in the time it takes to run the sp vs
>> generate the report?
>> Thanks.
>>|||Before optimizing the stored procedure it would run for 45 minutes and not
finish. The sp returns 998 records.
Tim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
> How many records are returned? The number of records being rendered makes
> a big difference with Reporting Services.
> Also, a 4 1/2 minute report is very long, have you looked into optimizing
> it. For instance, does your database need some additional indexes?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>|||That is not many records so rendering should not be an issue. Try using With
Recompile when creating the stored procedure and see if that makes any
difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ubvT7s2DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> Before optimizing the stored procedure it would run for 45 minutes and not
> finish. The sp returns 998 records.
> Tim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
>> How many records are returned? The number of records being rendered makes
>> a big difference with Reporting Services.
>> Also, a 4 1/2 minute report is very long, have you looked into optimizing
>> it. For instance, does your database need some additional indexes?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>>
>
Friday, March 23, 2012
Performance Question
Hello Everybody,
I do have a question abt performance of one of my report store procedure.
We have a reporing application using Microsoft Reporting Services a Report
Front End and SQL SERVER 2000 as a DB. I have written one report store proc.
That report store proc is taking arround 30 sec to run in Query Analyzer. I
had opened 10 query analyzer windows and ran that report at same time from
each window and it is taking arround 35-40 sec to run. But if 10 people
access that same report from Report Server at same time, it is taking very
long time.. arround 8-10 minutes...
so really wondering...what would be the reason. I have proper indexes
created on all the appropriate columns..
So pls let me know what i can do ?
ThanksGoing from 30 seconds to 10 minutes sounds like blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3C00A9E-9D21-4B58-98B5-C2A2A042BEE7@.microsoft.com...
> Hello Everybody,
> I do have a question abt performance of one of my report store procedure.
> We have a reporing application using Microsoft Reporting Services a Report
> Front End and SQL SERVER 2000 as a DB. I have written one report store
> proc.
> That report store proc is taking arround 30 sec to run in Query Analyzer.
> I
> had opened 10 query analyzer windows and ran that report at same time from
> each window and it is taking arround 35-40 sec to run. But if 10 people
> access that same report from Report Server at same time, it is taking very
> long time.. arround 8-10 minutes...
> so really wondering...what would be the reason. I have proper indexes
> created on all the appropriate columns..
> So pls let me know what i can do ?
> Thanks
I do have a question abt performance of one of my report store procedure.
We have a reporing application using Microsoft Reporting Services a Report
Front End and SQL SERVER 2000 as a DB. I have written one report store proc.
That report store proc is taking arround 30 sec to run in Query Analyzer. I
had opened 10 query analyzer windows and ran that report at same time from
each window and it is taking arround 35-40 sec to run. But if 10 people
access that same report from Report Server at same time, it is taking very
long time.. arround 8-10 minutes...
so really wondering...what would be the reason. I have proper indexes
created on all the appropriate columns..
So pls let me know what i can do ?
ThanksGoing from 30 seconds to 10 minutes sounds like blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3C00A9E-9D21-4B58-98B5-C2A2A042BEE7@.microsoft.com...
> Hello Everybody,
> I do have a question abt performance of one of my report store procedure.
> We have a reporing application using Microsoft Reporting Services a Report
> Front End and SQL SERVER 2000 as a DB. I have written one report store
> proc.
> That report store proc is taking arround 30 sec to run in Query Analyzer.
> I
> had opened 10 query analyzer windows and ran that report at same time from
> each window and it is taking arround 35-40 sec to run. But if 10 people
> access that same report from Report Server at same time, it is taking very
> long time.. arround 8-10 minutes...
> so really wondering...what would be the reason. I have proper indexes
> created on all the appropriate columns..
> So pls let me know what i can do ?
> Thanks
Tuesday, March 20, 2012
Performance problem - Conditional Formatting on Table
I am having a preformance problem which I seam to have narrowed down to the
conditional formatting I have placed on a table in my report :
I have some custom code which declares variables for all the formatting
fields. The values are taken from a database and are populated by a call to a
function in the custom code. The function is called by a dummy table placed
at the top of the report that references a stored procedure which returns one
row with all the formatting values. The formatting properties on the report
then reference the variables defined in the custom code.
The main body of the report is made up of a list component that contains
some charts and a table. With the data I am testing it on this list component
gets repeated 75 times when the report is rendered.
The conditional formatting is present on the charts and the table.
Without the conditional formatting on the table the report runs in under 10
seconds. If I add the conditional formatting to the table the report takes
nearly 3 minutes to run.
The table produces about 10 rows on average.
Conditional formatting on the charts does not cause any noticeable
performance hit.
Althought there is a lot of conditional formatting on the table (all color,
background ,border and font properties) I would not have expected it to have
such a dramatic affect on the preformance.
The report is being rendered to HTML.
Any ides why this is causing such a performance hit ?
Is it particular formatting properties that are causing the hit ?
Or a work around ?I wouldn'be surprised if the function which access the database for the
formatting parameters is being called for each row of the report..
To make sure what is happening regarding this, run SQL Profiler and monitor
while you run the report...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Woodgnome" wrote:
> I am having a preformance problem which I seam to have narrowed down to the
> conditional formatting I have placed on a table in my report :
> I have some custom code which declares variables for all the formatting
> fields. The values are taken from a database and are populated by a call to a
> function in the custom code. The function is called by a dummy table placed
> at the top of the report that references a stored procedure which returns one
> row with all the formatting values. The formatting properties on the report
> then reference the variables defined in the custom code.
> The main body of the report is made up of a list component that contains
> some charts and a table. With the data I am testing it on this list component
> gets repeated 75 times when the report is rendered.
> The conditional formatting is present on the charts and the table.
> Without the conditional formatting on the table the report runs in under 10
> seconds. If I add the conditional formatting to the table the report takes
> nearly 3 minutes to run.
> The table produces about 10 rows on average.
> Conditional formatting on the charts does not cause any noticeable
> performance hit.
> Althought there is a lot of conditional formatting on the table (all color,
> background ,border and font properties) I would not have expected it to have
> such a dramatic affect on the preformance.
> The report is being rendered to HTML.
> Any ides why this is causing such a performance hit ?
> Is it particular formatting properties that are causing the hit ?
> Or a work around ?
>
>
>
>
>
>
conditional formatting I have placed on a table in my report :
I have some custom code which declares variables for all the formatting
fields. The values are taken from a database and are populated by a call to a
function in the custom code. The function is called by a dummy table placed
at the top of the report that references a stored procedure which returns one
row with all the formatting values. The formatting properties on the report
then reference the variables defined in the custom code.
The main body of the report is made up of a list component that contains
some charts and a table. With the data I am testing it on this list component
gets repeated 75 times when the report is rendered.
The conditional formatting is present on the charts and the table.
Without the conditional formatting on the table the report runs in under 10
seconds. If I add the conditional formatting to the table the report takes
nearly 3 minutes to run.
The table produces about 10 rows on average.
Conditional formatting on the charts does not cause any noticeable
performance hit.
Althought there is a lot of conditional formatting on the table (all color,
background ,border and font properties) I would not have expected it to have
such a dramatic affect on the preformance.
The report is being rendered to HTML.
Any ides why this is causing such a performance hit ?
Is it particular formatting properties that are causing the hit ?
Or a work around ?I wouldn'be surprised if the function which access the database for the
formatting parameters is being called for each row of the report..
To make sure what is happening regarding this, run SQL Profiler and monitor
while you run the report...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Woodgnome" wrote:
> I am having a preformance problem which I seam to have narrowed down to the
> conditional formatting I have placed on a table in my report :
> I have some custom code which declares variables for all the formatting
> fields. The values are taken from a database and are populated by a call to a
> function in the custom code. The function is called by a dummy table placed
> at the top of the report that references a stored procedure which returns one
> row with all the formatting values. The formatting properties on the report
> then reference the variables defined in the custom code.
> The main body of the report is made up of a list component that contains
> some charts and a table. With the data I am testing it on this list component
> gets repeated 75 times when the report is rendered.
> The conditional formatting is present on the charts and the table.
> Without the conditional formatting on the table the report runs in under 10
> seconds. If I add the conditional formatting to the table the report takes
> nearly 3 minutes to run.
> The table produces about 10 rows on average.
> Conditional formatting on the charts does not cause any noticeable
> performance hit.
> Althought there is a lot of conditional formatting on the table (all color,
> background ,border and font properties) I would not have expected it to have
> such a dramatic affect on the preformance.
> The report is being rendered to HTML.
> Any ides why this is causing such a performance hit ?
> Is it particular formatting properties that are causing the hit ?
> Or a work around ?
>
>
>
>
>
>
Monday, March 12, 2012
Performance Opening Report in Preview
Opening the report in preview is very slow, as if the main query is running
without the parameters. Is there a setting to make sure a dataset is not
queried until after the report view button is clicked?Do you have defaults for all the parameters? If so then it runs as soon as
you preview the report. To have it not run until you click on the view
button at least one parameter needs to be without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillC" <WillC@.discussions.microsoft.com> wrote in message
news:3578FA47-2646-419F-B683-32E8C70B4622@.microsoft.com...
> Opening the report in preview is very slow, as if the main query is
> running
> without the parameters. Is there a setting to make sure a dataset is not
> queried until after the report view button is clicked?
without the parameters. Is there a setting to make sure a dataset is not
queried until after the report view button is clicked?Do you have defaults for all the parameters? If so then it runs as soon as
you preview the report. To have it not run until you click on the view
button at least one parameter needs to be without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillC" <WillC@.discussions.microsoft.com> wrote in message
news:3578FA47-2646-419F-B683-32E8C70B4622@.microsoft.com...
> Opening the report in preview is very slow, as if the main query is
> running
> without the parameters. Is there a setting to make sure a dataset is not
> queried until after the report view button is clicked?
Friday, March 9, 2012
Performance on RS
Hi All,
Everytime when I run the report (by URL access), it need a very long time to
wait. After such init step, then it return normal speed when I run another
report!
Anyone can help ! Thanks!
TonyI noticed this when I first started with RS and what I did to get around it
is to have a report open on my desktop that auto executes every 5 minutes,
this keeps this going. I have been told that the below will work. If you
want to do my way then get a simple report, in report->properties set it to
refresh every 5 minutes (or 10 or 15, whatever).
Here is what Chris suggests:
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> Hi All,
> Everytime when I run the report (by URL access), it need a very long time
> to
> wait. After such init step, then it return normal speed when I run
> another
> report!
> Anyone can help ! Thanks!
> Tony
>|||Thank you Bruce! I try it!
"Bruce L-C [MVP]" wrote:
> I noticed this when I first started with RS and what I did to get around it
> is to have a report open on my desktop that auto executes every 5 minutes,
> this keeps this going. I have been told that the below will work. If you
> want to do my way then get a simple report, in report->properties set it to
> refresh every 5 minutes (or 10 or 15, whatever).
> Here is what Chris suggests:
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> > Hi All,
> >
> > Everytime when I run the report (by URL access), it need a very long time
> > to
> > wait. After such init step, then it return normal speed when I run
> > another
> > report!
> >
> > Anyone can help ! Thanks!
> >
> > Tony
> >
>
>
Everytime when I run the report (by URL access), it need a very long time to
wait. After such init step, then it return normal speed when I run another
report!
Anyone can help ! Thanks!
TonyI noticed this when I first started with RS and what I did to get around it
is to have a report open on my desktop that auto executes every 5 minutes,
this keeps this going. I have been told that the below will work. If you
want to do my way then get a simple report, in report->properties set it to
refresh every 5 minutes (or 10 or 15, whatever).
Here is what Chris suggests:
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> Hi All,
> Everytime when I run the report (by URL access), it need a very long time
> to
> wait. After such init step, then it return normal speed when I run
> another
> report!
> Anyone can help ! Thanks!
> Tony
>|||Thank you Bruce! I try it!
"Bruce L-C [MVP]" wrote:
> I noticed this when I first started with RS and what I did to get around it
> is to have a report open on my desktop that auto executes every 5 minutes,
> this keeps this going. I have been told that the below will work. If you
> want to do my way then get a simple report, in report->properties set it to
> refresh every 5 minutes (or 10 or 15, whatever).
> Here is what Chris suggests:
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> > Hi All,
> >
> > Everytime when I run the report (by URL access), it need a very long time
> > to
> > wait. After such init step, then it return normal speed when I run
> > another
> > report!
> >
> > Anyone can help ! Thanks!
> >
> > Tony
> >
>
>
Performance of SQL
I am using Reporting Services and I have a heavily parameterised report which
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?
Wednesday, March 7, 2012
Performance of multiple charts on a report (RenderStream issue with RS2000)
I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.
However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.
Any help would be greatly appreciated. Thanks! - Eric -
|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!Performance of multiple charts on a report (RenderStream issue with RS2000)
I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.
25-30 questions and each question has a bar chart next to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (well over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one image for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
I have a similar issue. I have a report that reports the movement of various different items that are purchased from our company. The process involves the running of a VB application that extracts, calculates and generates data on a reporting table. Then, the application runs the Reporting services reports and renders them to PDF files. The PDF files are then to be e-mailed to several managers at the company. Of the three reports, two of them only have one Graph. These have multiple pages with the graph at the top of the first page. Those two reports generate the one graph and multiple detail pages just fine.
However, the third report does a page break by item with a graph for each item at the top of its respective page. I am only getting the first page generated through the Print preview in Visual studio or rendered to the PDF file through the VB application. When I view the report through the web interface, I get all of the page breaks and graphs as expected. Several of the managers want to store these e-mails in outlook for future reference, but if they don't contain all of the data, it isn't much help to them.
Any help would be greatly appreciated. Thanks! - Eric -
|||Some more information for you all. When I tried to export the same report from the web interface to PDF, this failed to export anything beyond the first page as well. Now my management is really getting restless about this not working. HELP!Performance of multiple bar charts per report (RS2000)
I have a 'questionnaire analysis' report. The report consists of approx
25-30 questions and each question has a bar chart attached to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
JonYou could make the calls asynchronous (I'm assuming all calls are
synchronous).
"jonny" <jon@.jongianni.com> wrote in message
news:1147337806.062580.126100@.u72g2000cwu.googlegroups.com...
>I have a 'questionnaire analysis' report. The report consists of approx
> 25-30 questions and each question has a bar chart attached to it to
> show the average score out of five for each multiple choice answer
> within a question.
> The report ends up containing 25-30 bar graphs all contained within a
> List control. The report performance is acceptable using URL access but
> is exceptionally slow (over 1 minute) when using SOAP access.
> The performance bottle-neck seems to be the multiple calls to
> RenderStream to render all of the images that get generated when the
> report executes (one for each chart.)
> I was wondering if there was any way, still using SOAP, of avoiding
> using RenderStream or of speeding up performance. Report caching is not
> really an option in my scenario nor is splitting into multiple pages
> (user wants everything on screen at once.).
> Jon
>
25-30 questions and each question has a bar chart attached to it to
show the average score out of five for each multiple choice answer
within a question.
The report ends up containing 25-30 bar graphs all contained within a
List control. The report performance is acceptable using URL access but
is exceptionally slow (over 1 minute) when using SOAP access.
The performance bottle-neck seems to be the multiple calls to
RenderStream to render all of the images that get generated when the
report executes (one for each chart.)
I was wondering if there was any way, still using SOAP, of avoiding
using RenderStream or of speeding up performance. Report caching is not
really an option in my scenario nor is splitting into multiple pages
(user wants everything on screen at once.).
JonYou could make the calls asynchronous (I'm assuming all calls are
synchronous).
"jonny" <jon@.jongianni.com> wrote in message
news:1147337806.062580.126100@.u72g2000cwu.googlegroups.com...
>I have a 'questionnaire analysis' report. The report consists of approx
> 25-30 questions and each question has a bar chart attached to it to
> show the average score out of five for each multiple choice answer
> within a question.
> The report ends up containing 25-30 bar graphs all contained within a
> List control. The report performance is acceptable using URL access but
> is exceptionally slow (over 1 minute) when using SOAP access.
> The performance bottle-neck seems to be the multiple calls to
> RenderStream to render all of the images that get generated when the
> report executes (one for each chart.)
> I was wondering if there was any way, still using SOAP, of avoiding
> using RenderStream or of speeding up performance. Report caching is not
> really an option in my scenario nor is splitting into multiple pages
> (user wants everything on screen at once.).
> Jon
>
Subscribe to:
Posts (Atom)