High reads

Some of my sps are showing high reads if executed from a >net application.
Like if sp is executed from .net app it shows some wayy around 5000000
reads and does not complete even in 10 min. Same sp , same time on th e same
server executes in 10 sec using sql query analyzer, and shows 30000 reads.
Can anyonme explains whats the problem?Bad query plans. Try adding the option WITH RECOMPILE to your stored
procedures.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"VSS" <aa@.aa> wrote in message news:e04bYa6rGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e
> same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?
>|||Check whether SET NCOUNT ON is set in SP .
M A Srinivas
VSS wrote:
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e sa
me
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?

High reads

Some of my sps are showing high reads if executed from a >net application.
Like if sp is executed from .net app it shows some wayy around 5000000
reads and does not complete even in 10 min. Same sp , same time on th e same
server executes in 10 sec using sql query analyzer, and shows 30000 reads.
Can anyonme explains whats the problem?Bad query plans. Try adding the option WITH RECOMPILE to your stored
procedures.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"VSS" <aa@.aa> wrote in message news:e04bYa6rGHA.2452@.TK2MSFTNGP03.phx.gbl...
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e
> same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?
>|||Check whether SET NCOUNT ON is set in SP .
M A Srinivas
VSS wrote:
> Some of my sps are showing high reads if executed from a >net application.
> Like if sp is executed from .net app it shows some wayy around 5000000
> reads and does not complete even in 10 min. Same sp , same time on th e same
> server executes in 10 sec using sql query analyzer, and shows 30000 reads.
> Can anyonme explains whats the problem?

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
..NET Software Developer
The information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
--
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:
> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server will
> just randomly start crawling
> --
> JP
> .NET Software Developer

High CPU usage help

Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
activity log is showing several ProcessIDs that say "AWAITING COMMAND" and i
t
also says the Status is SLEEPING, However the CPU# is 235789 and the Phyical
I/O is 36345 and Memory is 912
So if the status is Sleeping and AWAITNG COMMAND, why are these numbers not
0? If its waiting it shouldnt be doing much of anything. Our SQL server will
just randomly start crawling
JP
.NET Software DeveloperThe information you are seeing is cumulative for that spid. It isnt what it
is using at that point in time, but those statistics are the resources used
for the life of that spid.
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"JP" wrote:

> Im at my wits end. The SQL server (SQL2000 using 2005 Studio to view )
> activity log is showing several ProcessIDs that say "AWAITING COMMAND" and
it
> also says the Status is SLEEPING, However the CPU# is 235789 and the Phyic
al
> I/O is 36345 and Memory is 912
> So if the status is Sleeping and AWAITNG COMMAND, why are these numbers no
t
> 0? If its waiting it shouldnt be doing much of anything. Our SQL server wi
ll
> just randomly start crawling
> --
> JP
> .NET Software Developer

Hiding/Showing columns based on the columns present in the dataset

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that?

Any reply showing me the right way is appricited.

-Thanks,

Digs

In the Column properties, expand the Visibility section, and then click on Hidden, and choose Expression.

In the expression, use the same input parameters that you are using to build the column list in your SQL Statement to set the property.

An example expression might look like this:

=iif(Parameters!ShowColumn1.Value= True, false, true)

Where Parameters!ShowColumn1 is a report parameter that you have defined,

This should get you started...

BobP

|||

Hi,

Thanks for the reply..Need bit change =IIf(Fields!Collection.IsMissing=True,True,False) In Visibility -- > Hidden

-Thanks,

Digs

Hiding/Showing Columns based on Parameters

I have a report that has a matrix in it. It is a multidimensional report with
drill down effects resulting from an MDX query. It has 2 columns (measures).
I have created a parameter in order to hide/show a column. I have written the
following expression for the Visibility property of my Heading and Field
Values of a column:
=iif(Parameters!Show.Value="Measures_COUNT",True, False)
What happens is: My column heading and the values both disappear (appears
blank) but the column remains there. i.e the column width doesnt become zero.
Has someone faced a similar problem' I could not find an expression for the
"width" property of my textboxes so that I could set them to zero.
Also, what I would like to get done:
User selects a list of fields from a page that will act as a filter for my
report data (i.e. where clause of the query ) can i modify my MDX query of
the report to take these values into account?
Also, the user selects a list of fields that he/she wants appearing in the
report. Can i pass these fields to the report and manipulate the columns at
run time. This is what i was trying to achieve by hiding/showing columns?
Thanks,
RishitHi,
The "width" property for a textbox can be founf by expanding the "size"
property. In order to do what you want to do you have to do the following:
Set the visibility of the column - not the individual fields that make up
the column.
Hope this helps.
"Rishit" wrote:
> I have a report that has a matrix in it. It is a multidimensional report with
> drill down effects resulting from an MDX query. It has 2 columns (measures).
> I have created a parameter in order to hide/show a column. I have written the
> following expression for the Visibility property of my Heading and Field
> Values of a column:
> =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> What happens is: My column heading and the values both disappear (appears
> blank) but the column remains there. i.e the column width doesnt become zero.
> Has someone faced a similar problem' I could not find an expression for the
> "width" property of my textboxes so that I could set them to zero.
> Also, what I would like to get done:
> User selects a list of fields from a page that will act as a filter for my
> report data (i.e. where clause of the query ) can i modify my MDX query of
> the report to take these values into account?
> Also, the user selects a list of fields that he/she wants appearing in the
> report. Can i pass these fields to the report and manipulate the columns at
> run time. This is what i was trying to achieve by hiding/showing columns?
> Thanks,
> Rishit|||I'm sorry. I am not that familiar with using matrices and didn't realise they
are handled differently. Please let me know if you find an answer.
"Rishit" wrote:
> Hi,
> I checked the properties for the matrix column. The Visibility propert isnt
> present. Besides, the Width property of the field cannot be set to zero using
> an expression. It doesnt allow width to be set to zero thru an expression.
> Mithun
> "Andrew Byrne" wrote:
> > Hi,
> >
> > The "width" property for a textbox can be founf by expanding the "size"
> > property. In order to do what you want to do you have to do the following:
> >
> > Set the visibility of the column - not the individual fields that make up
> > the column.
> >
> >
> >
> > Hope this helps.
> >
> > "Rishit" wrote:
> >
> > > I have a report that has a matrix in it. It is a multidimensional report with
> > > drill down effects resulting from an MDX query. It has 2 columns (measures).
> > > I have created a parameter in order to hide/show a column. I have written the
> > > following expression for the Visibility property of my Heading and Field
> > > Values of a column:
> > > =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> > > What happens is: My column heading and the values both disappear (appears
> > > blank) but the column remains there. i.e the column width doesnt become zero.
> > > Has someone faced a similar problem' I could not find an expression for the
> > > "width" property of my textboxes so that I could set them to zero.
> > >
> > > Also, what I would like to get done:
> > > User selects a list of fields from a page that will act as a filter for my
> > > report data (i.e. where clause of the query ) can i modify my MDX query of
> > > the report to take these values into account?
> > > Also, the user selects a list of fields that he/she wants appearing in the
> > > report. Can i pass these fields to the report and manipulate the columns at
> > > run time. This is what i was trying to achieve by hiding/showing columns?
> > >
> > > Thanks,
> > > Rishit|||you must go to the properties pane of the Matrix itself. Then go to
groupings. Go to the column grouping properties for visibility. Put your
coditional statement there.
"Andrew Byrne" wrote:
> I'm sorry. I am not that familiar with using matrices and didn't realise they
> are handled differently. Please let me know if you find an answer.
> "Rishit" wrote:
> > Hi,
> > I checked the properties for the matrix column. The Visibility propert isnt
> > present. Besides, the Width property of the field cannot be set to zero using
> > an expression. It doesnt allow width to be set to zero thru an expression.
> >
> > Mithun
> >
> > "Andrew Byrne" wrote:
> >
> > > Hi,
> > >
> > > The "width" property for a textbox can be founf by expanding the "size"
> > > property. In order to do what you want to do you have to do the following:
> > >
> > > Set the visibility of the column - not the individual fields that make up
> > > the column.
> > >
> > >
> > >
> > > Hope this helps.
> > >
> > > "Rishit" wrote:
> > >
> > > > I have a report that has a matrix in it. It is a multidimensional report with
> > > > drill down effects resulting from an MDX query. It has 2 columns (measures).
> > > > I have created a parameter in order to hide/show a column. I have written the
> > > > following expression for the Visibility property of my Heading and Field
> > > > Values of a column:
> > > > =iif(Parameters!Show.Value="Measures_COUNT",True, False)
> > > > What happens is: My column heading and the values both disappear (appears
> > > > blank) but the column remains there. i.e the column width doesnt become zero.
> > > > Has someone faced a similar problem' I could not find an expression for the
> > > > "width" property of my textboxes so that I could set them to zero.
> > > >
> > > > Also, what I would like to get done:
> > > > User selects a list of fields from a page that will act as a filter for my
> > > > report data (i.e. where clause of the query ) can i modify my MDX query of
> > > > the report to take these values into account?
> > > > Also, the user selects a list of fields that he/she wants appearing in the
> > > > report. Can i pass these fields to the report and manipulate the columns at
> > > > run time. This is what i was trying to achieve by hiding/showing columns?
> > > >
> > > > Thanks,
> > > > Rishit

Hiding rows in a matrix and still showing subtotals for that row

Hi
I'm trying to tidy up a report that has a detail row that's not needed but
the subtotal row for that row is... hope that makes sense. The subtotal is
in fact is an average of the detail row. If I hide the detail row, the AVG
row also disappears! This is more or less what it looks like:
Machine Name | Cloth | Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
Total Utilised Looms | 1 3 5 6
Available Looms | 6 4 7 6
What I'm trying to do is hide the second row...
Any ideas would be appreciated.
Many thanks
Rob
--
Message posted via http://www.sqlmonster.comIf you edit the properties of de detail-cel an set the visibility/hidden
property =TRUE it should work
"robhob via SQLMonster.com" wrote:
> Hi
> I'm trying to tidy up a report that has a detail row that's not needed but
> the subtotal row for that row is... hope that makes sense. The subtotal is
> in fact is an average of the detail row. If I hide the detail row, the AVG
> row also disappears! This is more or less what it looks like:
> Machine Name | Cloth | Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> Total Utilised Looms | 1 3 5 6
> Available Looms | 6 4 7 6
> What I'm trying to do is hide the second row...
> Any ideas would be appreciated.
> Many thanks
> Rob
> --
> Message posted via http://www.sqlmonster.com
>

Hiding Duplicate Records

Is it possible to output records in a dataset while not showing the
duplicates. The dataset that I am using returns records like the following.
title1; problem1; associateddata1
title1; problem1; associateddata2
title2; problem2; associateddata1
Even though first two rows do not look like duplicates, since they have the
same title, I only want to show title1 and problem1 once, then show
associateddata1 & 2 at the bottom of that group. Then go to another page and
show title2, problem2, and associateddata1. I tried using a table and various
grouping layouts with no success. Can someone help with this?Yes...
Right click on one of the Table cells in the layout and Click on "Dont
show Duplicates"|||Sorry...I should have mentioned that I tried that. Is it not working because
of the associatedata part that I said keeps it from looking like an exact
duplicate?
"sorcerdon@.gmail.com" wrote:
> Yes...
> Right click on one of the Table cells in the layout and Click on "Dont
> show Duplicates"
>|||Alright I just made a simple Table in AdventureSoft looking like this:
title1 problem1 data1
title1 problem1 data2
title2 problem2 data1
title2 problem2 data2
Do you want the following results:
title1 problem1 data1
data2
title2 problem2 data1
data2
Is this what you want?|||if this is what you want then you need to make 1 group. Group by the
title field.
Then rick click on the =Fields!title.Value field and the
=Fields!problem.Value field and say no duplicates.
Doing these together shows the desired results you see above this post.|||That is exactly what I was looking for. Thanks.
"sorcerdon@.gmail.com" wrote:
> if this is what you want then you need to make 1 group. Group by the
> title field.
> Then rick click on the =Fields!title.Value field and the
> =Fields!problem.Value field and say no duplicates.
> Doing these together shows the desired results you see above this post.
>|||Glad it helped.
Regards,
Sorcerdon|||I hope you are getting a reply on this. I tried you solution and at first
thought it worked, but later remembered that I had changed my query. So I
only thought it was working. I'm going to try attaching my query and showing
you exactly what is coming back and how I'm displaying it on a report, to see
if this helps. Course it seemed like you outlined it the same as I'm about
to, but just in case it helps. I appreciate any help you can provide. I
apologize in advance for this being so long...just want to make sure I don't
miss anything.
I have two records in the Problems table. Of the two records in the problems
table, one of the records has two associated data elements and the second
record has none attached. So the resultset would be similar to the following
(with a few rows left off for easy viewing):
Problem Table:
prob1ID; found big problem title 1; location Virginia; mike collins
prob2ID; found big problem title 2; location Maryland; mike collins
Associated Data:
Prob1ID; Data Item 1
Prob1ID; Data Item 2
Below query would return it as:
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 1; location Virginia; mike collins; data
item 1
prob1ID; found big problem title 2; location Maryland; mike collins; data
item 2
Select t.ProblemID, t.Title, l.LocationName,
p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
Originator,
p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
Screener,
p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
SbjMtrExpert,
p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
TestDirector,
ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
From Problems t
Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
Join common..personnel p2 On p2.PersonnelID = t.FixedByID
Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
Join Locations l On t.LocationID = l.LocationID
Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
Now that is done, I am displaying everything you see the query returning on
the report using a stored procedure. I am using a table and adding many
detail rows so I can display the data looking like a form as opposed to just
rows. I tried setting the hide duplicate rows on the title and then on every
other field (with the exception of the associated data field) with no luck.
Any suggestions to what I am doing wrong?
"sorcerdon@.gmail.com" wrote:
> Glad it helped.
> Regards,
> Sorcerdon
>|||hi mike
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
"Mike Collins" wrote:
> I hope you are getting a reply on this. I tried you solution and at first
> thought it worked, but later remembered that I had changed my query. So I
> only thought it was working. I'm going to try attaching my query and showing
> you exactly what is coming back and how I'm displaying it on a report, to see
> if this helps. Course it seemed like you outlined it the same as I'm about
> to, but just in case it helps. I appreciate any help you can provide. I
> apologize in advance for this being so long...just want to make sure I don't
> miss anything.
> I have two records in the Problems table. Of the two records in the problems
> table, one of the records has two associated data elements and the second
> record has none attached. So the resultset would be similar to the following
> (with a few rows left off for easy viewing):
> Problem Table:
> prob1ID; found big problem title 1; location Virginia; mike collins
> prob2ID; found big problem title 2; location Maryland; mike collins
> Associated Data:
> Prob1ID; Data Item 1
> Prob1ID; Data Item 2
> Below query would return it as:
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 1; location Virginia; mike collins; data
> item 1
> prob1ID; found big problem title 2; location Maryland; mike collins; data
> item 2
> Select t.ProblemID, t.Title, l.LocationName,
> p1.FirstName + ' ' + IsNull(p1.MiddleName + ' ', '') + p1.LastName As
> Originator,
> p2.FirstName + ' ' + IsNull(p2.MiddleName + ' ', '') + p2.LastName As FixedBy,
> p3.FirstName + ' ' + IsNull(p3.MiddleName + ' ', '') + p3.LastName As
> Screener,
> p4.FirstName + ' ' + IsNull(p4.MiddleName + ' ', '') + p4.LastName As
> SbjMtrExpert,
> p5.FirstName + ' ' + IsNull(p5.MiddleName + ' ', '') + p5.LastName As
> TestDirector,
> ProblemSource, SystemVersion, SystemBuild, Type, Category, UserPriority,
> DateProblemIdentified, SequenceOfEvents, ProblemDescription, Workaround,
> SuggestedFix, TrackingNumber, ad.AssociatedDataIdentifier
> From Problems t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.FixedByID
> Join common..personnel p3 On p3.PersonnelID = t.ScreenerID
> Join common..personnel p4 On p4.PersonnelID = t.SubjectMatterExpertID
> Join common..personnel p5 On p5.PersonnelID = t.TestDirectorID
> Join Locations l On t.LocationID = l.LocationID
> Left Join AssociatedDataTPRLI a On t.TPRID = a.TPRID
> Left Join AssociatedData ad On a.AssociatedDataID = ad.AssociatedDataID
> Now that is done, I am displaying everything you see the query returning on
> the report using a stored procedure. I am using a table and adding many
> detail rows so I can display the data looking like a form as opposed to just
> rows. I tried setting the hide duplicate rows on the title and then on every
> other field (with the exception of the associated data field) with no luck.
> Any suggestions to what I am doing wrong?
> "sorcerdon@.gmail.com" wrote:
> > Glad it helped.
> >
> > Regards,
> >
> > Sorcerdon
> >
> >