Performance Tuning UPDATE Statement

Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?

Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".

UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL

Any Help would be greatly appreciated!On 4 Aug 2004 08:27:50 -0700, MAS wrote:

>Below is a simple UPDATE that I have to perform on a table that has
>about 2.5 million rows (about 4 million in production) This query
>runs for an enourmous amount of time (over 1 hour). Both the
>ChangerRoleID and the ChangerID are indexed (not unique). Is there
>any way to performance tune this?
>Controlling the physical drive of the log file isn't possible at our
>client sites (we don't have control) and the recovery model needs to
>be set to "Full".
>UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
>ChangerRoleID IS NULL
>Any Help would be greatly appreciated!

Hi MAS,

If you remove the non-unique index on ChangerRoleID before doing the
update and recreate it afterwards, you'll probably save some time. The
index could have been useful if only a few of all rows match the IS NULL
condition, but with over aan hour execution time, I think there are so
many matches that a full table scan will be quicker. Removing the index
before doing the update saves SQL Server the extra work of constantly
having to update the index to keep it in sync with the data. Of course,
this might affect other queries that execute during the update and would
have benefited from this index. The index on ChangerID will neither be
used nor cause extra work for this update.

Check if there's a trigger that gets fired by the update. If you can
safely disable that trigger during the update process, do so. Same for
constraints: are there any CHECK or REFERENCES (foreign key) constraints
defined for ChangerRoleID? If so, disable constraint checking (again, only
if it is safe, i.e. you have to be sure that this update won't cause
violation of the constraint *and* that no other person accessing the
database during the time constraint checking is disabled will be able to
cause violations of the constraint).

You state that the recovery model needs to be full; from that I conclude
that you can't lock other users out of the database during the update. Can
you at least take measures to prevent other users from using (updating,
but preferably reading as well) the CLIENTSHISTORY table?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

MAS (mas32677@.hotmail.com) writes:
> Below is a simple UPDATE that I have to perform on a table that has
> about 2.5 million rows (about 4 million in production) This query
> runs for an enourmous amount of time (over 1 hour). Both the
> ChangerRoleID and the ChangerID are indexed (not unique). Is there
> any way to performance tune this?
> Controlling the physical drive of the log file isn't possible at our
> client sites (we don't have control) and the recovery model needs to
> be set to "Full".
> UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
> ChangerRoleID IS NULL
> Any Help would be greatly appreciated!

To add to what Hugo said, if that index on ChangerRoleID is clustered,
and many rows have a NULL value, then you are in for a problem.

It may help to do it batches:

DECLARE @.batch_size int, @.rowc int
SELECT @.batch_size = 50000
SELECT @.rowc = @.batch_size
SET ROWCOUNT @.batch_size
WHILE @.rowc = @.batch_size
BEGIN
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID
WHERE ChangerRoleID IS NULL
AND ChangerID IS NOT NULL
SELECT @.rowc = @.@.rowcount
END
SET ROWCOUNT 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance Tuning SQL query in Trigger

i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in

CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.

please find the structure of CORP_CAGE table:

1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO

CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following

query from application which updates 10,000 records.

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using

UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum

records,this trigger is working fine.

Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END

END

please help me on this for performance tuning the below query.

I don't have the schema of your table, which in this case is critical. However, if this statement:

Code Snippet

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

is updating 10,000 records then your join is going to cause an update that is the cross product of 10,000 x 10,000 or 100,000,000 logical records. This cannot be right. Look at your trigger WHERE condition:

Code Snippet

WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM

since you are updating ONLY for SEQ_NO = 111 and you are joining the INSERTED pseudo table -- with 10,000 records -- with the DELETE pseudo table -- also with 10,000 records -- and since all records of the DELETED pseudo and all records of the DELETED pseudo have the same SEQ_NO -- specifically 111 you end up with the cross product. You need to linclude the KEY information as part of the join condition.

sql

Performance Tuning for Row-by-Row Update Statement

hi

For an unavoidable reason, I have to use row-by-row processing
(update) on a temporary table to update a history table every day.
I have around 60,000 records in temporary table and about 2 million in
the history table.

Could any one please suggest different methods to imporve the runtime
of the query?

Would highly appreciate!Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||"Muzamil" <muzamil@.hotmail.com> wrote in message
news:5a998f78.0405211023.24b40513@.posting.google.c om...
> hi
> For an unavoidable reason, I have to use row-by-row processing
> (update) on a temporary table to update a history table every day.
> I have around 60,000 records in temporary table and about 2 million in
> the history table.

Not much you can do if you absolutely HAVE to do row-by-row updating.

You might want to post DDL, etc. so others can take a crack at it. I've
seen many times someone will say, "I have to use a cursor", "I have to
update one row at a time" and then someone posts a much better/faster
solution.

Also, how are you handling transactions? Explicitly or implicitely? If
you're doing them implicitely, are you wrapping each update in its own, or
can up batch say 20 updates?

Finally, where's your log files? Separate physical drives?

> Could any one please suggest different methods to imporve the runtime
> of the query?
> Would highly appreciate!|||Hi
Thanks for your reply.

The row-by-row update is mandatory becuase the leagacy system is
sending us the information such as "Add", "Modify" or "delete" and
this information HAS to be processed in the same order otherwise we'll
get the erroneous data.
I know it's a dumb way of doing things but this is what our and their
IT department has chosen to be correct way of action after several
meetings. Hence the batch idea will not work here.

I am not using Cursors, instead I am using the loop based on the
primary key.

The log files are on different drives.

I've also tried using "WITH (ROWLOCK)" in the update statement but
it's not helping much.

Can you please still throw in some idea? Would be great help!

Thanks

"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<tOxrc.234090$M3.65389@.twister.nyroc.rr.com>...
> "Muzamil" <muzamil@.hotmail.com> wrote in message
> news:5a998f78.0405211023.24b40513@.posting.google.c om...
> > hi
> > For an unavoidable reason, I have to use row-by-row processing
> > (update) on a temporary table to update a history table every day.
> > I have around 60,000 records in temporary table and about 2 million in
> > the history table.
> Not much you can do if you absolutely HAVE to do row-by-row updating.
> You might want to post DDL, etc. so others can take a crack at it. I've
> seen many times someone will say, "I have to use a cursor", "I have to
> update one row at a time" and then someone posts a much better/faster
> solution.
> Also, how are you handling transactions? Explicitly or implicitely? If
> you're doing them implicitely, are you wrapping each update in its own, or
> can up batch say 20 updates?
> Finally, where's your log files? Separate physical drives?
>
> > Could any one please suggest different methods to imporve the runtime
> > of the query?
> > Would highly appreciate!|||Muzamil (muzamil@.hotmail.com) writes:
> The row-by-row update is mandatory becuase the leagacy system is
> sending us the information such as "Add", "Modify" or "delete" and
> this information HAS to be processed in the same order otherwise we'll
> get the erroneous data.

Ouch. Life is cruel, sometimes.

I wonder what possibilities there could be to find parallel streams,
that is updates that could be performed independently. Maybe you
can modify 10 rows at a time then. But it does not sound like a very
easy thing to do.

Without knowing the details of the system, it is difficult to give
much advice. But any sort of pre-aggregation you can do, is probably
going to pay back.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Details of the system:
The leagcy system sends us records flagged with "Add", "modify" or
"delete".
The purpose of these flags is self-explnatory. But the fun began when
we noticed that within same file , legacy system sends us "Add" and
then "Modify". Thus, we were left with no other option except to do
row-by-row processing.
We came up with the following logic:

a)If records StatusFlag is A' and records key does not exist in
DataWareHouse's Table, then the record is inserted into
DataWareHouse's Table.

b)If records StatusFlag is A', but records key exists in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable..

c)If records StatusFlag is M' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated.

d)If records StatusFlag is M' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

e)If records StatusFlag is M' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

f)If records StatusFlag is D' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated as inactive.

g)If records StatusFlag is D' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

h)If records StatusFlag is D' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

This logic takes care of ALL the anomalies we were facing before but
at the cost of long processing time.

I await your comments.

Thanks

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F53BF51111Yazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > The row-by-row update is mandatory becuase the leagacy system is
> > sending us the information such as "Add", "Modify" or "delete" and
> > this information HAS to be processed in the same order otherwise we'll
> > get the erroneous data.
> Ouch. Life is cruel, sometimes.
> I wonder what possibilities there could be to find parallel streams,
> that is updates that could be performed independently. Maybe you
> can modify 10 rows at a time then. But it does not sound like a very
> easy thing to do.
> Without knowing the details of the system, it is difficult to give
> much advice. But any sort of pre-aggregation you can do, is probably
> going to pay back.|||Muzamil (muzamil@.hotmail.com) writes:
> Details of the system:
> The leagcy system sends us records flagged with "Add", "modify" or
> "delete".
> The purpose of these flags is self-explnatory. But the fun began when
> we noticed that within same file , legacy system sends us "Add" and
> then "Modify". Thus, we were left with no other option except to do
> row-by-row processing.
> We came up with the following logic:

Hm, you might be missing a few cases. What if you get an Add, and record
exists in DW, but is marked inactive? With your current logic, the
input record moved to the Invalid table.

And could that feediug system be as weird as to send Add, Modify, Delete,
and Add again? Well, for a robust solution this is what we should assume.

It's a tricky problem, and I was about to defer the problem, when I
recalled a solution that colleague did for one of our stored procedures.
The secret word for tonight is bucketing! Assuming that there are
only a couple of input records for each key value, this should be
an excellent solution. You create buckets, so that each bucket has
at most one row per key value. Here is an example on how to do it:

UPDATE inputtbl
SET bucket = (SELECT count(*)
FROM inputtbl b
WHERE a.keyval = b.keyval
AND a.rownumber < b.rownumber) + 1
FROM inputtbl a

input.keyval is the keys for the records in the DW table. Rownumber
is a column which as describes the processing order. I assume that
you have such a column.

So now you can iterate over the buckets, and for each bucket, you can do
set- based processing. You still have to iterate, but instead over 60000
rows, only over a couple of buckets.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think I was not articulate enough to convey the logic properly.
Anyways, thanks to everyone for your help.
By using the ROWLOCK and proper indexes, I was ale to reduce the time considerably.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F6821D6ABYazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > Details of the system:
> > The leagcy system sends us records flagged with "Add", "modify" or
> > "delete".
> > The purpose of these flags is self-explnatory. But the fun began when
> > we noticed that within same file , legacy system sends us "Add" and
> > then "Modify". Thus, we were left with no other option except to do
> > row-by-row processing.
> > We came up with the following logic:
> Hm, you might be missing a few cases. What if you get an Add, and record
> exists in DW, but is marked inactive? With your current logic, the
> input record moved to the Invalid table.
> And could that feediug system be as weird as to send Add, Modify, Delete,
> and Add again? Well, for a robust solution this is what we should assume.
> It's a tricky problem, and I was about to defer the problem, when I
> recalled a solution that colleague did for one of our stored procedures.
> The secret word for tonight is bucketing! Assuming that there are
> only a couple of input records for each key value, this should be
> an excellent solution. You create buckets, so that each bucket has
> at most one row per key value. Here is an example on how to do it:
> UPDATE inputtbl
> SET bucket = (SELECT count(*)
> FROM inputtbl b
> WHERE a.keyval = b.keyval
> AND a.rownumber < b.rownumber) + 1
> FROM inputtbl a
> input.keyval is the keys for the records in the DW table. Rownumber
> is a column which as describes the processing order. I assume that
> you have such a column.
> So now you can iterate over the buckets, and for each bucket, you can do
> set- based processing. You still have to iterate, but instead over 60000
> rows, only over a couple of buckets.|||Muzamil (muzamil@.hotmail.com) writes:
> I think I was not articulate enough to convey the logic properly.
> Anyways, thanks to everyone for your help. By using the ROWLOCK and
> proper indexes, I was ale to reduce the time considerably.

Good indexes is always useful, and of course for iterative processing
it is even more imperative, since the cost a less-than-optimal plan
is multiplied.

I'm just curious, would my bucketing idea be applicable to your problem?
It should give you even more speed, but if you have good-ebough now, there
is of course no reason to spend more time on it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance Question

Hi there,
In a SP, I want to update table A based on values in table
B after data manipulation.
Which of the following option is better in the performance
point of view.
(1) Using Cursor
(2) Using 'table' datatype to hold one table
(3) Using temporary table instead of cursor.
Is there any other better approach exist?
Can 'table' datatype be used in 'Execute SQLTask' in DTS?
TIA,
HariHi
I would go with (2) but If you post DDL+ sample data + expected result it
will be more easily to olve the problem
Also consider
UPDATE tableA SET col=b.col1 FROM
tableB b JOIN tableA a on b.pk=a.pk
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari|||I would also go for table datatype. But if you can share your code with us
then we can try to give you a better solution ... Did you look at using the
following syntax:
Update <TableA>
Set Col1 = <TableB>.Col1
Where <tableA>.id = <TableB>.id
something on these lines if you want to update a table comparing the values
from another table ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari

Performance problem: SSAS 2005 + ProClarity #2 (Update)

Hi,

I have updated my earlier post on performance (Thomas was helping me),

had some updates, thoughts and queries.

Please do check that.

here is the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=929316&SiteID=1

apologies for this post, but I am not sure if the older ones that get updated are seen. if you want me to open another post for it , please do tell me.

thanks a lot.

Regards

I have posted an answere on your original post.

Regards

Thomas Ivarsson

Performance of UPDATE commands on individual records

Ok, I'm at a crossroads in my program.
I've got a program that needs to throw an SQL update command to update
some individual records.

>From an efficiency standpoint, does SQL handle the UPDATE command
differently if the field is the same as the old field?
IE
Is it worth doing a string comparision on old vs new data in the
program, or should I just code to update all fields regardless and then
will the server optimize based on whether or not the data actually
changed?
Thanks,
Josh McFarlaneLogically SQL Server doesn't consider the diff at all.
If you want to only update small part of a large volume rows, doing string
comparision mostly yields better performance since that consumes less log
space.
For single line, I still suggest you "update when necessary", since some
triggers may sit there to enforce biz logic.
James
"Josh McFarlane" wrote:

> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
> Thanks,
> Josh McFarlane
>|||Josh McFarlane (darsant@.gmail.com) writes:
> Ok, I'm at a crossroads in my program.
> I've got a program that needs to throw an SQL update command to update
> some individual records.
>
> differently if the field is the same as the old field?
> IE
> Is it worth doing a string comparision on old vs new data in the
> program, or should I just code to update all fields regardless and then
> will the server optimize based on whether or not the data actually
> changed?
Since it's a bit of work, I'm not sure that it's worth the effort, but
there are at least two scenarios where you can gain some performance.
One case is if you use merge replication. I'm not into replication myself,
but I got a question from a guy who is very good at replication, and he
wanted to reduce an update, so that only columns that were actually
changed were to be updated. Apparently, this made replication more
effective.
The other case concerns indexed columns. Consider this:
SELECT * INTO Orders FROM Northwind..Orders
create unique clustered on Orders
create index ix On Orders(CustomerID)
go
BEGIN TRANSACTION
UPDATE Orders
SET EmployeeID = 18
WHERE OrderID = 11000
At this point runs query from another window:
select count(*) from Orders WHERE CustomerID = 'RATTC'
RATTC is the customer id for order 11000. This query returns the
value 18 instantly, was not blocked. Now in the first window do this:
UPDATE Orders
SET EmployeeID = 118,
CustomerID = 'RATTC'
WHERE OrderID = 11000
and now try the SELECT COUNT(*) again. This time it will block.
If you are generatnig the UPDATE statement dynamically, and in client
code, then filtering on columns that have actually changed is probably
manageable. In a stored procedure it is just painful.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Performance of SQL

I am using Reporting Services and I have a heavily parameterised report which
update each others lists etc.
When I run the report through the web service, the sql; server goes
ballistic, and runs at 100% for several minutes.
I have looked in SQL Profiler at what is going on and the actual queries (my
SQL) is only taking a few seconds to run, but there is loads of other stuff
happening, moving around chunkdata and the like. Also looking at the current
activity, I can see
several Shared DB Locks on my database and it is all looking pretty nasty.
My reports are all dynamic so is there anyway that I can strip back all of
these other things. I am quite prepared to belive that there is a problem in
my code, but I could translate the entire database into French quicker than
my 3 second SQL runs.
Can anyone shed any light on this."Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Tom,
Try modifying your queries for using Stored Procedures. I am really not
aware how you are using the parameters or else prepare your data's in the
form of table or views so that you can run the reporting services on the view
or tables. if you are using 2005 prepare a report model and use it.
Regards
Amarnath
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thanks for the pointers, but I am confident that this issue is a little more
than that. I have about 40 reports, some of which are very straightforward
and some that arent.
All use a few parameters, and search through a chunk of data. It is the
difference between the speed of the SQL in Query Analyser and that in Report
Designer. I cant see how precomiled SQL ios going to make a significant
difference, and I cant do what I want to do with views as the query has these
parameters and performance would go down to several minutes if I did it that
way.|||You need to clean up the database, that would speed everything up. ^_^|||Make sure you are only returning the rows you need in your report ( no
filtering.)
Make sure you are not doing extra sorting in Groups, etc, that might already
be done in the SQL.
Take a look at the log in REporting Services, and it will tell you how much
time is spent doing each of three steps... rendering often can take quite a
while... All of the data has to be loaded into memory on the IIS server, then
aggregations etc are done there..
The best thing you can do otherwise is to begin to simplify a slow report...
reducing the number of rows, sorts, groups, filters etc until you can find
the culprit..
Also try to rending using different things html, pdf. etc and see if that
makes a difference..
Also try rendering from a snapshot and see..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Tom Robson" wrote:
> I am using Reporting Services and I have a heavily parameterised report which
> update each others lists etc.
> When I run the report through the web service, the sql; server goes
> ballistic, and runs at 100% for several minutes.
> I have looked in SQL Profiler at what is going on and the actual queries (my
> SQL) is only taking a few seconds to run, but there is loads of other stuff
> happening, moving around chunkdata and the like. Also looking at the current
> activity, I can see
> several Shared DB Locks on my database and it is all looking pretty nasty.
> My reports are all dynamic so is there anyway that I can strip back all of
> these other things. I am quite prepared to belive that there is a problem in
> my code, but I could translate the entire database into French quicker than
> my 3 second SQL runs.
> Can anyone shed any light on this.|||Thank you all fo0r your advice,
Except Sorcerdon. What makes you think you know anything about my database?
Anyway. I will have a look at the reports, maybe reinstall a few things and
see if it clears up the mess. It certainly appears that it relates to
something between my SQL and hitting nthe database. I know that my SQL is
fine, as I can run that against the db and it is fine. Does Report Server
dump the results off somewhere before rendering? Or is it straight back via
asp.net data providers.?