Cascading Parameter couse requery of other parameters

Cascading Parameter cousing fatal DB overhead and performance problem.
I have 2 parameters:
- Parameter A) Customers
- Parameter B) Contact Persons
Both have a dataset as list source.
In the dataset of B) is a reference to A).
If I choose a selectio in A) then B) becomes refreshed and the selection in
A) is used as parameter. That is how it should be.
BUT: A) is requiried too!!! :-O
In other reports I have more parameters and dependencies. The list sources
are allways datasets with many returning rows. And this behaviour results in
a big performance problem.
Even in the sample report described here it is a performance problem.
I can't believe it, that this is the ripeness of reporting services! Please
tell me somebody that this is not true and how I can solve the problem.I don't understand your problem. Is it that you don't want to require a
selection for customer? You can always add an All (do a union query).
Not sure why you have a performance problem, you shouldn't. Assuming that
you design it that each cascading parameter has no more that a few hundred
responses. I use cascading parameters all the time without a problem.
What do you consider many rows?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> Cascading Parameter cousing fatal DB overhead and performance problem.
> I have 2 parameters:
> - Parameter A) Customers
> - Parameter B) Contact Persons
> Both have a dataset as list source.
> In the dataset of B) is a reference to A).
> If I choose a selectio in A) then B) becomes refreshed and the selection
in
> A) is used as parameter. That is how it should be.
> BUT: A) is requiried too!!! :-O
> In other reports I have more parameters and dependencies. The list sources
> are allways datasets with many returning rows. And this behaviour results
in
> a big performance problem.
> Even in the sample report described here it is a performance problem.
> I can't believe it, that this is the ripeness of reporting services!
Please
> tell me somebody that this is not true and how I can solve the problem.|||Thank you for the fast answer!
My dropdownlist in the parameter "customers" must be queried only one time.
The list in parameter "Contact Person" must be filled each time a customer is
selected. Becouse this list shows all contact persons from the selected
customer.
In the parameters are several 1000 records displayed e.g. "Customers" or
"Projects".
You wrote "You can always add an All (do a union query)." What does this
means exactly. I use a dataset for the parameter "customer". The dataset
contains:
"Select CustID, CustName from Customers Order by Custname".
In the "Report Parameter" dialog I choose
Available Valus:
- From query
There is no other option to specify that this query should be executed only
one time.
I have to create reports with more then 5 cascading parameters. Some
parameters have several 1000 rows. If each selection will couse the requery
from all parameters it takes realy to long. Much more then 6 seconds (after
somebody choose a parameter) which is the "success criteria".
"Bruce L-C [MVP]" wrote:
> I don't understand your problem. Is it that you don't want to require a
> selection for customer? You can always add an All (do a union query).
> Not sure why you have a performance problem, you shouldn't. Assuming that
> you design it that each cascading parameter has no more that a few hundred
> responses. I use cascading parameters all the time without a problem.
> What do you consider many rows?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > Cascading Parameter cousing fatal DB overhead and performance problem.
> >
> > I have 2 parameters:
> > - Parameter A) Customers
> > - Parameter B) Contact Persons
> > Both have a dataset as list source.
> >
> > In the dataset of B) is a reference to A).
> >
> > If I choose a selectio in A) then B) becomes refreshed and the selection
> in
> > A) is used as parameter. That is how it should be.
> > BUT: A) is requiried too!!! :-O
> >
> > In other reports I have more parameters and dependencies. The list sources
> > are allways datasets with many returning rows. And this behaviour results
> in
> > a big performance problem.
> >
> > Even in the sample report described here it is a performance problem.
> >
> > I can't believe it, that this is the ripeness of reporting services!
> Please
> > tell me somebody that this is not true and how I can solve the problem.
>
>|||I don't think that the queries should be executed more than once, are you
sure they are? The customers query should execute only once as far as I know
(could be wrong, could be that my lists are short enough that I don't see
the delay).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> Thank you for the fast answer!
> My dropdownlist in the parameter "customers" must be queried only one
time.
> The list in parameter "Contact Person" must be filled each time a customer
is
> selected. Becouse this list shows all contact persons from the selected
> customer.
> In the parameters are several 1000 records displayed e.g. "Customers" or
> "Projects".
> You wrote "You can always add an All (do a union query)." What does this
> means exactly. I use a dataset for the parameter "customer". The dataset
> contains:
> "Select CustID, CustName from Customers Order by Custname".
> In the "Report Parameter" dialog I choose
> Available Valus:
> - From query
> There is no other option to specify that this query should be executed
only
> one time.
> I have to create reports with more then 5 cascading parameters. Some
> parameters have several 1000 rows. If each selection will couse the
requery
> from all parameters it takes realy to long. Much more then 6 seconds
(after
> somebody choose a parameter) which is the "success criteria".
>
>
> "Bruce L-C [MVP]" wrote:
> > I don't understand your problem. Is it that you don't want to require a
> > selection for customer? You can always add an All (do a union query).
> >
> > Not sure why you have a performance problem, you shouldn't. Assuming
that
> > you design it that each cascading parameter has no more that a few
hundred
> > responses. I use cascading parameters all the time without a problem.
> >
> > What do you consider many rows?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > > Cascading Parameter cousing fatal DB overhead and performance problem.
> > >
> > > I have 2 parameters:
> > > - Parameter A) Customers
> > > - Parameter B) Contact Persons
> > > Both have a dataset as list source.
> > >
> > > In the dataset of B) is a reference to A).
> > >
> > > If I choose a selectio in A) then B) becomes refreshed and the
selection
> > in
> > > A) is used as parameter. That is how it should be.
> > > BUT: A) is requiried too!!! :-O
> > >
> > > In other reports I have more parameters and dependencies. The list
sources
> > > are allways datasets with many returning rows. And this behaviour
results
> > in
> > > a big performance problem.
> > >
> > > Even in the sample report described here it is a performance problem.
> > >
> > > I can't believe it, that this is the ripeness of reporting services!
> > Please
> > > tell me somebody that this is not true and how I can solve the
problem.
> >
> >
> >|||100% for shure!
I replaced the SQL Statment with a call of a stored procedure. And the
stored procedure write a log entry in a table each time the SP is called.
Each time I select a entry in the 'Customer' Parameter the query for the
'customer' Parameter get executed. And (strictly correct) also the query for
the cascading parameter 'Contact Person'.
Just for the sake of completness: The database is on a oracle server. But
this could not be related to this behaviour, becouse Reporting Services fires
the refill of the parameter drop down list.
"Bruce L-C [MVP]" wrote:
> I don't think that the queries should be executed more than once, are you
> sure they are? The customers query should execute only once as far as I know
> (could be wrong, could be that my lists are short enough that I don't see
> the delay).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > Thank you for the fast answer!
> >
> > My dropdownlist in the parameter "customers" must be queried only one
> time.
> > The list in parameter "Contact Person" must be filled each time a customer
> is
> > selected. Becouse this list shows all contact persons from the selected
> > customer.
> >
> > In the parameters are several 1000 records displayed e.g. "Customers" or
> > "Projects".
> >
> > You wrote "You can always add an All (do a union query)." What does this
> > means exactly. I use a dataset for the parameter "customer". The dataset
> > contains:
> > "Select CustID, CustName from Customers Order by Custname".
> > In the "Report Parameter" dialog I choose
> > Available Valus:
> > - From query
> > There is no other option to specify that this query should be executed
> only
> > one time.
> >
> > I have to create reports with more then 5 cascading parameters. Some
> > parameters have several 1000 rows. If each selection will couse the
> requery
> > from all parameters it takes realy to long. Much more then 6 seconds
> (after
> > somebody choose a parameter) which is the "success criteria".
> >
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't understand your problem. Is it that you don't want to require a
> > > selection for customer? You can always add an All (do a union query).
> > >
> > > Not sure why you have a performance problem, you shouldn't. Assuming
> that
> > > you design it that each cascading parameter has no more that a few
> hundred
> > > responses. I use cascading parameters all the time without a problem.
> > >
> > > What do you consider many rows?
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > > > Cascading Parameter cousing fatal DB overhead and performance problem.
> > > >
> > > > I have 2 parameters:
> > > > - Parameter A) Customers
> > > > - Parameter B) Contact Persons
> > > > Both have a dataset as list source.
> > > >
> > > > In the dataset of B) is a reference to A).
> > > >
> > > > If I choose a selectio in A) then B) becomes refreshed and the
> selection
> > > in
> > > > A) is used as parameter. That is how it should be.
> > > > BUT: A) is requiried too!!! :-O
> > > >
> > > > In other reports I have more parameters and dependencies. The list
> sources
> > > > are allways datasets with many returning rows. And this behaviour
> results
> > > in
> > > > a big performance problem.
> > > >
> > > > Even in the sample report described here it is a performance problem.
> > > >
> > > > I can't believe it, that this is the ripeness of reporting services!
> > > Please
> > > > tell me somebody that this is not true and how I can solve the
> problem.
> > >
> > >
> > >
>
>|||Just to make sure I understand. You pick a customer and the customer sp is
executed. It only gets executed when you select a customer. It does not get
executed when you select a cascading parameter, correct?
If this is true, is it a real problem? Does the user keep the report open
and keep selecting different customers?
One possible solution is to have the customers be cascaded parameter itself.
Having several thousand records as the source for a listbox is not a good
idea. I have seen this be a problem with other products besides RS. My
suggestion is to somehow end up with a most a couple of hundred items in the
list.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> 100% for shure!
> I replaced the SQL Statment with a call of a stored procedure. And the
> stored procedure write a log entry in a table each time the SP is called.
> Each time I select a entry in the 'Customer' Parameter the query for the
> 'customer' Parameter get executed. And (strictly correct) also the query
> for
> the cascading parameter 'Contact Person'.
> Just for the sake of completness: The database is on a oracle server. But
> this could not be related to this behaviour, becouse Reporting Services
> fires
> the refill of the parameter drop down list.
> "Bruce L-C [MVP]" wrote:
>> I don't think that the queries should be executed more than once, are you
>> sure they are? The customers query should execute only once as far as I
>> know
>> (could be wrong, could be that my lists are short enough that I don't see
>> the delay).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
>> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
>> > Thank you for the fast answer!
>> >
>> > My dropdownlist in the parameter "customers" must be queried only one
>> time.
>> > The list in parameter "Contact Person" must be filled each time a
>> > customer
>> is
>> > selected. Becouse this list shows all contact persons from the selected
>> > customer.
>> >
>> > In the parameters are several 1000 records displayed e.g. "Customers"
>> > or
>> > "Projects".
>> >
>> > You wrote "You can always add an All (do a union query)." What does
>> > this
>> > means exactly. I use a dataset for the parameter "customer". The
>> > dataset
>> > contains:
>> > "Select CustID, CustName from Customers Order by Custname".
>> > In the "Report Parameter" dialog I choose
>> > Available Valus:
>> > - From query
>> > There is no other option to specify that this query should be executed
>> only
>> > one time.
>> >
>> > I have to create reports with more then 5 cascading parameters. Some
>> > parameters have several 1000 rows. If each selection will couse the
>> requery
>> > from all parameters it takes realy to long. Much more then 6 seconds
>> (after
>> > somebody choose a parameter) which is the "success criteria".
>> >
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't understand your problem. Is it that you don't want to require
>> > > a
>> > > selection for customer? You can always add an All (do a union query).
>> > >
>> > > Not sure why you have a performance problem, you shouldn't. Assuming
>> that
>> > > you design it that each cascading parameter has no more that a few
>> hundred
>> > > responses. I use cascading parameters all the time without a problem.
>> > >
>> > > What do you consider many rows?
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
>> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
>> > > > Cascading Parameter cousing fatal DB overhead and performance
>> > > > problem.
>> > > >
>> > > > I have 2 parameters:
>> > > > - Parameter A) Customers
>> > > > - Parameter B) Contact Persons
>> > > > Both have a dataset as list source.
>> > > >
>> > > > In the dataset of B) is a reference to A).
>> > > >
>> > > > If I choose a selectio in A) then B) becomes refreshed and the
>> selection
>> > > in
>> > > > A) is used as parameter. That is how it should be.
>> > > > BUT: A) is requiried too!!! :-O
>> > > >
>> > > > In other reports I have more parameters and dependencies. The list
>> sources
>> > > > are allways datasets with many returning rows. And this behaviour
>> results
>> > > in
>> > > > a big performance problem.
>> > > >
>> > > > Even in the sample report described here it is a performance
>> > > > problem.
>> > > >
>> > > > I can't believe it, that this is the ripeness of reporting
>> > > > services!
>> > > Please
>> > > > tell me somebody that this is not true and how I can solve the
>> problem.
>> > >
>> > >
>> > >
>>|||You understand it right!
I choose a customer, customer and contact person get requiried. If I choose
a contact person nothing get requiried.
Possibly your hint on making all parameters to cascading parameters help me
out of the mud. I could place a preselected parameter for language and refer
on it from all other parameter datasets. I'll test it.
The requirements are focused on filtering the data. It is a CRM Database.
I can't imagine how to keep the parameters less then several thousends.
Customer beginning with 'A' are more then 7000 for example. Projects in one
country are several thousends, Sales Persons are more then 1000. It is just a
middle sized company. And I'm in trouble at the beginning of the project.
Thank you very much for all your help on this!!!
"Bruce L-C [MVP]" wrote:
> Just to make sure I understand. You pick a customer and the customer sp is
> executed. It only gets executed when you select a customer. It does not get
> executed when you select a cascading parameter, correct?
> If this is true, is it a real problem? Does the user keep the report open
> and keep selecting different customers?
> One possible solution is to have the customers be cascaded parameter itself.
> Having several thousand records as the source for a listbox is not a good
> idea. I have seen this be a problem with other products besides RS. My
> suggestion is to somehow end up with a most a couple of hundred items in the
> list.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > 100% for shure!
> >
> > I replaced the SQL Statment with a call of a stored procedure. And the
> > stored procedure write a log entry in a table each time the SP is called.
> >
> > Each time I select a entry in the 'Customer' Parameter the query for the
> > 'customer' Parameter get executed. And (strictly correct) also the query
> > for
> > the cascading parameter 'Contact Person'.
> >
> > Just for the sake of completness: The database is on a oracle server. But
> > this could not be related to this behaviour, becouse Reporting Services
> > fires
> > the refill of the parameter drop down list.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I don't think that the queries should be executed more than once, are you
> >> sure they are? The customers query should execute only once as far as I
> >> know
> >> (could be wrong, could be that my lists are short enough that I don't see
> >> the delay).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> >> > Thank you for the fast answer!
> >> >
> >> > My dropdownlist in the parameter "customers" must be queried only one
> >> time.
> >> > The list in parameter "Contact Person" must be filled each time a
> >> > customer
> >> is
> >> > selected. Becouse this list shows all contact persons from the selected
> >> > customer.
> >> >
> >> > In the parameters are several 1000 records displayed e.g. "Customers"
> >> > or
> >> > "Projects".
> >> >
> >> > You wrote "You can always add an All (do a union query)." What does
> >> > this
> >> > means exactly. I use a dataset for the parameter "customer". The
> >> > dataset
> >> > contains:
> >> > "Select CustID, CustName from Customers Order by Custname".
> >> > In the "Report Parameter" dialog I choose
> >> > Available Valus:
> >> > - From query
> >> > There is no other option to specify that this query should be executed
> >> only
> >> > one time.
> >> >
> >> > I have to create reports with more then 5 cascading parameters. Some
> >> > parameters have several 1000 rows. If each selection will couse the
> >> requery
> >> > from all parameters it takes realy to long. Much more then 6 seconds
> >> (after
> >> > somebody choose a parameter) which is the "success criteria".
> >> >
> >> >
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > I don't understand your problem. Is it that you don't want to require
> >> > > a
> >> > > selection for customer? You can always add an All (do a union query).
> >> > >
> >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> >> that
> >> > > you design it that each cascading parameter has no more that a few
> >> hundred
> >> > > responses. I use cascading parameters all the time without a problem.
> >> > >
> >> > > What do you consider many rows?
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> >> > > > Cascading Parameter cousing fatal DB overhead and performance
> >> > > > problem.
> >> > > >
> >> > > > I have 2 parameters:
> >> > > > - Parameter A) Customers
> >> > > > - Parameter B) Contact Persons
> >> > > > Both have a dataset as list source.
> >> > > >
> >> > > > In the dataset of B) is a reference to A).
> >> > > >
> >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> >> selection
> >> > > in
> >> > > > A) is used as parameter. That is how it should be.
> >> > > > BUT: A) is requiried too!!! :-O
> >> > > >
> >> > > > In other reports I have more parameters and dependencies. The list
> >> sources
> >> > > > are allways datasets with many returning rows. And this behaviour
> >> results
> >> > > in
> >> > > > a big performance problem.
> >> > > >
> >> > > > Even in the sample report described here it is a performance
> >> > > > problem.
> >> > > >
> >> > > > I can't believe it, that this is the ripeness of reporting
> >> > > > services!
> >> > > Please
> >> > > > tell me somebody that this is not true and how I can solve the
> >> problem.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Dosn't work!
Now I made the customer parameter a cascading parameter from parameter
language. But the customer parameter get still requeried.
"Bruce L-C [MVP]" wrote:
> Just to make sure I understand. You pick a customer and the customer sp is
> executed. It only gets executed when you select a customer. It does not get
> executed when you select a cascading parameter, correct?
> If this is true, is it a real problem? Does the user keep the report open
> and keep selecting different customers?
> One possible solution is to have the customers be cascaded parameter itself.
> Having several thousand records as the source for a listbox is not a good
> idea. I have seen this be a problem with other products besides RS. My
> suggestion is to somehow end up with a most a couple of hundred items in the
> list.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > 100% for shure!
> >
> > I replaced the SQL Statment with a call of a stored procedure. And the
> > stored procedure write a log entry in a table each time the SP is called.
> >
> > Each time I select a entry in the 'Customer' Parameter the query for the
> > 'customer' Parameter get executed. And (strictly correct) also the query
> > for
> > the cascading parameter 'Contact Person'.
> >
> > Just for the sake of completness: The database is on a oracle server. But
> > this could not be related to this behaviour, becouse Reporting Services
> > fires
> > the refill of the parameter drop down list.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I don't think that the queries should be executed more than once, are you
> >> sure they are? The customers query should execute only once as far as I
> >> know
> >> (could be wrong, could be that my lists are short enough that I don't see
> >> the delay).
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> >> > Thank you for the fast answer!
> >> >
> >> > My dropdownlist in the parameter "customers" must be queried only one
> >> time.
> >> > The list in parameter "Contact Person" must be filled each time a
> >> > customer
> >> is
> >> > selected. Becouse this list shows all contact persons from the selected
> >> > customer.
> >> >
> >> > In the parameters are several 1000 records displayed e.g. "Customers"
> >> > or
> >> > "Projects".
> >> >
> >> > You wrote "You can always add an All (do a union query)." What does
> >> > this
> >> > means exactly. I use a dataset for the parameter "customer". The
> >> > dataset
> >> > contains:
> >> > "Select CustID, CustName from Customers Order by Custname".
> >> > In the "Report Parameter" dialog I choose
> >> > Available Valus:
> >> > - From query
> >> > There is no other option to specify that this query should be executed
> >> only
> >> > one time.
> >> >
> >> > I have to create reports with more then 5 cascading parameters. Some
> >> > parameters have several 1000 rows. If each selection will couse the
> >> requery
> >> > from all parameters it takes realy to long. Much more then 6 seconds
> >> (after
> >> > somebody choose a parameter) which is the "success criteria".
> >> >
> >> >
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> > > I don't understand your problem. Is it that you don't want to require
> >> > > a
> >> > > selection for customer? You can always add an All (do a union query).
> >> > >
> >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> >> that
> >> > > you design it that each cascading parameter has no more that a few
> >> hundred
> >> > > responses. I use cascading parameters all the time without a problem.
> >> > >
> >> > > What do you consider many rows?
> >> > >
> >> > > --
> >> > > Bruce Loehle-Conger
> >> > > MVP SQL Server Reporting Services
> >> > >
> >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> >> > > > Cascading Parameter cousing fatal DB overhead and performance
> >> > > > problem.
> >> > > >
> >> > > > I have 2 parameters:
> >> > > > - Parameter A) Customers
> >> > > > - Parameter B) Contact Persons
> >> > > > Both have a dataset as list source.
> >> > > >
> >> > > > In the dataset of B) is a reference to A).
> >> > > >
> >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> >> selection
> >> > > in
> >> > > > A) is used as parameter. That is how it should be.
> >> > > > BUT: A) is requiried too!!! :-O
> >> > > >
> >> > > > In other reports I have more parameters and dependencies. The list
> >> sources
> >> > > > are allways datasets with many returning rows. And this behaviour
> >> results
> >> > > in
> >> > > > a big performance problem.
> >> > > >
> >> > > > Even in the sample report described here it is a performance
> >> > > > problem.
> >> > > >
> >> > > > I can't believe it, that this is the ripeness of reporting
> >> > > > services!
> >> > > Please
> >> > > > tell me somebody that this is not true and how I can solve the
> >> problem.
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>|||Something more what I recognized right now!
If I execute the report the parameters get requeried again!
After I selected the parameters I clicked on the button to create the report
and after that new requests from the parameter datasources are traced on the
server.
"Dev Main" wrote:
> Dosn't work!
> Now I made the customer parameter a cascading parameter from parameter
> language. But the customer parameter get still requeried.
>
> "Bruce L-C [MVP]" wrote:
> > Just to make sure I understand. You pick a customer and the customer sp is
> > executed. It only gets executed when you select a customer. It does not get
> > executed when you select a cascading parameter, correct?
> >
> > If this is true, is it a real problem? Does the user keep the report open
> > and keep selecting different customers?
> >
> > One possible solution is to have the customers be cascaded parameter itself.
> > Having several thousand records as the source for a listbox is not a good
> > idea. I have seen this be a problem with other products besides RS. My
> > suggestion is to somehow end up with a most a couple of hundred items in the
> > list.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > > 100% for shure!
> > >
> > > I replaced the SQL Statment with a call of a stored procedure. And the
> > > stored procedure write a log entry in a table each time the SP is called.
> > >
> > > Each time I select a entry in the 'Customer' Parameter the query for the
> > > 'customer' Parameter get executed. And (strictly correct) also the query
> > > for
> > > the cascading parameter 'Contact Person'.
> > >
> > > Just for the sake of completness: The database is on a oracle server. But
> > > this could not be related to this behaviour, becouse Reporting Services
> > > fires
> > > the refill of the parameter drop down list.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I don't think that the queries should be executed more than once, are you
> > >> sure they are? The customers query should execute only once as far as I
> > >> know
> > >> (could be wrong, could be that my lists are short enough that I don't see
> > >> the delay).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > >> > Thank you for the fast answer!
> > >> >
> > >> > My dropdownlist in the parameter "customers" must be queried only one
> > >> time.
> > >> > The list in parameter "Contact Person" must be filled each time a
> > >> > customer
> > >> is
> > >> > selected. Becouse this list shows all contact persons from the selected
> > >> > customer.
> > >> >
> > >> > In the parameters are several 1000 records displayed e.g. "Customers"
> > >> > or
> > >> > "Projects".
> > >> >
> > >> > You wrote "You can always add an All (do a union query)." What does
> > >> > this
> > >> > means exactly. I use a dataset for the parameter "customer". The
> > >> > dataset
> > >> > contains:
> > >> > "Select CustID, CustName from Customers Order by Custname".
> > >> > In the "Report Parameter" dialog I choose
> > >> > Available Valus:
> > >> > - From query
> > >> > There is no other option to specify that this query should be executed
> > >> only
> > >> > one time.
> > >> >
> > >> > I have to create reports with more then 5 cascading parameters. Some
> > >> > parameters have several 1000 rows. If each selection will couse the
> > >> requery
> > >> > from all parameters it takes realy to long. Much more then 6 seconds
> > >> (after
> > >> > somebody choose a parameter) which is the "success criteria".
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > I don't understand your problem. Is it that you don't want to require
> > >> > > a
> > >> > > selection for customer? You can always add an All (do a union query).
> > >> > >
> > >> > > Not sure why you have a performance problem, you shouldn't. Assuming
> > >> that
> > >> > > you design it that each cascading parameter has no more that a few
> > >> hundred
> > >> > > responses. I use cascading parameters all the time without a problem.
> > >> > >
> > >> > > What do you consider many rows?
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > >> > > > Cascading Parameter cousing fatal DB overhead and performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I have 2 parameters:
> > >> > > > - Parameter A) Customers
> > >> > > > - Parameter B) Contact Persons
> > >> > > > Both have a dataset as list source.
> > >> > > >
> > >> > > > In the dataset of B) is a reference to A).
> > >> > > >
> > >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> > >> selection
> > >> > > in
> > >> > > > A) is used as parameter. That is how it should be.
> > >> > > > BUT: A) is requiried too!!! :-O
> > >> > > >
> > >> > > > In other reports I have more parameters and dependencies. The list
> > >> sources
> > >> > > > are allways datasets with many returning rows. And this behaviour
> > >> results
> > >> > > in
> > >> > > > a big performance problem.
> > >> > > >
> > >> > > > Even in the sample report described here it is a performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I can't believe it, that this is the ripeness of reporting
> > >> > > > services!
> > >> > > Please
> > >> > > > tell me somebody that this is not true and how I can solve the
> > >> problem.
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >|||You need to reconsider how you are doing this. I had thought letting them
pick a letter as you suggest here but if you have 7000 customers with the
letter A then that doesn't work. It doesn't matter what product you have,
you do not want 7000 or more in a list, it is just a bad design and the
performance will be awful. One thing you could do is have the user put in a
search criteria prior to customer, for instance parts of the customer name
and then the customer list uses like
select customer, customerid from customer where customer like '%' +
@.CustSearchCriteria + '%'
A user can not select well from a list of 7,000 (that is 100 pages of
customers).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
news:9B6FA691-39BC-4F99-BC5D-ABB1FD662F50@.microsoft.com...
> You understand it right!
> I choose a customer, customer and contact person get requiried. If I
choose
> a contact person nothing get requiried.
> Possibly your hint on making all parameters to cascading parameters help
me
> out of the mud. I could place a preselected parameter for language and
refer
> on it from all other parameter datasets. I'll test it.
> The requirements are focused on filtering the data. It is a CRM Database.
> I can't imagine how to keep the parameters less then several thousends.
> Customer beginning with 'A' are more then 7000 for example. Projects in
one
> country are several thousends, Sales Persons are more then 1000. It is
just a
> middle sized company. And I'm in trouble at the beginning of the project.
> Thank you very much for all your help on this!!!
> "Bruce L-C [MVP]" wrote:
> > Just to make sure I understand. You pick a customer and the customer sp
is
> > executed. It only gets executed when you select a customer. It does not
get
> > executed when you select a cascading parameter, correct?
> >
> > If this is true, is it a real problem? Does the user keep the report
open
> > and keep selecting different customers?
> >
> > One possible solution is to have the customers be cascaded parameter
itself.
> > Having several thousand records as the source for a listbox is not a
good
> > idea. I have seen this be a problem with other products besides RS. My
> > suggestion is to somehow end up with a most a couple of hundred items in
the
> > list.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > news:7575313F-9D00-4D93-9339-706F23D7F621@.microsoft.com...
> > > 100% for shure!
> > >
> > > I replaced the SQL Statment with a call of a stored procedure. And the
> > > stored procedure write a log entry in a table each time the SP is
called.
> > >
> > > Each time I select a entry in the 'Customer' Parameter the query for
the
> > > 'customer' Parameter get executed. And (strictly correct) also the
query
> > > for
> > > the cascading parameter 'Contact Person'.
> > >
> > > Just for the sake of completness: The database is on a oracle server.
But
> > > this could not be related to this behaviour, becouse Reporting
Services
> > > fires
> > > the refill of the parameter drop down list.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I don't think that the queries should be executed more than once, are
you
> > >> sure they are? The customers query should execute only once as far as
I
> > >> know
> > >> (could be wrong, could be that my lists are short enough that I don't
see
> > >> the delay).
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >>
> > >> "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> news:ADB810DF-4409-44AD-B5E7-A0948DE06F3B@.microsoft.com...
> > >> > Thank you for the fast answer!
> > >> >
> > >> > My dropdownlist in the parameter "customers" must be queried only
one
> > >> time.
> > >> > The list in parameter "Contact Person" must be filled each time a
> > >> > customer
> > >> is
> > >> > selected. Becouse this list shows all contact persons from the
selected
> > >> > customer.
> > >> >
> > >> > In the parameters are several 1000 records displayed e.g.
"Customers"
> > >> > or
> > >> > "Projects".
> > >> >
> > >> > You wrote "You can always add an All (do a union query)." What does
> > >> > this
> > >> > means exactly. I use a dataset for the parameter "customer". The
> > >> > dataset
> > >> > contains:
> > >> > "Select CustID, CustName from Customers Order by Custname".
> > >> > In the "Report Parameter" dialog I choose
> > >> > Available Valus:
> > >> > - From query
> > >> > There is no other option to specify that this query should be
executed
> > >> only
> > >> > one time.
> > >> >
> > >> > I have to create reports with more then 5 cascading parameters.
Some
> > >> > parameters have several 1000 rows. If each selection will couse the
> > >> requery
> > >> > from all parameters it takes realy to long. Much more then 6
seconds
> > >> (after
> > >> > somebody choose a parameter) which is the "success criteria".
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "Bruce L-C [MVP]" wrote:
> > >> >
> > >> > > I don't understand your problem. Is it that you don't want to
require
> > >> > > a
> > >> > > selection for customer? You can always add an All (do a union
query).
> > >> > >
> > >> > > Not sure why you have a performance problem, you shouldn't.
Assuming
> > >> that
> > >> > > you design it that each cascading parameter has no more that a
few
> > >> hundred
> > >> > > responses. I use cascading parameters all the time without a
problem.
> > >> > >
> > >> > > What do you consider many rows?
> > >> > >
> > >> > > --
> > >> > > Bruce Loehle-Conger
> > >> > > MVP SQL Server Reporting Services
> > >> > >
> > >> > > "Dev Main" <DevMain@.discussions.microsoft.com> wrote in message
> > >> > > news:6419869B-930C-4675-9D16-A94DBE55B279@.microsoft.com...
> > >> > > > Cascading Parameter cousing fatal DB overhead and performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I have 2 parameters:
> > >> > > > - Parameter A) Customers
> > >> > > > - Parameter B) Contact Persons
> > >> > > > Both have a dataset as list source.
> > >> > > >
> > >> > > > In the dataset of B) is a reference to A).
> > >> > > >
> > >> > > > If I choose a selectio in A) then B) becomes refreshed and the
> > >> selection
> > >> > > in
> > >> > > > A) is used as parameter. That is how it should be.
> > >> > > > BUT: A) is requiried too!!! :-O
> > >> > > >
> > >> > > > In other reports I have more parameters and dependencies. The
list
> > >> sources
> > >> > > > are allways datasets with many returning rows. And this
behaviour
> > >> results
> > >> > > in
> > >> > > > a big performance problem.
> > >> > > >
> > >> > > > Even in the sample report described here it is a performance
> > >> > > > problem.
> > >> > > >
> > >> > > > I can't believe it, that this is the ripeness of reporting
> > >> > > > services!
> > >> > > Please
> > >> > > > tell me somebody that this is not true and how I can solve the
> > >> problem.
> > >> > >
> > >> > >
> > >> > >
> > >>
> > >>
> > >>
> >
> >
> >

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?
I was hoping someone had experimented and found which works best.DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below. In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go. The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed.
On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL. Hope this helps.|||Thanks for the great info. All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.

Capture the SELECT Statement Duration on SQL7

Hello:
I need to give a report of the server performance about
the select statement duration. Does any setup that I can
capture these information into CSV format? How can I do
that?
Does any URL can reference?
Thanks a lot!Alan
Look at set statistics io on and set statistics time in the BOL
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!|||For big production stuf you can do several things.
Capture information using Profiler < the info would be inthe statement
ending row.
You can capture this to a sql table, or to a file and then import it into a
sql table.
You can then export to a flat file using DTS, or bcp , or a select in query
analyzer, then save the results area to a file.
Good luck
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!

Capture the SELECT Statement Duration on SQL7

Hello:
I need to give a report of the server performance about
the select statement duration. Does any setup that I can
capture these information into CSV format? How can I do
that?
Does any URL can reference?
Thanks a lot!
Alan
Look at set statistics io on and set statistics time in the BOL
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!
|||For big production stuf you can do several things.
Capture information using Profiler < the info would be inthe statement
ending row.
You can capture this to a sql table, or to a file and then import it into a
sql table.
You can then export to a flat file using DTS, or bcp , or a select in query
analyzer, then save the results area to a file.
Good luck
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alan" <anonymous@.discussions.microsoft.com> wrote in message
news:101501c49978$fc1227b0$a501280a@.phx.gbl...
> Hello:
> I need to give a report of the server performance about
> the select statement duration. Does any setup that I can
> capture these information into CSV format? How can I do
> that?
> Does any URL can reference?
> Thanks a lot!

Can't use Profiler to get execution plan for a particular query

I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Sorry I should have clarified - even Profiler won't give me the
execution plan for a query that is already running when I start the
trace, correct? I would have to already have the trace running first.

Can't use Profiler to get execution plan for a particular query

I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!Are you saying that you are also capturing System IDs , if you put ObjectID
greater than or equal to 100 - this will disclude System Objects.
This may not be complteley relevant to your problem , but you could also use
the DatabaseID / DatabaseName filter
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
> I am trying to troubleshoot some performance problems and I am trying
> to set up Profiler so that I can look at the execution plan of a
> particular stored procedure. I am using the ExecutionPlan event class
> in the Performance event category. I am also setting up a filter on
> ObjectID so that only activity related to this particular store
> procedure is returned.
> However Profiler is capturing ALL the activity on the server. Filtering
> by ObjectID doesn't seem to make any difference even though according
> to BOL the ObjectID is a valid data column for the ExecutionPlan event.
> Am I missing something or is this a bug in SQL Server/error in the BOL
> documentation?
> Thanks!
>|||The ExecutionPlan event class doesn't populate the object id unfortunately.
So, we cannot filter on it in SQL Server 2000 Profiler. But it does populate
the database id, so you can filter on that to reduce the amount of data
being captured by Profiler. You can also filter on the hostname, if you know
from which machine the request is coming in.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150241832.487422.17030@.y43g2000cwc.googlegroups.com...
I am trying to troubleshoot some performance problems and I am trying
to set up Profiler so that I can look at the execution plan of a
particular stored procedure. I am using the ExecutionPlan event class
in the Performance event category. I am also setting up a filter on
ObjectID so that only activity related to this particular store
procedure is returned.
However Profiler is capturing ALL the activity on the server. Filtering
by ObjectID doesn't seem to make any difference even though according
to BOL the ObjectID is a valid data column for the ExecutionPlan event.
Am I missing something or is this a bug in SQL Server/error in the BOL
documentation?
Thanks!|||Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||I am not aware of a way of getting execution plan of a query while it is
running, without using Profiler.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<pshroads@.gmail.com> wrote in message
news:1150323592.065493.262700@.f6g2000cwb.googlegroups.com...
Thanks for your reply. Do you know of any way to get the execution plan
for a query that is already running? Our database is too active to run
a profiler for an extended period of time to catch this intermittent
problem but when it's occuring I'd like to be able to see the query
plan that it's using.
Thanks|||Sorry I should have clarified - even Profiler won't give me the
execution plan for a query that is already running when I start the
trace, correct? I would have to already have the trace running first.|||One way of doing this is to inject some sp_trace_create script into the
stored proc from which you want to capture exec plans, using @.@.spid as a
filter. Start the trace at the beginning of the proc & close it at the end.
If the proc is a high repitition proc, you might need some logic to limit
how many instances actually get traced, but this isn't too hard to work
out..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150387360.417932.118480@.h76g2000cwa.googlegroups.com...
> Sorry I should have clarified - even Profiler won't give me the
> execution plan for a query that is already running when I start the
> trace, correct? I would have to already have the trace running first.
>|||That's a great idea! I will try that.
Thanks.|||Remember to capture Showplan ALL, so you can pick apart the execution plan
subtree cost & focus your tuning work similarly to how you would if you were
using a graphical plan (based on node %). Post back if you have any
difficulty analysing the text output as that can also be tricky sometimes..
Regards,
Greg Linwood
SQL Server MVP
<pshroads@.gmail.com> wrote in message
news:1150465481.039455.258630@.u72g2000cwu.googlegroups.com...
> That's a great idea! I will try that.
> Thanks.
>