Adding "From Query" parameters degrades performance

Hi list,
This is the second time I've posted this question, the second time I've
created a report with "From Query" parameters and the second time I've had
problems with performance with the "From Query" parameters...
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
Here's the question I posed before, unfortunately I've recieved no
response.. Can someone help, has anyone else noticed the same problem...?
The problem I'm having is very easy to reproduce...
On the main menu in report designer... In the Report->Report Parameters
property page there are two types of
parameters Non-Queried and From Query. Non-Queried appears to prompt the
user for its value while the From Query parameter appears to be populated
from the query as it's name implies by assigning a column from the dataset.
I've created several of these type of parameters because I need a way to
have a page header with data from the query repeat on every page since I
cannot put Fields in the page header and using a table with the
repeatonnewpage doesn't seem to work this was my only viable solution. If
you look at my posts for the last couple days you'll get an idea of what I'm
trying to do. So my question remains why would creating From Query
parameters slow the execution of my report...? Am I using them incorrectly
is there another direction I can take to to achieve my goal...
Thanks,
DanI use this all the time without any performance problem whatsoever. A few
questions for you. How many parameters do you have in the report? How many
rows are being returned? How long does it take for each of them to run from
outside of RS (from Query Analyzer).
Am I right in assuming the time taken is for the report to come up and
prompt for the parameters? It is not when you try to then view the report is
it?
Try a report that does nothing except have the report parameters based on
the queries. This would help pinpoint if it is the parameters or something
else.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>|||Hi Bruce,
Thanks for your reply, in this particular report I have 47 "From Query"
parameters which is less than the previous report I built which seemed to
take longer. For the report I'm working on now I have a header and a
subreport, the header returns one row of data and contains the from query
parameters. Strangely enough the troubles I was having with the header
report have seemed to clear up, although the first couple of times I tried
to run the report the report previewer indicated that my report had been
canceled and then I would click the View Report button again and the report
would run. My first report returned several rows of data but the data was
repeated for every line of detail this is the one that I'm continually
having performance problems with I'm assigning the repeated data to the From
Query parameters.
Dan
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> I use this all the time without any performance problem whatsoever. A few
> questions for you. How many parameters do you have in the report? How many
> rows are being returned? How long does it take for each of them to run
from
> outside of RS (from Query Analyzer).
> Am I right in assuming the time taken is for the report to come up and
> prompt for the parameters? It is not when you try to then view the report
is
> it?
> Try a report that does nothing except have the report parameters based on
> the queries. This would help pinpoint if it is the parameters or something
> else.
> Bruce L-C
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > Hi list,
> >
> > This is the second time I've posted this question, the second time I've
> > created a report with "From Query" parameters and the second time I've
had
> > problems with performance with the "From Query" parameters...
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> >
> > Here's the question I posed before, unfortunately I've recieved no
> > response.. Can someone help, has anyone else noticed the same
problem...?
> > The problem I'm having is very easy to reproduce...
> >
> > On the main menu in report designer... In the Report->Report Parameters
> > property page there are two types of
> > parameters Non-Queried and From Query. Non-Queried appears to prompt the
> > user for its value while the From Query parameter appears to be
populated
> > from the query as it's name implies by assigning a column from the
> dataset.
> > I've created several of these type of parameters because I need a way to
> > have a page header with data from the query repeat on every page since I
> > cannot put Fields in the page header and using a table with the
> > repeatonnewpage doesn't seem to work this was my only viable solution.
If
> > you look at my posts for the last couple days you'll get an idea of what
> I'm
> > trying to do. So my question remains why would creating From Query
> > parameters slow the execution of my report...? Am I using them
incorrectly
> > is there another direction I can take to to achieve my goal...
> >
> > Thanks,
> >
> > Dan
> >
> >
> >
>|||One other issue to look at as well is whether the performance issue is seen
in only the development environment versus both development and after
deployment. That would be one other issue to consider.
Bruce L-C
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:uxjpJzrjEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi Bruce,
> Thanks for your reply, in this particular report I have 47 "From Query"
> parameters which is less than the previous report I built which seemed to
> take longer. For the report I'm working on now I have a header and a
> subreport, the header returns one row of data and contains the from query
> parameters. Strangely enough the troubles I was having with the header
> report have seemed to clear up, although the first couple of times I tried
> to run the report the report previewer indicated that my report had been
> canceled and then I would click the View Report button again and the
report
> would run. My first report returned several rows of data but the data was
> repeated for every line of detail this is the one that I'm continually
> having performance problems with I'm assigning the repeated data to the
From
> Query parameters.
> Dan
>
>
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OO4WMgrjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> > I use this all the time without any performance problem whatsoever. A
few
> > questions for you. How many parameters do you have in the report? How
many
> > rows are being returned? How long does it take for each of them to run
> from
> > outside of RS (from Query Analyzer).
> >
> > Am I right in assuming the time taken is for the report to come up and
> > prompt for the parameters? It is not when you try to then view the
report
> is
> > it?
> >
> > Try a report that does nothing except have the report parameters based
on
> > the queries. This would help pinpoint if it is the parameters or
something
> > else.
> >
> > Bruce L-C
> >
> >
> > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
> > in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> > > Hi list,
> > >
> > > This is the second time I've posted this question, the second time
I've
> > > created a report with "From Query" parameters and the second time I've
> had
> > > problems with performance with the "From Query" parameters...
> > >
> >
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> > >
> > > Here's the question I posed before, unfortunately I've recieved no
> > > response.. Can someone help, has anyone else noticed the same
> problem...?
> > > The problem I'm having is very easy to reproduce...
> > >
> > > On the main menu in report designer... In the Report->Report
Parameters
> > > property page there are two types of
> > > parameters Non-Queried and From Query. Non-Queried appears to prompt
the
> > > user for its value while the From Query parameter appears to be
> populated
> > > from the query as it's name implies by assigning a column from the
> > dataset.
> > > I've created several of these type of parameters because I need a way
to
> > > have a page header with data from the query repeat on every page since
I
> > > cannot put Fields in the page header and using a table with the
> > > repeatonnewpage doesn't seem to work this was my only viable solution.
> If
> > > you look at my posts for the last couple days you'll get an idea of
what
> > I'm
> > > trying to do. So my question remains why would creating From Query
> > > parameters slow the execution of my report...? Am I using them
> incorrectly
> > > is there another direction I can take to to achieve my goal...
> > >
> > > Thanks,
> > >
> > > Dan
> > >
> > >
> > >
> >
> >
>|||I think the entire query is first run without parameters. Then from the
results of the query, the parameters are retrieved making them available for
selection. That would be why you would see a performance issue, especially
with larger sets of data.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%2392SqZrjEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi list,
> This is the second time I've posted this question, the second time I've
> created a report with "From Query" parameters and the second time I've had
> problems with performance with the "From Query" parameters...
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=eOTaQ1ufEHA.3024%40TK2MSFTNGP10.phx.gbl&rnum=1&prev=/groups%3Fq%3DFrom%2BQuery%2Bparameters%2Bslows%2Bquery%2Bmicrosoft.public.sqlserver.reportingsvcs%26ie%3DUTF-8%26hl%3Den%26btnG%3DGoogle%2BSearch
> Here's the question I posed before, unfortunately I've recieved no
> response.. Can someone help, has anyone else noticed the same problem...?
> The problem I'm having is very easy to reproduce...
> On the main menu in report designer... In the Report->Report Parameters
> property page there are two types of
> parameters Non-Queried and From Query. Non-Queried appears to prompt the
> user for its value while the From Query parameter appears to be populated
> from the query as it's name implies by assigning a column from the
dataset.
> I've created several of these type of parameters because I need a way to
> have a page header with data from the query repeat on every page since I
> cannot put Fields in the page header and using a table with the
> repeatonnewpage doesn't seem to work this was my only viable solution. If
> you look at my posts for the last couple days you'll get an idea of what
I'm
> trying to do. So my question remains why would creating From Query
> parameters slow the execution of my report...? Am I using them incorrectly
> is there another direction I can take to to achieve my goal...
> Thanks,
> Dan
>
>